Steve's Financial Modeling Tutorial

My Blog

Home
Who
Basics
Model Basics
Overview
Excel
Income I
Income II
Cashflow
Balance Sheets
Depreciation
Corporate Tricks I
Corporate Tricks II
Enron
Downloads
Income Statements II

Here's our second income statement:  


Download My Simple Financial Model - $9.95

Let's go through this new, improved income statement and you'll see how complex these things can get.

Stats

First, you'll note that there is a section at the top of the income statement called "Stats". You can call this whatever you want.  This is how I derive the revenue.  Often the stats are on a separate worksheet (especially if you plan on printing your model), but I left it on the income worksheet so I could show it on the web easier.

I know certain things about my lemonade business.  I know how much lemonade I am going to produce and I know how much a cup of lemonade will sell for.  I also know the ratios and costs of inputs.

So I listed all these statistics and started to make calculations.  Column D is my constants column.  I use it to store important figures for each row.  Sometimes folks hide this column or make it white.

Cups of Lemonade Produced:  I have a 5% monthly increase in production each month from a starting level of 2,000.  By entering a different figure in column D I can change the monthly increase to zero or even make a decrease.  I can change the starting level also.

Lemonade Price:  This is my selling price for every cup of lemonade sold.  I have a monthly price increase of =.08/12, which is approximately an 8% annual rate.  (I know it's not really, Mr. Math Police).  I can change the price increase or the starting price easily.

Sugar (cups):  This is the quantity of sugar I use to produce the production figure in the first Stats row.  You can see the formula in the spreadsheet.  I have a figure of .2 cups of sugar per every cup of lemonade produced.

Lemons (#): This is the number of lemons I use to produce the production figure in the first Stats row.  I figured 1 lemon per cup of lemonade.

Lemon Price ($): This is the price per lemon.

Sugar Price ($): This is the price per cup of sugar.

Revenue:  I created two lines for revenue and made them related.  You can model revenue any way you like.  I figured that some percentage of my revenue would be on-line and some would be offline.  So there's a constant in column D that separates the revenue.  Perhaps later I could have a different cost structure for on-line revenue (shipping, customer service).  Or perhaps my boss has a bug up his ass about having on-line revenue broken out in financial models.

Annualized Basis Revenue:  Row 13 is simply row 12 times 12.  This way folks who can't do the math in their head can easily see what each months revenue would mean if it you got the revenue for 12 months.  This simply makes the model more useful, though it does take up a valuable row of real estate on the screen.

COGS (Costs of Good Sold):  If you took micro-economics, you can relate to COGS to variable costs.  For example, if you buy a lemon for 10 cents and sell that same lemon for 20 cents, you've got 10 cents profit.  But you don't really, because you have to pay for some fixed costs like your salary, insurance, etc.  The 10 cents is variable because it varies with the number of lemons you buy.  If you buy 2 lemons it's 20 cents.  Some things, like electricity, often have a fixed and a variable component.  Sometimes it is difficult to figure out what's fixed and what's variable.  Head office staff, for example, is fixed until you get to some very high level of revenue and then you need to start adding managers, so there's a variable component.  Modeling this stuff can be tricky.  That's why they pay you the big bucks.  Right.

Gross profit: Gross Profit is also called Marginal Profit.  Even if your company is losing money, you might have marginal profit.  For example, if you buy a lemon for 10 cents and sell it for 20 cents and pay 50 cents in salary, you have marginal profit (you sold the lemon for more than it cost you), but you lost money overall due to your fixed salary costs.  Micro-economic theory says that competition will result in selling prices set at the point where your marginal profit is zero (price equals marginal cost).  Certainly that's where the pricing pressure is heading, but I think that theory is too simple to actually represent reality. Or maybe it's totally true all time in every situation and there's no need for us to do any more planning since we now know everything.

 

My Blog on Tumblr


 

Copyright and other other rights reserved by Steven Saltman. © 2002 and all future years until the end of time. Yes, I answer mail sent to me via ssaltman@gmail.com