Excel Budgets

When speaking with Excel students, one of the most commonly asked Excel questions is: “How do I create a budget?”. My advice would be, if your boss asks you to create a department budget your first question should be “Okay, will the CFO provide a template for this project” (the answer should be yes). The second question might be (if there is no template), “can I base my forecast on a previous spreadsheet you provide”. However, assuming there is no template or previous spreadsheet, this tutorial will delve into the topic of basic financial modelling with Excel.

Budgeting is a broad topic and the approach taken will vary depending on the type of business. Budgeting for a manufacturer is not the same a budgeting for a non-profit organization. For simplicity this tutorial will focus on a Finance department in a small organization (20 employees) that sells a service. For readers who require a manufacturing budget or a marketing budget, please email me your requests and I will improve this tutorial in the next iteration.

Components Of A Budget

A typical budget will likely include income and expenses. For simplicity, this example will ignore the income components and focus on modelling the expense items.

When constructing a budget for expenses we typically categorize departmental expenses in to two categories: operating expenses and non-operating expenses. Operating expenses typically fall into two categories: direct costs and indirect costs, which may be further classified as fixed or variable in reports.

One of the most common direct fixed costs in your budget should include items like salaries and rent. Direct variable costs may include travel and mail. As much as your department has direct costs, they also account for indirect costs like electricity and selling, general, and administrative expenses. Though it is unlikely your boss requires this much detail, in this tutorial I also include a capital expenditures (CapEx) budget, which includes depreciation on computers and furniture.

Where a budget needs to show an annual number, this value is typically based on monthly details. Think of the annual budget as the end result of calculating each department’s monthly expenses. Your budget will likely be consolidated with other departments budgets and show up as line items on the income statement, balance sheet, and used in financial ratio analysis.

Though it’s possible to generate an entire budget using a single worksheet tab, best-practice would be to separate each line item to its own sheet tab and consolidate that data into the front summary budget sheet. The CFO may then take five departmental workbooks and consolidate those budgets into a ‘master’ workbook.

Download the sample budget workbook

Step 1: Start With Employees

Suggestion: When viewing a spreadsheet you did not create, I recommend beginning your review by selecting the Formulas tab of the ribbon > Show formulas command. The keyboard shortcut to show formulas is hold the Ctrl key, tap and release the ~ (tilde key – upper left of keyboard). Ctrl~ a second time will hide the formulas and show the values.

The second worksheet in this example lists employees by job title, their annual salaries as a variable input, and formulas to show these salaries as monthly costs, with quarterly summaries as well. When modelling be aware that a well designed model should use color, borders, and bold fonts to improve readability. In this model blue numbers are variable inputs. Light grey cells are formulas based on the variable inputs. On the personnel sheet, several key cells use named ranges to simplify formulas on subsequent worksheet tabs. Best-practice would be to show a legend key that lists the named ranges. To improve readability of the quarterly values, consider using the group and outline feature to allow your boss to easily collapse monthly values to quarterly summaries.

In this tutorial the company is generous and pays 100% of a benefits plan, which should then be reflected in the budget. Cell A10 is a direct fixed expense value that feeds the calculation in cell S10. Notice in cell A10 I recommend a custom cell format to include the words Per Employee in the label.

Note that the end result of step 1 is the value on the Personnel sheet in cell S12, which feeds cell R2 on the Operating Expense Budget sheet tab.

Step 2: Calculate the Operating Expenses

Where a general ledger would break operating expenses into monthly items, in this example I have annualized the data (for simplicity). The total annual operating expense items on the OpEx Details sheet tab produce a value that feeds the Operating Expense sheet tab cell R3. This example lists common operating expense accounts but your own list will be different. Also, you should ask if required to include Selling, General and Administrative allocations in your own budget.

As a final note, although Excel offers the ability to add comments to cells, notice on the OpEx Details tab and the CapEx Details sheet tab I have included a column labelled Notes. I believe that a Notes column adds clarity and improves readability compared to using Excel’s notes or comments feature. Notes should list any assumptions that are not obvious.

Step 3: Calculate Rent Expenses

It is highly unlikely that your boss expects you to provide your rent expense in your budget. Rent is a part of operating expenses listed in the cash budget, which will impact the Cash line item on the balance sheet.
On the RentExpense sheet tab we lay out variable inputs to calculate the total rent allocation in cell Q9. To improve the readability of the formula in C9, the model uses named ranges in column B.

The cost per square foot will likely be influenced by at least two factors: 1) The building’s street address; 2) The class of office (A, B, C, etc). In this example I show an average price for a class A office in midtown NYC.
Disclaimer: I am not a Certified Public Account. In my opinion, if your company owns the building and does not pay rent, then consider using the property tax plus property insurance plus maintenance costs plus building salaries here to approximate a value for the cost per square foot.

Step 4: Calculate Depreciation

It is highly unlikely that your boss expects you to forecast your department’s depreciation expense allocations. Depreciation is a non-cash operating expense will affect the Asset line on the balance sheet.
To calculate depreciation, we need a list of capital expenditures, which are items that have a life longer than one year and are amortized according to IRS guidelines. CapEx items often include computer systems, telephone systems, copiers, and furniture. In the Excel Class budget the CapEx details sheet tab lists these one time expenses and assumptions.

To obtain the annual value, the Capital Expenses sheet tab lays out the line items as Computer, Furniture and Miscellaneous, given that some items have different depreciation requirements.

Of course, this model still has room for improvement. SavvyExcel developers will create a separate worksheet tab labelled ‘Assumptions’. In this model there are many formulas where I have hard coded a 20% allocation for shared resources. If that allocation were to change, I would then need to update many formulas. Were I to reference a value in a cell on an allocation assumptions worksheet then I would only have to change the value in a single location. This model does not include a seasonality factor. If I had included an assumptions worksheet tab, for some businesses I would include a seasonality multiplier. A seasonality multiplier would allow us to increase or decrease some variable operating expenses depending on the month.

I hope that some of you will find this budgeting exercise helpful. Thank you for your time! If you find any errors or omissions in this Excel budget example please contact me with proposed corrections or suggestions.