The Nine Circles of CapEx Spreadsheet Hell
Most hotel owners will be receiving 2018 CapEx plans from their hotels in the next few weeks. Most of those CapEx plans will be submitted on Excel spreadsheets.
This is a perfect time (maximum pain levels) to consider The Nine Circles of CapEx Spreadsheet Hell, and develop strategies for a move to CapEx Planning Paradise.
- What is wrong with using CapEx planning spreadsheets?
- What are the alternatives?
- What specific steps should owners and operators take?
Avoid CapEx Spreadsheet Hell!
Hotel owners spend an average of 7% of gross revenues on CapEx, every year; a very significant reinvestment of scarce owner’s capital. Most management contracts require the hotel operator to deliver a CapEx plan to the owner sometime between August and the end of the year. The description of this “CapEx plan” is usually vague; anything will do to meet the requirement. These CapEx plans are often prepared and delivered in the form of an Excel spreadsheet.
If your properties are still using spreadsheets to manage CapEx planning and administration for a single property, or thousands of projects for a large portfolio, you have needlessly set yourself up for a trip to CapEx Spreadsheet Hell!
The Nine Circles of CapEx Spreadsheet Hell
Using spreadsheets to manage CapEx will eventually lead owners and operators into each of the following Nine Circles of CapEx Spreadsheet Hell:
Regenerating CapEx plans every year as a stand-alone process requires hours of dedicated time. Management at the property, regional, and corporate level must identify, describe, scope, price, and prioritize each project at each property.
Hotel owners are faced with individual spreadsheets from each property and/or multiple management companies which must all be compiled into a common format for portfolio level review and analysis.
Inconsistency in project titles, scopes, and budget estimates make it extremely difficult to estimate CapEx costs across the portfolio, compare costs between similar properties, and aggregate purchasing and contracting across the portfolio to drive the lowest pricing from suppliers and contractors.
In a recent CapEx submission for a portfolio of 180 hotels, “PTAC” replacement projects were listed (and “speled”) in 26 different ways. Some of the PTAC projects included tax, freight, installation, and disposal; some only the purchase price of the PTAC unit. Some project costs were based on national account pricing; some on last year’s purchase costs; some on local supplier estimates.
Spreadsheet numbers entered as text, formulas overwritten or corrupted, and errors in column ranges can cause errors in individual projects, as well as property and portfolio totals. Checking thousands of projects for spelling, math, and spreadsheet entry errors is both mind-numbing and time consuming.
CapEx planning often conflicts with the preparation of annual operating budgets, and is usually considered a lower priority for management time. CapEx plans are often hastily assembled at the last possible moment.
This time conflict is exacerbated by the stand-alone nature of spreadsheet based CapEx planning.
Spreadsheets typically cannot be integrated with other systems that are used to purchase, administer, manage, and maintain capital assets.
- Asset age and condition data from the Maintenance Management System (computerized or manual), must be transcribed into the CapEx planning spreadsheets.
- Fixed asset accounting system records are typically not available at the property level where CapEx planning is taking place.
- Reflecting CapEx approvals, funding, and procurement processes in hotel accounting systems becomes a manual transcription process.
- Project management systems typically cannot be simply updated from approved CapEx plan spreadsheets.
Updating fixed asset and maintenance records when assets are replaced does not flow smoothly from a spreadsheet based CapEx planning process.
Capital equipment data (age, useful life, maintenance history, cost, etc.) must be transcribed from equipment records into the CapEx planning spreadsheet. It is nearly impossible to maintain this integrity as it is transcribed from each individual equipment record to each individual CapEx project.
Often, properties resort to guessing or making up this data rather than finding and re-typing multiple data points for each project.
Data Entry and Reporting
CapEx planning data and reports need to be formatted differently at each stage of the process. Property level planning requires input fields that control consistency and automatically correct errors. Portfolio level review requires less individual project detail, but accuracy and consistency on project names, scope, costs, and timing.
Collaboration and Coordination
Good CapEx planning requires an iterative process that fosters collaboration between management staff at the property, regional, and corporate levels, subject matter experts, and Ownership. It is very difficult to keep track of the status of review, requests for information, plan revisions, approval, and execution when the plan is on a spreadsheet that must be edited and redistributed with each change, and at each step of the process.
Spreadsheet based CapEx planning makes it difficult to confidently estimate CapEx expense, cash flow, and project timing. This lack of confidence leads directly to indecision at the Ownership level, delayed approvals, and difficulties executing CapEx in a timely and efficient manner.
Finding CapEx Paradise or “isn’t there an App for this?”
You wouldn’t consider using a spreadsheet to manage your contact list; manage depreciation schedules for fixed assets, or manage your guestroom inventory; you probably use software applications specifically designed for each of those functions.
Cost effective CapEx planning and administration technologies are available as standalone systems, as part of an existing system (accounting, maintenance management, etc.), or agile integrated systems that can connect across multiple systems, organizations, and levels.
Additionally, “Big Data” technology has enabled leveraging existing property level systems and process to provide a strong foundation for portfolio level CapEx planning, administration, and execution. Increasingly, data can be shared across systems seamlessly, eliminating redundant data entry, and providing for greater accuracy, reliability, and efficiency in CapEx planning, administration, and execution.
Here are the seven steps to find CapEx Planning Paradise:
Systems Based Continuous Process
Implement an integrated CapEx planning and administration technology that supports continuous planning.
Commit to developing and maintaining a ten year forward looking CapEx plan for each of your hotel properties, and put processes in place to ensure that projects are entered and discussed on a regular basis throughout the year.
Support your continuous process with standards that help bring consistency to your planning. Define what is considered CapEx, and which non-CapEx expenditures you would also like to include in the planning process (e.g. large periodic maintenance expenses).
Develop a dictionary of project categories, areas, names, and scope descriptions that supports consistent planning from year to year and hotel to hotel. Decide how budgets should be presented, and how budget add-ons should be calculated (e.g. design, tax, freight, fees, etc.).
Design your system so that data resides in the appropriate system, and is available to the CapEx planning system as needed. That is, avoid replicating fixed asset data in the CapEx system if it is available in the maintenance management system or accounting system.
Choose a system and process that provides transparency across linked systems and parties to the process. Management staff at the property, regional, and corporate office should have access to the same information as Ownership, asset managers, auditors, etc.
Transparency is critical to any collaborative planning process.
Long Term View
Ensure that the system captures all large and/or recurring CapEx projects; for example renovations, fire alarms, chillers, boilers, roofs, exterior paint, etc. Scheduling these projects out ten years provides a strategic long term view of the properties capital needs, and supports effective portfolio planning by Ownership.
Focused Review Process
Set up the CapEx system to support the review and approval process required by Ownership, as well as providing for incremental review of individual projects, and focused review of “classes” of projects. Rooms renovations require in depth review and planning; PTAC replacements do not.
CapEx approvals and administration should mirror this focused review. Senior management needs to review the scope of every renovation, but does not need to approve every replacement PTAC.
The CapEx system and process should support portfolio level planning by both the Management Company and Ownership, and should allow aggregation of purchasing and contracting across the portfolio.
Having a strong CapEx planning system and process in place will help ensure that capital funds are only invested at their highest and best use, and that you will never again need to visit The Nine Circles of CapEx Spreadsheet Hell.
- Implement integrated systems.
- Adopt a continuous planning process.
- Insist on data accuracy, consistency, and efficiency.
- Require a transparent process.
- Take the long view.
- Focus CapEx review and administration.
- Negotiate best value through portfolio purchasing.