Recently, I led a program that encompassed several different business units (called “properties,” in our jargon) with differing objectives. Several of the projects within the program replaced legacy software with our corporate standard, while others were upgrades. Within the upgrade category, compliance with PCI standards was the primary business objective for some properties while others were already PCI compliant. In the latter case, improved functionality and environmental standardization were objectives. Some properties had hardware refreshes, others did not. Several properties requested hardware and\or software that was not essential to the installation itself, but was required to implement new business procedures. The software and professional service discount percentage varied depending on the types of install.
From the initial estimate to final budget approval, management requested cost modeling in three views: based upon request type, for the program (as a whole) and for the individual components. It was essential that the different components were broken down in enough detail to provide what was requested without entering the data in multiple places, in order to eliminate transcription errors, and also summarized to provide overviews without recalculating. Here are all my data sets and how I organized them. You can download the sample Excel spreadsheet with the calculations and links.
Sample Budget Template
Costs that varied by property – Pricing for certain items varied based on whether or not the property had an existing agreement with the vendor. Those properties that needed only upgrades were priced according to their existing agreements, while new installations were priced under the corporate agreement. These items included:
- Licenses
- Second and third year Maintenance
- Professional Services
Costs with a common basis – Unit costs for other items was independent of the existing agreements, but total cost varied by property, based on scope. These items included:
- Hardware by type (printer, PC, credit card swipe)
- Hardware Tax
- Interface software & installation labor
Costs that varied by Time– Unit costs for several items varied by start date, resource assigned and location. These items included:
- Prorated First Year Maintenance
- Vendor travel costs (airfare, travel to airport, parking, transportation in town)
- Organization of Data
The cover tab incorporates line item costs as well as subtotals and totals by category. A tab for each property contains more detailed breakdown of costs. Additionally, there is a notes and a revisions tab for quick reference regarding research, assumptions, decisions and changes to the budget.
Cover Page Data
- Program Costs
- Total program costs (with and without first year maintenance fees)
- Cost category
- PCI required
- Suggested (not PCI required)
- Additional Requests
- Totals by request type
- Software including new installations and upgrades
- Interfaces
- Professional Services (broken down by type)
- Hardware (broken down by type)
- Vendor travel
- Maintenance (first and second year)
- Cost per property
- License Totals (including main software and interface)
- Professional Services (by type)
- Hardware (including tax)
- Vendor travel\lodging
- Maintenance (by first and second year)
The heading showed the Program title, key to the color codes used in the high level objective and the most frequently requested totals.
In the main grid, I listed each property in a single row and color coded the name by the high level objective. Along the Y axis, I listed the cost breakdown for each component. If there were further breakdowns in cost, the reader was directed to “See property tabs.” The cost categories were subtotaled for each property and for each objective category.
Property Page Data
License and Maintenance cost breakdown
Lessons Learned
Things I would change
Move the detailed training requirements to the property tab and keep one placeholder on the overview tab. Determining the number of days of training per property required multiple iterations of requirements discussion with the business stakeholders and vendor. Additionally, the training cost was the same per day but the training categories were different, and were referenced by category in billing and requirements gathering.
List the full year maintenance cost in the property tabs and calculate the prorated amount for each property on the cover page.
Things I would leave the same
Have multiple total sections. Not only are having subtotals & totals spread throughout the spreadsheet convenient, it was an intuitive data checker. If the main totals didn’t match, the calculations were off somewhere. I totaled across both rows and columns.
Track revisions. This was especially handy when costs or requirements changed during planning and executives wanted a quick answer regarding why the estimates were different.
Document the findings that influenced costs. For instance, when a property had already paid for the software but not for the professional services. Documenting those details ensures you have a quick answer when someone asks if you “forgot” to include the software costs.
The Excel spreadsheet is available for download here. Happy budgeting!
