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 courses los angelesvba generate random numberhow to make a random number generator in excelcompound if statement excelrandom usernames generatorhistogram chart makerexcel delete duplicate entriesvlookup based on multiple criteriacustom graphs in excelcreate macro exceladobe acrobat convert pdf to excelvlookup codeexcel vba memory managementexcel combine formulasincome tax spreadsheet templatesucla admissions portalexcel drop down list vlookupfemba uclaiif excelexcel formulas with exampleerase duplicates excela stacked column chartucla store ackermanremove gridlines excelduplicate rows in excelz distribution in excelvlookup with indirectsimple tax refund estimatornest functions in excelhow to highlight a column in excelexcel generate histogramucla gradesmultiple if statement in excelexcel 2010 delete duplicatesvlookup excel exampleucla pay scaleinsert tick in word documentif logical_test value_if_true value_if_falsevba else without ifsyntax in excelhow do i do a vlookupmba at uclaexcel remove duplicates based on two columnstask tracker templatecheckmarks in excelexample of countif in excelexcel table lookup functionquestions to ask at mba interviewchart legend exceleliyahu goldratt the goal summaryexcel power query examplesexcel formula reference cell abovehow can use vlookup in excelvlookup excel 2007 tutorialexcel writing formulashow to create a histogram in excelexcel compare dates in two columnssimple income tax estimatorexcel insert check markhow to copy formatting in excelhow to compare two column in excelexcel formula to compare two columns for matcheslookup vs vlookuphow to conditional format in excel 2013excel formula not blankshortcut key for pivot table in excel 2010microsoft excel offset functionucla anderson alumni directoryhow to randomize numbers in excelinventory formulas excelmicrosoft vlookupexcel duplicate cellsmicrosoft excel formulas tutorialtable pivot exceluniversal studios tickets uclaucla grade distributionexcel function formulasfind and remove duplicates in excelexcel sorting formula
\n