Budgeting Basics and Beyond

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

1. 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

2. 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:

1. 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

2. 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 ...