rusinfomos.ru Sample Model

by Matthew Kuo on May 21, 2013

in Excel, Excel Templates, Model Building

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

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 rusinfomos.ru Sample Model v1.0


rusinfomos.ru Sample Model v1.0

How to Build an Excel Model 04

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.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post:


Related pages


ms excel formulas list with exampleshow do you make a stacked bar chart in excelhow to create bar graph in excel 2010hlookup vbawhat are vlookups in excelucla mba programseliyahu m goldratt the goalexcel comparing columnseliyahu goldratt the goal summaryshortcut in excelusing rand function in excelexcel vlookup example different sheethousing construction cost variables excelbest book for excelnormal distribution graph in excelexcel alt shortcutsexcel formulas meannested if statements excel 2013laptops for mba studentsshortcut keys of excelbell curve normal distributionbell curve template excelzs associates salariesadobe converter to excelrandom name generator excelbest microsoft excel bookshow to use the lookup function in excelvlookup excel 2010 for dummiesvlookup function in vbarandomizing in excelinsert symbol shortcutcool excel graphshow to drag formula in excelsample spreadsheetshistogram excel bin rangelookups excelhistogram formulavba offset selectionvlookup training videoexcel formula for multiple cellsmaking a bar graph in excel 2010excel calendar generatormonthly calendar excel template 2014offset function in vbaexcel highlight duplicates in columnhow to construct a formula in excelthe default format that excel applies to numbers is thems excel formulas list with examplesportal uclahow to highlight duplicates in excelvlookup excel easy stepslabel chart excelmacro excel open filewhat is a nested if statementwingdings keysexcel find duplicates in columnh lookup excelpdf vlookup in excel with exampleexcel formula for random selectionhow to compare to columns in excelrandom name generator to pick winnerrumus hlookup dan vlookup excel 2007tracking calendar templatelibrary hours uclalookup formula in excel 2007how to fix formula in excelhlookup excel tutorialforecast excel exampleremove duplicates in excel 2007harvard mba syllabusmeaning of vlookup and hlookupduplicate remover excelhow to create a bar graph in excel 2010excel hlookup tutorialvlookup for datesexcel clustered stacked columntask tracker templatemyucla comexcel stacked charts
\n