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


nested if condition in exceloffset formula excelcountifs greater thanhow to vlookup with multiple criteriaduplicate rows in excelexcel histogram templateexcel graph makinghow to create a vlookup function in excelcompare two spreadsheets for duplicatesexcel matching formulaucla owatick for microsoft wordexcel formula cheat sheetwrite if statement in excelbuild a histogramcase interview zs associatesexcel formula percentage formattick in the box symbolvlookup across workbooksexcel advanced formulas examples excel sheethow to create lookup in excelmatch worksheetsiferror in excelformula vlookupexcel plot chartduplicate values excelconsulting names generatorhow to randomize in excelsyntax for vlookupvlookup errorshow to insert icon in excelexcel tutorial 2003conjoint analysis excelexcel formulas for beginnerscountif examplesformatting excel chartshistogram i excelexcel maskstacked chart excelnested if statement excel 2010you can identify duplicate rows in a table using theexcel paycheck templatecompound if statement excelfit gaussian excelexcel formula duplicatesexcel how to identify duplicatesserenity dental culver cityexcel equalsexcel blank cell valueexcel if statement examples textwingdings 2 downloadvlookup learninghow to create a vlookup in excel 2010vlookup explained for dummieshow to use excellexcel highlight duplicate cellsuse of hlookup in excelmultiple if statements in excelbeautiful excel graphswhat is the function of vlookuppivot tables vlookupsvlook in excelexcel vlookup multiple columnscumbersome tabexcel keyboard shortcut delete rowtask tracker excel templatevlookup command in excelninja notes reviewadvanced excel array formulasexcel spreadsheet shortcutscpk excel formulainventory formulas excelreplace formula in excel
\n