Resource Allocation Tables
In my last post, I discussed tying expenses to hiring. One of the more powerful ways I extend this is with a resource allocation table. It really saves a lot of time while removing a real risk factor when it comes to keeping the model in synch with changes in the hiring plan.
For one of my latest clients, I defined every employee and contractor as belonging to one of the following roles:
- none
- standard
- traveling
- field
- it
I then used a matrix to indicate what operating and capital expenditures were tied to each role. For example, traveling personnel were allocated a desk, telephone, laptop computer, cell phone and remote internet access. Consultants covering their own expenses were assigned to role "none".
My department worksheet always has a few rows to calculate headcount. This includes a separate row for the headcount associated with each role. Each expense category based on resource allocations is then defined by a pair of rows:
- A row to define allocation in units, which is calculated by matching the allocation definitions in the resource allocation matrix with each role and multiplying by the headcount in that role in the given month
- A row that translates the unit allocations into dollars, multiplying units by ongoing monthly expense and multiplying increases in units by one-time expense
Saving Time with Personnel-Related Expenses
Recognizing every single expense that can be tied to specific hirings is one way to build a business plan spreadsheet more quickly. It is worth the time to build a worksheet that defines rules for tying both operating and capital expenses to roles. This is really a very simple worksheet that lists each expense item and the one-time and ongoing monthly amount. For more sophisticated models or complex funding situations, additional rules can be added if appropriate, such as a time delay (start some number of months after hiring) or a funding contingency (such as applying the rule only after the second funding round). Example expenses include:
- travel
- software licenses
- rent
- supplies
- printing
- shipping
- telephone
- cell one
These are just a few examples. I try to define every expense item I can in this way.
On my department worksheet, I then build out the expense with a rule that ties the information on the expense definition worksheet to the headcount for the department.
The real time savings in this approach comes later, when the model gets changed. Every time that headcount is added or subtracted, the entire model adjusts all related expenses. Similarly if an expense level is adjusted (a reduction in travel expense for example) it is automatically carried across all departments.
Annual Summaries of Month-By-Month Worksheets
I gave up presenting annual numbers on month-by-month worksheets a long time ago. I don't like the side effects from either of the two approaches I've seen used in models given to me:
- Co-mingling annual numbers on different rows makes for a worksheet that is very hard to read.
- Maintaining annual columns, especially co-mingled with monthly columns makes formulae very hard to edit because of all the extra copying and pasting to get around the annual columns.
I prefer to have a separate annual sheet, which I typically give the same name as the monthly sheet appending the word "Summary". Most of the time I need to create these summary sheets anyhow for reporting.
In order to make these summary sheets easy to maintain as the underlying monthly worksheet changes, I have come up with a pair of Excel formulae. In the following examples, the monthly worksheet is named Rollout and column D is the first month column on Rollout:
- Summation of Twelve Monthly Amounts:
=SUM(OFFSET(Rollout!$D21,0,12*(COLUMN()-COLUMN($D14)),1,12))
- Copy of a Year-To-Date Value from a Cummulating Row:
=OFFSET(Rollout!$C6,0,12*(COLUMN()-COLUMN($C4)))
I have recently come up with an extension to this approach that increases the visibility associated with varying model assumptions. I now put all my assumption fields on the summary worksheet and then refer to them in the formulae that use them on the monthly sheet. As the results are recalculated, they automatically boil up into the summary numbers, allowing me to see the effect over several years without having to scroll across the worksheet. This keeps my involvement with the monthly sheets constrained to when I am actually building them or modifying their inner workings.
Categorizing Expenses
In my previous post I discussed how departments are used to group expenses. However, management, investors and bankers will want to see expenses grouped by the type of expense as well. Simple examples of expenses frequently managed this way are personnel and travel expenses.
I handle this by including two Profit and Loss statements in my model, one with operating expenses grouped by deparment, the other with operating expenses grouped by category.
I do two things to make sure that these statements stay in synch.
- Whenever a number is used in both models (such as a sales itemization), I set the value in the category statement to the value in the department statement rather than having both calculated using the same formula from underlying worksheets.
- Using conditional formating, I include a line on the category worksheet that only becomes visible (in red) if there is a difference in net profit between the two statements. If I see red numbers, I know I have a modeling error.
There is another important reason for a P&L by category statement. Some departmental expenses may really be Cost of Sales. A reclassification worksheet in between the two P&L statements can be used to transfer expenses to Cost of Sales on the category statement. This has the advantage of keeping the expenses in the department for expense management purposes, but showing them as sales dependent when varying sales growth rates.
What Is a Department?
This sounds like such a simple question, but it is not. A wrong answer can result in signficant model re-engineering at the worst possible time.
Departments exist for two reasons. The obvious one is that departments are the classic technique used to partition expenses into categories that define spending such as development or marketing. The second reason is to provide a framework for expense management. If you do get funded, your model becomes your budget and investors will hold you to your spending plan. Department worksheets become spending roadmaps for departmental executives. The granularity caused by detailed spending management can result in too many expense categories on the P&L. Sometimes the model has to stop short of keeping every manager's expense structure in a separate department. Sub-departments in a subordinate model might make more sense if this is important.
From a modeling standpoint, you want as few departments as possible to keep it simple to make quick changes at the request of management and potential investors.
Products and Services - How Much Detail?
What belongs on a products and services worksheet varies from one initiative to the next. This is the one worksheet where I strive for as much detail as possible. I've learned the hard way that whenever I shortcut this process I wind up having to swag line items when it comes time to product the P&L. Unlike any items I can incorporate on the product and services worksheet, "swagged" entries require manual updates every time sales assumptions are changed.
This is the checklist I use to try and make sure I don't miss anything. Obviously, not all items relate to every business initiative:
- Pricing
- Average suggested or unit price for each product
- Package pricing for product combinations
- Average discounts from suggested list price for products or product combinations
- Sales
- Product combinations sold
- % of unit sales for each product combination
- Target market share on at least one of the combinations
- Target market size
- Rules for add-on sales
- Rules for renewal sales
- Rules for referral sales
- Sales-Related Expenses (by product or product combination)
- Lead generation expense
- Commissions
- Other marketing expenses
- Job costs including labor, materials and fees
- Other direct expenses such as fees
- Royalties and revenue allocations
- Insurance premiums tied to revenue such as general liability
It's All Starts with Detailing Products and Services
Most of the financial models I have seen focus much more on expenses than sales. Yet every venture investor I have ever dealt with challenges the sales assumptions and asks to have the model adjusted to what they consider "believable".
The first step in developing a model with the sensitivity to react is to develop a worksheet that accurately defines every product or service. This sheet then becomes the basis for month-by-month calculations of unit sales, revenues, repeat business, referrals, cost of sales and other sales-related expenses such as some insurance costs.
This is usually much more detail than is initially considered in building a model, but it has two really big payoffs. First, it becomes relatively straightforward to tie all this detail to the driving forces behind sales growth (such as direct sales staff, advertising spending and response rates or direct mail and cold calling). Second, I have never failed to see this approach lead to learning quite a bit about how the business will behave as it grows.
Modeling Funded Growth without Knowing When Funds Will Be Received
One of the trickiest aspects in using Microsoft Excel to model a young company for funding is realizing that the actual date of the funding cannot be predicted. The first reaction is to model starting with the funding month rather than a calendar month.
While this is the right approach, it does not account for the fact that until the funding is received, the company will grow according to a different set of rules based on existing momentum and spending. These rules on sales momentum can be kept in a separate worksheet based on calendar month.
Upon funding, the funding month from this calendar-based worksheet becomes the starting point to the sales rollout in the funding (actually post-funding) model. This can be achieved by storing the starting month and using it in Excel's OFFSET formula to port the calendar-based information into the right columns in the funding model.
It is actually a little more realistic to assume that not all growth patterns can change instantly upon receipt of funds. In all likelihood additional offset months are also needed to define the number of months post-funding that it takes each funded program to become implemented. The result is a model that uses the initial offsets to define the starting point of the plan, a transition where pre-existing growth patterns are still in effect, and a growth period caused by funded initiatives.
