Corporate Finance: Part 4 – Model Set-up
Share
Introduction
This blog is the fourth in a series I am producing once a week. There are many topics we can discuss under Corporate Finance. This series will include a subset of topics, including the following:
- Chart of Accounts
- Project Costing
- Reporting
- Model Set Up
- Forecasting
- Budgeting
- Program Based Fixed Asset Management
I have another blog about Data Analytics (Data Analytics: Part 2 – Set Up the Model) which goes into how I set up a model for analytics. That blog goes into how I format my tabs in Excel. Please read that blog also so I can focus on financial modeling specifics in this blog.
What makes a good financial model? I can think of some key components I start with:
- Organization: I have specific tabs that you will see in most, if not all my models.
- Verification: Can be a verification tab, or many times I verify specific information in the tabs.
- Scenario: Should have at least two, most do plan and a stretch plan, some add worst case.
- Drivers: What are the key drivers of revenue and cost to the organization, identify them early on.
- File Naming: Version control is important; we will get into when and how.
- Comfort: Yep, once you get started, you won’t want to stop. Have healthy snacks, drink and a comfortable place to work on the model without distraction.
A simple model will take me less than a day to create, while more complex models take me a few days. Now it just comes naturally but expect to do it a few times before you get fluid. Because complexity is based on specific organizations and the drivers behind those numbers, we will be more focused on what it takes to make a strong simple model.
Model Set Up
What makes a model a "good model"?
-
Organization: When I create the blank excel sheet here are the first tabs I add:
- Inputs: This is where I put numbers that feed into the model at various tabs. I usually break it down into categories like Labor Rates, Employee Benefits, Utility Rates, Tax Rates, etc.
- Summary: Ultimately this will have all my reports/graphs for presentations. I take out the gridlines and as I add reports, then I make sure to organize for copy/paste into presentation without manipulation. It is also print formatted with header/footer and page breaks for a perfect printout set.
-
People: Rows are broken down between the following categories: Key Dates, Employees, Contractors. Columns are grouped by Description, IT Needs, Months for Count, Months for Costs (I will sometimes later have multiple ‘Months for Costs’ to the right for employee benefit calculations and/or for scenarios)
- Key Dates: list out the drivers for major hiring. Across the columns ‘Months for Count’ we will use words and/or background colors for when those occur. I freeze panes one row below the last Key Date, and group them so I can hide.
- Employees: This group will spread over 4 columns, including (in order from left to right) Position, Department, Name, and Role. Department is used to sum by department below this group. Name is “TBD” if pending a new hire. The ‘Role’ is used to drive the rates for calculation. Rates are looked up from the ‘Input’ tab.
- Contractors: This group header follows the same as ‘Employees’, except there is no role. Also, same as ‘Employees’, the department is important include contractors in the people counts by department.
The Columns are grouped:
- Description: Mostly used in reference to Employee and Contractors rows, see above for how I lay these out.
- IT Needs: This will feed into the IT tab, create as many columns as you need for IT licenses and hardware. When an employee needs one or more, mark a ‘1’ in each column the employee needs. Example, do they need laptop, MS 360, Adobe Photoshop, Engineering Software, ERP access, etc. Any IT expense driven by employees.
- Months for Count: Each row should be one position, I normally do not group based on position because I like to monitor by employee. I enter a ‘1’ for easy use in calculations. If existing employee, then they are ‘1’s from the starting month. If new hire, then the ‘1’ starts on hire month and extend to end unless a temporary employee. If an employee is planned for promotion, then a new line added and the ‘1’ ends at the month of old position with a ‘1’ the next month for new position at different cost.
- Months for Costs: Using the ‘Months for Count’, can some wages by employee (if known) or can be by Role which is linked to an average salary in the ‘inputs’ tab. ‘Inputs’ tab can also have burden costs (% or per month $) which can feed into column groups of costs further right of the ‘wages by month’.
The following are created with the following column set up: create the tabs, then select all the new tabs (listed below), collapse columns A-D to 30 pixels. Then add the dates on top for all tabs to be the same. ALWAYS have your Annual Totals as a group to the left of your months. Your months should NOT have annual totals after each year transition:
- Cap&Depr: For adding equipment and other depreciable assets. I run the list down based on department, then run asset acquisition and depreciation totals under the list.
- Facility: For facility costs, starting with leases, then the costs to run and maintain the facilities. I use the ‘input’ tab for ‘cost per’ drivers of facility costs.
- IT: I like to group by hardware and software. The top of each group starts with existing, then existing we plan to drop sometime soon, then what is new. Make sure you pull the employee’s needs to calculate specific IT needs. If you have a hardware life policy that triggers replacements, be sure to layer those in too.
- GM: Gross Margin is a tab I use for all Revenue and COGS. You may need more than one tab to calculate, but all should run into ‘GM’ tab for a consolidated view before it goes into the financials. Keep it as simple as you can, only detailing out where there are key drivers in the plan. Note that COGS accruals also go into here for P&L, like warranty.
- Project: List all projects and the costs associated. Assuming you are going to also summarize by department, I usually group accordingly.
- IS: Start with basic groups of Gross Margin, Expense and Other Income & Costs. If you have direct entries into the financial, always show with a light-colored background, otherwise should get data feeding from other tabs.
- BS: Always starts with Assets (Current and LT), Liabilities (Current and LT), Equity then lastly your KPI calculations.
- CF: I love the smell of a good cash flow in the morning! Income less depreciation and all those key balance sheet changes. Go positive!
- Verification: I have gone different directions on this and sometimes do both: Build into each tab and/or summarize in one tab. Anything that can go wrong will. You must layer in simple verifications throughout your model. Things like, if the balance sheet doesn’t balance, or the cost per ‘driver’ in the ‘IS’ tab is not within acceptable amounts. Color code with conditional formatting so it is very easy to see the problem.
- Scenario: When running models, always understand the risk areas of the model that drive the final results. Then either build into the tab or create copies of tabs that require a change for scenario. Use ‘Input’ tab to drive deviations as much as possible for a centralized control point.
- Drivers: Make sure to build drivers into the model, don’t calculate off the worksheet to only input result. The drivers are important to use in calculations throughout the model.
- File Naming: I always put the date at the end of my file ‘yyyymmdd’ for major changes. If using a portal that tracks versions, that helps, BUT I still do file name changes at key changes of the model or when the report being used for a specific high-level meeting. I always like to be able to pull up old reports that were presented and if you keep the same format, you can easily run comparisons.
- Comfort: I like to isolate myself as I build models, to minimize interruptions while formulating. Get in the headspace and knock it out quick. Got my snacks, water, and comfortable outfit to also help stay focused.
My approach is based on my experience, what is yours? I write these blogs in hopes that it may help others. I prefer to blog what I have learned from experience, not just a rewrite of what I found on the internet. I also prefer collaboration over critique, so please comment only if you have something constructive to add.
If you have other methods for setting up a model, please share.