Financial Projection Lab using attached Excel Template

FN2640: Week 3 Financial Management and Planning

Lab 3.1/Financial Projections

1

Genatron wants to estimate what will happen to its income before interest and taxes if its net sales

change from the 2012 level of $1,500,000. Refer to Genatron’s 2012 income statement below, where

the income before interest and taxes is $247,000 (EBT of $190,000 plus Interest of $57,000).

Assume that the cost of goods sold are variable expenses and that the other operating expenses are

fixed.

Income Statement 2012 2011

Net sales $1,500,000 $1,300,000

Cost of goods sold 900,000 780,000

Gross profit 600,000 520,000

Expenses: general and administrative 150,000 150,000

Marketing 150,000 130,000

Depreciation 53,000 40,000

Interest 57,000 45,000

Earnings before taxes 190,000 155,000

Income taxes 76,000 62,000

Net income $114,000 $93,000

a. Calculate the expected amount of income before interest and taxes for both a 10 percent

decrease and a 10 percent increase in net sales for next year.

b. Determine the percentage change in income before interest and taxes given your

calculations in Part a, and determine the degree of operating leverage.

Click here to access the worksheet for this assessment.

Source textbook: Melicher, R. W., & Norton, E. A. (2011). Introduction to finance: Markets,

investments, and financial management (14th ed.). Hoboken, NJ: John Wiley.

Submission Requirements:

Answer each problem in detail with a conclusion and results.

Submit your answer in a Microsoft Excel file, showing step-by-step solutions to all calculations.

Evaluation Criteria:

You will be evaluated on the following points using the rubric for your performance in this

assessment:

Did you accurately calculate the expected amount of income before interest and taxes for

both a 10 percent decrease and a 10 percent increase in net sales for next year?

FN2640: Week 3 Financial Management and Planning

Lab 3.1/Financial Projections

2

Did you accurately calculate the percentage change in income before interest and taxes

given your calculations in Part a, and determine the degree of operating leverage?

Click here to download the rubric that will be used to evaluate this lab.