Introduction
At Computer Ingenuity Associates, Inc. (CIA), we are glad to work with you to create better budgets, accurate forecasts and more profit using our software tools in your hotel management operations. You are receiving this document because you have purchased a license to one or more of the following:
- CIA Financial Planning (which might include (DREV, FBREV, Buddy and BPLAN)
- DSTAT
- CIA Online Portal
- HLO – Hotel Labor Optimization
These software applications and services can be highly customized to meet your specific needs and integrate with your other systems. To get you up and running in the most efficient manner, we have designed this step-by-step configuration guide. CIA Configuration Guide
Configuration Steps
STEP 1 is the Data collection phase. We will provide you with a series of spreadsheets to complete that will collect key data about your company and the hotels you operate.
STEP 2 is the Configuration phase where we will build databases based on the data you provided.
STEP 3 is the History Loading phase. Assuming this is not a pre-opening situation, your historical information will be of great value in the use of the system. Pre-loading operational data from this year and last year will improve your productivity will these tools.
STEP 4 is the Interface phase. Once the CIA systems are installed on your hotels’ systems, we will implement interfaces to pull data in an automated way. Depending on the systems you identify in STEP 1, we may have existing interface process. Otherwise, we may have to build into the timeline a project to design and create an interface. You may also choose to use the system with manual data entry until an interface is tested.
Timeline
It is difficult at the outset to set a timeline for completing the configuration. This depends, in part, how quickly you are able to gather the data and files we need. In our experience, the entire process can take from 3 to 20 weeks depending on the complexity of your setup and in what form you provide the data we need. While all steps must be completed before we can do training and you can start using the systems, you do not need to complete one step before working on the next. The steps are described below and may be worked on simultaneously. A timeline for implementation will be defined after completion of Step 1.
Let’s get started…
Step 1 – Data Collection
In this phase, we will need you to send us certain “hard copy” documents and fill out some surveys.
Company Survey
We have prepared an Excel workbook named ‘Customer Data Collection Survey (Custom).xlsx’ for you to complete and return to us. This is information common to all hotels in your company. Individualized information for each hotel is collected separately (see Systems Survey section below). It is important to complete all tabs of the workbook. Contact us if you have questions about any of the specific data required.
Hard Copy Samples
The “hard copy” documents can be provided on paper, PDF format or Excel format. In some cases, a sample from one hotel is all that is needed since the report will be the same at every location. We will highlight any instances where we’ll need a copy for each hotel.
Please provide a copy of these documents:
- A sample of the Daily Report (or Manager’s Report) produced daily from the PMS or other system. Provide a copy for each hotel only if they are different by hotel.
- A sample Trial Balance Report for one day showing transaction totals. Provide one report for each hotel only if the PMS configuration is not the same across hotels.
- Do Banquet charges flow through PMS? If not, provide sample banquet check recap report.
- A sample of your monthly Profit & Loss (P&L) Statement showing all accounts. We will mimic this format in the budget reports.
Other reports that that may be useful in the configuration phase include:
- Last Daily Report of each Month for past three months
- Last Group Room Booking Pace Reports
- Group Room Control Log
- Banquet on Books Report
- Report showing month end values from Time Clock System for past 3 months
Systems Surveys
There is a Systems Survey form in Exhibit A of this configuration guide. It is important to have this completed for each hotel being configured. For your convenience, we will also provide an Excel format of this form.
Step 2 – Configuration
We will construct customized databases based on the data provide in Step 1. The databases include:
- Company database – containing the Company Name, calendar and hotel list
- Security database – to store all the user logins and authentication information
- C_Master – this is the “master” list for accounts, departments, jobs, market segments, outlets, meal periods, etc. The individual hotel databases will be derived from this.
- mdb – This is the initial template for creating databases used by DSTAT.
Step 3 – Loading History
To get the fullest benefit from CIA Financial Planning, we load history for the latest complete year (i.e. “last year”) and the latest incomplete year (i.e. “current year”). In this section, we will highlight the various historical data needed and methods available to get that historical data into CIA Financial Planning.
The history data loaded will include:
- Room nights and Room Revenue History by Market Segment
- Food and Beverage Revenue History by Outlet / by Meal Period
- Labor cost and hours worked by position
- Line Item expense (P&L History)
DSTAT
The major revenues in the hotel (such as Rooms, Food and Beverage) are stored and budgeted as daily values. Therefore, it is important to have a source of daily history. Whether you are using DSTAT for night audit or not, this application will be used to track daily revenue data going forward. It can also be loaded with historical data.
Loading history into DSTAT first and then using DSTAT to export history is the easiest and most accurate method of loading daily revenue history to CIA Financial Planning since the export of data to CIA is already configured. However, this method assumes that the history is available in the PMS to load into DSTAT.
Market Segment History (DREV)
If historical rooms and revenue by market segment is not available in DSTAT, we can read the history from a .csv file. If the data is not in the correct format, you will have to re-format the data or incur extra contract fees to have CIA re-format the data for you.
Food & Beverage Revenue (FBREV)
If historical covers, food revenue and beverage revenue is not available in DSTAT, we can read the history from a .csv file. If the data is not in the correct format, you will have to re-format the data or incur extra contract fees to have CIA re-format the data for you.
Sample history file format
The F&B history can be loaded using account numbers since covers and food revenue by meal period has a unique account number in Buddy. Beverage (liquor, beer, wine) has unique account numbers as well. CIA has the ability to read history in this format:
Day |
Account |
Amount |
1/1/2013 |
1101-42970-0000 |
884.00 |
1/1/2013 |
1101-42980-0000 |
576.87 |
1/1/2013 |
1101-42990-0000 |
202.52 |
1/1/2013 |
1101-43000-0000 |
318.77 |
1/1/2013 |
1101-43030-0000 |
-403.00 |
1/1/2013 |
1101-43060-0000 |
17.61 |
1/1/2013 |
1101-43070-0000 |
65.84 |
1/1/2013 |
1101-43080-0000 |
19.42 |
1/1/2013 |
1101-93511-0000 |
78.81 |
1/1/2013 |
1101-93512-0000 |
32.58 |
1/1/2013 |
1101-93513-0000 |
13.23 |
1/1/2013 |
1106-42990-0000 |
1,252.65 |
1/1/2013 |
1106-43060-0000 |
420.42 |
1/1/2013 |
1106-43070-0000 |
356.26 |
Note: if daily F&B values are not available, then monthly values can be prorated into the daily format above.
Line Item History (Buddy)
The “Buddy” module is used to budget & forecast line item expenses and print financial reports. Data is loaded by month by account. There are several “standard” formats that can be used for reading this history. Since you’ll need to read P&L actuals every month, the simplest method for loading P&L history is to use the standard format defined by your accounting system. One such format is shown below.
Historical P&L Actual values
Year |
Period |
Account |
Amount |
2015 |
1 |
3010-40300-00000 |
14544.37 |
2015 |
1 |
3010-43300-93514 |
-233487.77 |
2015 |
1 |
3010-43300-93586 |
-117763.81 |
2015 |
1 |
3010-43300-93682 |
-36462 |
2015 |
1 |
3010-43300-93962 |
-8725.92 |
2015 |
1 |
3010-43300-94026 |
4229.16 |
- Each file has data for one property for one year
- Columns are Year, Month, Account Number and Amount (dollars)
- Revenue are negative, expenses are positive
Hours and Labor Cost History (Buddy)
The hours and labor cost history are used for comparison reporting and default Standards in Labor. There are two files for each hotel.
The first .csv file has hours and cost by job by period:
Property |
Year |
Period |
Position code |
GL Account |
Hours |
Expense |
3320 |
2015 |
1 |
3010051360 |
0 |
320 |
6342 |
3320 |
2015 |
1 |
3010051365 |
0 |
160 |
3918 |
3320 |
2015 |
1 |
3010051380 |
0 |
320 |
5816 |
3320 |
2015 |
1 |
3010057359 |
0 |
24 |
294 |
3320 |
2015 |
1 |
3010057361 |
0 |
334 |
3937 |
3320 |
2015 |
1 |
3010057363 |
0 |
330 |
4215 |
Position |
Day of Week |
Hours |
Stat |
Num Days |
Hours |
Stat |
Num Days |
Stat |
Description |
Per Text |
3010051360 |
2 |
464 |
24887 |
52 |
152 |
7103 |
13 |
102 |
Rooms Sold |
Room |
3010051360 |
3 |
464 |
30961 |
52 |
152 |
8747 |
13 |
102 |
Rooms Sold |
Room |
3010051360 |
4 |
464 |
31394 |
52 |
152 |
8926 |
13 |
102 |
Rooms Sold |
Room |
3010051360 |
5 |
464 |
28468 |
52 |
160 |
8439 |
13 |
102 |
Rooms Sold |
Room |
3010051360 |
6 |
464 |
29837 |
52 |
152 |
7657 |
13 |
102 |
Rooms Sold |
Room |
3010051365 |
2 |
416 |
24887 |
52 |
104 |
7103 |
13 |
102 |
Rooms Sold |
Room |
3010051365 |
3 |
416 |
30961 |
52 |
104 |
8747 |
13 |
102 |
Rooms Sold |
Room |
The second .csv file has day of week averages by position.
- This file contains Day of Week averages for the past 52 weeks and the past 13 weeks
- Day of week 1=Sunday, 2=Monday, etc.
- Stat is the value of the statistic over that period
- Stats are defined by position as that which is most relevant for that job
Note: is using ADP eTime and loading automatically to the CIA online portal, then these files will be generated automatically.
Step 4 – Interfaces
Once the databases are configured and deployed, you will use the system daily to import values from PMS, POS and Timekeeping systems to produce Daily Reports, journal entries for accounting and history for the forecast. Once per month, data will be read from the PMS, Sales system, and accounting system to prepare for the doing the forecast.
Daily Interfaces
- Export file from PMS (e.g. Opera, SMS-Host, OnQ, etc.)
- Export file from POS (e.g. Micros, Infogenesis, etc.)
- Export file from Timekeeping System (e.g. ADP eTime, Kronos, etc.)
Monthly Interfaces
- Group Room Data from Sales System (e.g. Delphi, Hotel SalesPro)
- PMS Reservation Data
- Banquet On the books from Sales System
- Import labor hours and cost by month
- Import labor hours by Day of Week for past 52 weeks
- P&L actuals from accounting system
The details of these interfaces are described in a separate document.
Exhibit A – Data Collection Checklist for CIA & DSTAT
Use for Transition to New Management Company
Hotel Information
Hotel Name |
|
Property Code |
|
Number of Guestrooms |
|
New Mgmt Start Date |
|
F&B Outlets |
Outlet |
Meal Period |
Revenue Ctr No. or Acct Product Code |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Systems Interface
Use the chart below to record the Software Name and version currently used at the hotel along with the replacement system (if applicable).
|
Current System |
New System (include projected installation date) |
PMS
(Name & Version) |
|
|
POS
(Name & Version) |
|
|
Time Clock Software
(Name & Version) |
|
|
Source of Daily Market Segment Data |
|
|
Source of Daily F&B Covers & Revenue |
|
|
Sales & Catering System
(Name & Version) |
|
|
Source of P&L History
(i.e. accounting system) |
|
|
Items to collect:
Use this chart to check off items as they are collected.
|
A Sample of Current Daily Report – PMS report, Excel workbook or other system report used to report daily statistics and revenues to management. |
|
A printout of all Transaction Codes and Descriptions in PMS. (e.g. For Fidelio, print the ‘Department Code – Number Order’). |
|
A listing of all Market Codes defined in the PMS; with Market Segment they roll into. |
|
A sample PMS close out report such as the Balance Check Report in Fidelio or the Detailed Trial Balance in Opera. |
|
A sample of a close out report from the POS. This should be whatever report(s) used by the accounting dept to create the current Daily Report. |
|
A listing of all positions (by code) defined in the Time Clock system. |
|
A sample daily report from the Time Clock system showing Position Code, Hours Worked and Expense (e.g. in ADP Time$aver, use the Hours Worked Summary for one day). |
Questions to Ask:
Question |
Answer / Comment |
Are the PMS, POS and Time Clock systems connected via a common internal network within the hotel?
|
|
Do the accounting office computers have direct access to the server folders containing the data for PMS, POS and Time Clock? Is so, what type of database is it (e.g. .dbf, .mdb, .mdf)?
|
|
Are there ‘export’ features in the PMS, POS and Time Clock software which enable the dump of daily data to an external data format (.txt, .csv, .xls)? |
|