19.1. Use of a Spreadsheet Program for Financial Modeling and Budgeting
Spreadsheet programs such as Excel and a stand-alone package such as Up Your Cash Flow can be used to develop a financial model. For illustrative purposes, we present some examples of projecting an income statement.
Projecting an Income Statement
We will show how to develop a projected contribution income statement (in Case 1) and a projected traditional income statement (in Case 2) for two hypothetical firms:
Case 1
Sales for 1st month3$60,000
Cost of sales3 42% of sales, all variable
Operating expenses3 $10,000 fixed plus 5% of sales
Taxes = 46% of net income
Sales increase by 5% each month
Based on this information, we will create a spreadsheet for the contribution income statement for the next 12 months and in total. This is shown in Exhibit 19.1
We will do the same as above, assuming that sales increase by 10 percent and operating expenses = $10,000 plus 10 percent of sales. This is an example of "what-if" analysis. This is shown in Exhibit 19.2
Case 2
Delta Gamma Company wishes to prepare a three-year projection of net income, using this information:
The 2007 base year amounts are:
Sales revenues $4,500,000 Cost of sales 2,900,000 Selling and administrative expenses 800,000 Net income before taxes 800,000 Use these assumptions:
Sales revenues increase by 6% in 2008, 7% in 2009, and 8% in 2010.
Cost of sales increase by 5% each year.
Selling and administrative expenses increase only 1% in 2008 and will remain at the ...
Get Budgeting Basics and Beyond now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.