The attached model is used as the baseline example for my How to Build an Excel Model series of posts. It has been designed to illustrate the key principles and concepts behind effective model building. If you plan to download the file, please read the series of posts here to gain further context:
How to Build an Excel Model: Key Principles
How to Build an Excel Model: Tab Structure
How to Build an Excel Model: Step by Step
The rest of this post acts as documentation for the sample model.
Click below to download the MBA Excel Sample Model v1.0
Disclaimers & Notifications
- The data included in this model is entirely fictional and does not represent any real world company
- The file is being distributed as is – while I’ve done my best to remove errors from this file, I am not responsible for any subsequent errors made if this file is used for any other purposes
- Please do not use this file for commercial purposes without written permission
Background
The sample model was built around an unnamed company that sells three products: Nitro, Infinity, and Durango. The company is looking to build a five year forecast and get a one year historical view of its sales by geography. To conduct this analysis, we’ve been given a set of historical data points, important rates and assumptions, and a sales database that includes 2013 sales by geography.
Assumptions
Revenue – The model includes data on revenue by both product and geography. To forecast revenue, we look at total sales growth and based on that total value, allocate to individual products. These allocation percentages can be adjusted for each individual forecast year. It is assumed that the company expects certain products to become more prominent in the company’s portfolio in the future.
COGS – Cost of goods sold have been calculated and forecasted as a percentage of revenue. The forecast of COGS is performed by indicating percentage point changes to the historical COGS as a % of revenue figure. These percentages can be adjusted for each individual forecast year.
SG&A – All SG&A line items, with the exception of depreciation, are forecasted with a single rate over the course of five years. These line items are deemed as less important, so the model does not include the flexibility to adjust these rates year by year.
Depreciation – We assume that the company uses straight line depreciation and expenses approximately the same amount for deprecation each year. Within the scope of the model, we are also assuming that the company will not acquire new depreciable assets.
Interest Rate – I made a simplifying assumption to calculate interest off of the prior year’s ending cash balance. Interest rate calculations typically require a one year offset to prevent a circular reference issue.
Taxes – Taxes are calculated at 35% of Pre-Tax Earnings, which includes interest earnings from the company’s cash balance.
Outputs – The key outputs of this model are a five your forecast of the company’s P&L and a one year historical view of the company’s sales by city.
Excluded Items – We’ve assumed that the company has no debt and will incur no gains or losses outside of its normal operating practices.