CIA Configuration Guide

You are here:
< Back

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:

  1. Room nights and Room Revenue History by Market Segment
  2. Food and Beverage Revenue History by Outlet / by Meal Period
  3. Labor cost and hours worked by position
  4. 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)?