Week 6 Forecasting Financial Statements and External Financing Needed

Assignment: Interpreting a Financial Forecast

In this assignment the forecasted data is provided in Columns F,G,H, i.e., a completed forecast.

Examine formulas cell-by-cell to learn how the forecast process works.

Use CFW Chapter 3 as your guide, but be aware that the formats of the financial statements in CFW and Flash Memory are (a little) different.

In the world of practice, you will find that financial statements are formatted differently.

No matter what format you find, a close look reveals these differences do not limit your ability to understand the financial statements,

because of differences are in the labels for each line item and the ordering of the line items.

The Questions for this assignment start at Row 76. Remember directions on the READ THIS FIRST page on spreadsheet conventions.

Refer to the previous tab to see the row-by-row assumptions.

$000s Actual Forecast

2007 2008 2009 2010 2011 2012

Sales $77,131 $80,953 $89,250 $120,000 $144,000 $144,000

Cost of goods sold $62,519 $68,382 $72,424 $97,320 $116,784 $116,784

Gross margin $14,612 $12,571 $16,826 $22,680 $27,216 $27,216

Research and development $3,726 $4,133 $4,416 $6,000 $7,200 $7,200

Selling, general and administrative $6,594 $7,536 $7,458 $10,032 $12,038 $12,038

Operating income $4,292 $902 $4,952 $6,648 $7,978 $7,978

Interest expense $480 $652 $735 $0 $0 $0

Other income (expenses) -$39 -$27 -$35 -$50 -$50 -$50

Income before income taxes $3,773 $223 $4,182 $6,598 $7,928 $7,928

Income taxes $1,509 $89 $1,673 $2,639 $3,171 $3,171

Net income $2,264 $134 $2,509 $3,959 $4,757 $4,757

Earnings per share $1.52 $0.09 $1.68 $2.65 $3.19 $3.19

3.3% 3.3% 3.3%

$000s (except shares outstanding and book value per share)

Actual Forecast

2007 2008 2009 2010 2011 2012

Cash $2,536 $2,218 $2,934 $3,960 $4,752 $4,752

Accounts receivable $10,988 $12,864 $14,671 $19,726 $23,671 $23,671

Inventories $9,592 $11,072 $11,509 $13,865 $16,638 $16,638

Prepaid expenses $309 $324 $357 $480 $576 $576

Total current assets $23,425 $26,478 $29,471 $38,031 $45,637 $45,637

Property, plant & equipment at cost $5,306 $6,116 $7,282 $8,182 $9,082 $9,982

Less: Accumulated depreciation $792 $1,174 $1,633 $2,179 $2,793 $3,474

Net property, plant & equipment $4,514 $4,942 $5,649 $6,003 $6,290 $6,508

Total assets $27,939 $31,420 $35,120 $44,034 $51,926 $52,145

Accounts payable $3,084 $4,268 $3,929 $4,799 $5,759 $5,759

Notes payable $6,620 $8,873 $10,132 $0 $0 $0

Accrued expenses $563 $591 $652 $876 $1,051 $1,051

Income taxes payable $151 $9 $167 $264 $317 $317

Other current liabilities $478 $502 $554 $744 $893 $893

Total current liabilities $10,896 $14,243 $15,434 $6,683 $8,020 $8,020

Common stock at $0.01 per share par value $15 $15 $15 $15 $15 $15

Paid in capital in excess of par value $7,980 $7,980 $7,980 $7,980 $7,980 $7,980

Retained earnings $9,048 $9,182 $11,691 $15,650 $20,406 $25,163

Total shareholders' equity $17,043 $17,177 $19,686 $23,645 $28,401 $33,158

Total liabilities & shareholders' equity $27,939 $31,420 $35,120 $30,328 $36,422 $41,178

EFN $13,706 $15,505 $10,967

Number of shares outstanding 1,491,662 1,491,662 1,491,662 1,491,662 1,491,662 1,491,662

Book value per share $11.43 $11.52 $13.20 $15.85 $19.04 $22.23

Return on equity 13.3% 0.8% 12.7% 16.7% 16.7% 14.3%

Interest coverage ratio (times) 8.9 1.4 6.7 #DIV/0! #DIV/0! #DIV/0!

Notes payable / accounts receivable 60.2% 69.0% 69.1% 0.0% 0.0% 0.0%

Notes payable / shareholders' equity 38.8% 51.7% 51.5% 0.0% 0.0% 0.0%

Total liabilities / shareholders' equity 63.9% 82.9% 78.4% 28.3% 28.2% 24.2%

Q1

Explain how the working capital accounts (receivables, inventory, payables) are forecasted.

Q2

Expain how EBIT is forecasted. Yellow highlighting connects balance sheet debt to income statement interest.

Q3

Explain how interest expense is forecasted.

Q4

Explain how PPE is forecasted.

Q5

Explain how long-term debt is forecasted.

Q6

Explain how stockholder's equity is foecasted.

Q7

Explain where EFN comes from and explain what it means, i.e., what does the forecast

tell you? Yellow highlighting starting at row 68 warns you that forecasted ratios are incomplete

beause the financing for EFN has not been determined.