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


excel how to vlookupinventory in excel sheetincome tax worksheet excelbell curve generator onlineexel vlookupdelete adjacent duplicatesexcel vba lookup value in arraynyuclahistogram tool excelv lookup examplenested if function examplespricing model template in excelfancy excel templatescheck excel for duplicatesduplicate remover excelv look ups excelexcel inventory spreadsheetwingding checkmarkhlookup excel 2007goldratt the goalexcel stacked chartshow do you do sums on excelexcel color schemeinventory excel spreadsheethbr tiplookup value excelrandom score generatorexcel formula syntaxhow to draw normal distribution curve in excel 2007excel if statement with 2 conditionslookup exelmake line graph in excelexcel simple formulasexcel fuzzy lookuperase duplicates excelaccenture management consulting career pathexample formulas in excelhow to draw normal distribution in excelduplicate in excel 2007vloopup in excelvlookup named rangehow to draw bar graph in excelthe goal by eli goldrattduplicate remover exceldata format in excelsetting formulas in excelexcel chart labelsexamples of hlookupmba at uclaexcel formulas cheat sheetpay stub excelhbs interview tipsexcel spreadsheet inventoryall formulas of excel 2007 with examplethe excel operator for greater than or equal to isgrade curvingeliyahu goldratt booksexcel duplicatesfind duplicates in excel columnsindex matching excellocate duplicates in excelexcel how to create histogramexcel tracking templatestrue false excel formulavlookup range lookupmr excel formulasms excel lookup function exampleexcel 2010 compare two columnsvlookup will not workcreate a lookup table in excelucla business school requirementsinventory formula excel
\n