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.