Model Setup
In this model excel tabs are dedicated to a specific functionality. E.g. there is a tab that is dedicated to accounts receivable, forecast bookings, deferred revenue, etc. For Profit and Loss, dedicated excel tabs are made for revenue, bookings, operating expenses, wages, and non-operating expenses.
Notes on the Balance Sheet
Not all of the categories not balance sheet options on the balance sheet have been trended. If the category is complex and big enough e.g. accounts receivables or deferred revenue, there will be a tab that is dedicated solely to that category. It requires a little more effort, but it means that your model is more accurate and it will help to develop the knowledge you have of these numbers.
Non-wage Operating Expenses
A tab has been dedicated to budgeting and forecasting operating expenses that excludes wages. At the top of the page, you are able to sum up your expenses by department, by different expense categories, and by major expense type, e.g. non-operating expense and operating expense. You can use all or only one of these summary tables depending on the detail level you require.
Below the summaries, you will find that there are rows that are dedicated to the forecasting of each department's expense. In previous models, there were tabs for each department, but that begins to be less efficient if formula changes or addition/removal of departments is necessary.
Many of the smaller expenses are able to be trended with relative accuracy. Five formula types have been created that help to automate the spreading of expenses over the periods. The good thing about a number of these formulas is that the expense moves to the right month if you adjust the start month of the forecast.
Formulas to Spread Expenses
Fixed - input an amount and it will forecast that same amount eternally
EOQ - End of Quarter - input an amount and that amount will only be placed at the end of each quarter, whether or not you have changed the model's date range. If you change the dates at the top, the expense will be places in the right month(s).
Spread Amount - enter an amount plus the beginning and end dates and this amount will be spread over that time frame.
Department Expense Formulas
Annually - enter a date (only the month will suffice) and an amount, and the amount will be placed once a year in the specified month by the formula.
Odd Month - input an amount and it will be placed in all of the odd months (May, July, September, etc.)
Spread Evenly - input an amount plus the beginning and end date, and the same amount will be placed in each month over the period that has been specified.
Conclusion
Combining these formulas will reduce the required hard coding for forecasting operating expenses at a more detailed level. Using this tab, it speeds up forecast update, and with SUMIF formulas, the roll up of expenses will take place automatically. Note that some travel expenses and marketing expenses which can be too variable won't work well using these formulas.
