Balance Sheet and Net Sales

3 shows the December 31, 2009 pro- forma balance sheet and income statements for R& E Supplies, Inc. The pro- forma balance sheet shows that R& E Supplies will need external funding from the bank of $ 1. 4 million. However, they show $ 1. 27 million in cash and short- term securities. Why are they going to the bank when they have most of the required amount in their cash account? 2. Pro forma financial statements, by definition, are predictions of a company’s financial statements at a future point in time.So why is it important to analyze the historical performance of the company before constructing pro forma financial statements? 3. Suppose you constructed a pro forma balance sheet for a company and the estimate for external financing required was negative. How would you interpret this result? 4. Harlin Fencing Company’s sales, half of which are for cash, over the past three months were: August September October $70,000 $120,000 $80,000 a. Estimate Harlin’s cash receipts in October if the company’s collection period is 30 days. b.Estimate Harlin’s cash receipts in October if the company’s collection period is 45 days. c. What would be the October balance of Accounts Receivable for Harlin Fencing if the company’s collection period is 30 days? 45 days? 5. Suppose you constructed a pro forma balance sheet and a cash budget for a company for the same time period and the external financing required from the pro forma forecast exceeded the cash deficit estimated on the cash budget. How would you interpret this result? 6. Table 3. 5 presents a computer spreadsheet for estimating R& E Sup-plies’ external financing required for 2009.The text mentions that with modifications to the equations for equity and net sales, the fore-cast can easily be extended through 2010. Write the modified equations for equity and net sales. 7. Using a computer spreadsheet, the information presented below, and the modified equations de termined in question 6 above, extend the fore-cast for R& E Supplies contained in Table 3. 5 through 2010. Is R& E’s external financing required in 2009 higher or lower than in 2010? R& E Supplies Assumptions for 2009 ($ thousands) Growth rate in net sales 30. 0% Tax rate 45. 0% Cost of goods sold/ net sales 86. % Dividend/ earnings after tax 50. 0% Gen. , sell. & admin. Current assets/ net sales 29. 0% expenses/net sales 11. 0% Net fixed assets $270 Long- term debt $560 Current liabilities/ net sales 14. 4% Current portion long- term debt $100 Interest rate 10. 0% 8.This and the following two problems demonstrate that pro forma forecasts, cash budgets and cash flow forecasts all yield the same estimated need for external financing— provided you don’t make any mistakes. For problems 8, 9, and 10, you may ignore the effect of added borrowing on interest expense. The treasurer of Pepperton, Inc. a wholesale distributor of household appliances, wants to estimate hi s company’s cash balances for the first three months of 2009. Using the information below, construct a monthly cash budget for Pepperton for January through March 2009. Does it appear from your results that the treasurer should be concerned about investing excess cash or looking for a bank loan? Pepperton Selected Information Sales (20 percent for cash, the rest on 30- day credit terms): 2008 Actual October $360,000 November 420,000 December 1,200,000 2009 Projected January $600,000 February 240,000 March 240,000Purchases (all on 60- day terms): 2008 Actual October $510,000 November 540,000 December 1,200,000 2009 Projected January $300,000 February 120,000 March 120,000 Wages payable monthly $180,000 Principal payment on debt due in March 210,000 Interest due in March 90,000 Dividend payable in March 300,000 Taxes payable in February 180,000 Addition to accumulated depreciation in March 30,000 Cash balance on January 1, 2009 $300,000 Minimum desired cash balance 150,000 9. C ontinuing problem 8, Pepperton’s annual income statement and balance sheet for December 31, 2008 appear below.Additional in-formation about the company’s accounting methods and the treasurer’s expectations for the first quarter of 2009 appear in the footnotes. Pepperton Annual Income Statement December 31, 2008 ($ thousands) Net sales $6,000 Cost of goods sold1 3,900 Gross profits 2,100 Selling and administrative expenses2 1,620 Interest expense 90 Depreciation3 90 Net profit before tax 300 Tax (33%) 99 Net profit after tax $ 201Balance Sheet December 31, 2008 ($ thousands) Assets Cash $300 Accounts receivable 960 Inventory 1,800 Total current assets $3,060 Gross fixed assets 900 Accumulated depreciation 150 Net fixed assets 750 Total assets $3,810 Liabilities Bank loan $0 Accounts payable 1,740 Miscellaneous accruals4 60 Current portion long- term debt5 210 Taxes payable 300 Total current liabilities $2,310 Long- term debt 990 Shareholders’ equity 510 To tal liabilities and equity $3,810 1.Cost of goods sold consists entirely of items purchased in first quarter. 2 Selling and administrative expenses consist entirely of wages. 3 Depreciation is at the rate of $30,000 per quarter. 4 Miscellaneous accruals are not expected to change in the first quarter. 5 $210 due March 2009. No payments for remainder of year. a. Use this information and the information in problem 8 to construct a pro forma income statement for the first quarter of 2009 and a pro forma balance sheet for March 31, 2009. What is your estimated external financing need for March 31? b.Does the March 31, 2009, estimated external financing equal your cash surplus (deficit) for this date from your cash budget in problem 8? Should it? c. Do your pro forma forecasts tell you more than your cash budget does about Pepperton’s financial prospects? d. What do your pro forma income statement and balance sheet tell you about Pepperton’s need for external financing on F ebruary 28, 2009? 10. Based on your answer to question 9, construct a first- quarter 2009 cash flow forecast for Pepperton. 11. Toys- 4- Kids manufactures plastic toys. Sales and production are highly seasonal.Below is a quarterly pro forma forecast indicating external financing needs for 2009. Assumptions are in parentheses. Toys- 4- Kids 2009 Quarterly Pro Forma Forecast ($ thousands) Qtr 1 Qtr 2 Qtr 3 Qtr 4 Net sales $300 $375 $3,200 $5,000 Cost of sales (70% of sales) 210 263 2,240 3,500 Gross profit 90 113 960 1,500 Operating expenses 560 560560 560 Profit before tax (470) (448) 400 940Income taxes (188) (179) 160 376 Profit after tax ($282) ($269) $240 $564 Cash (minimum balance $200,000) $1,235 $927 $200 $200 Accounts receivable (75% of quarterly sales) 225 281 2,400 3,750 Inventory (12/ 31/ 08 balance $ 500,000) 500 500 500 500 Current assets 1,960 1,990 3,120 4,450 Net plant & equipment 1,000 1,000 1,000 1,000 Total assets $2,960 $2,708 $4,100$5,450 Accounts payable ( 10% o f quarterly sales) 30 38 320 00 Accrued taxes ( payments quarterly in arrears) (188) (179) 160 376 Current liabilities ( 158) ( 142) 480 876 Long- term debt 400 400 400 400 Equity (12/ 31/ 08 balance $3,000,000) 2,718 2,450 2,690 3,254 Total liabilities and equity $2,960 $2,708 $3,570 $4,530 External financing required $ 0 $ 0 $ 530 $ 920 a. How do you interpret the negative numbers for income taxes in the first two quarters? b. Why are cash balances in the first two quarters greater than the minimum required $ 200,000?How were these numbers deter-mined? c. How was â€Å" external financing required† appearing at the bottom of the forecast determined? d. Do you think Toys- 4- Kids will be able to borrow the external financing required as indicated by the forecast? 12. Continuing with Toys- 4- Kids introduced in the preceding problem, the company’s production manager has argued for years that it is inefficient to produce on a seasonal basis. She believes the company sho uld switch to level production throughout the year, building up finished goods inventory in the first two quarters to meet the peak selling needs in the last two.She believes the company can reduce its cost of goods sold from 70 to 65 percent with level production. a. Prepare a revised pro forma forecast assuming level production. In your forecast assume that quarterly accounts payable under level production equal 10 percent of average quarterly sales for the year. To estimate quarterly inventory, use the following two formulas: Inventoryeoq = Inventoryboq + Quarterly production – Quarterly cost of sales Quarterly production Annual cost of sales/ 4 where eoq and boq refer to end of quarter and beginning of quarter, respectively.Please ignore the effect of increased external financing required on interest expense. b. What is the effect of the switch from seasonal to level production on annual profits? c. What effect does the switch have on the company’s quarterly ending inventory? On the company’s quarterly need for external financing? d. Do you think the company will be able to borrow the amount of money required by level production? What obsolescence risks does the company incur by building up inventory in anticipation of future sales? Might this be a concern to lenders? 13. You will need to use the Standard & Poor’s Market Insight Web site ( www. hhe. com/ edumarketinsight) for this problem. Market Insight presents a spreadsheet entitled â€Å" Forecasted Values. † ( Excel Analytics, Valuation Data, Forecasted Values. )a. How are these forecasts generated? Are they more than simple extrapolation of past trends? b. How useful might these forecasts be for projecting a company’s future financing needs? 14. This problem asks you to construct a simple simulation model. If you do not own simulation software, you can download to your computer a free, full- strength version of Crystal Ball for a one- week trial. Point your b rowser to www. crystalball. om and select download. a. Problem 7 above asked you to extend the forecast for R& E Sup-plies contained in Table 3. 5 through 2010. Using the same spread-sheet, simulate R& E Supplies’ external funding requirements in 2009 under the following assumptions. i. Represent the growth rate in net sales as a triangular distribution with a mean of 30 percent and a range 25 percent to 35 percent. ii. Represent the interest rate as a uniform distribution varying from 9 percent to 11 percent.iii. Represent the tax rate as a lognormal distribution with a mean of 45 percent and a standard deviation of 2 percent. . If the treasurer wants to be 95 percent certain of raising enough money in 2009, how much should he raise? ( Grab the triangle below the frequency chart on the right and move it to the left until 95. 00 appears in the â€Å" Certainty† window. ) 15. This problem asks you to prepare one- and five- year financial fore-casts for Aquatic Supplies Company. An Excel spreadsheet containing the company’s 2008 financial statements and management’s projections is available for download at www. mhhe. com/ higgins9e. (Select Student Edition Choose> a Chapter >Excel Spreadsheets. Use this information to answer the questions posed in the spreadsheet. 16. The financial statements and additional information for Noble Equipment Corp. appear at www. mhhe. com/ higgins9e. (Select Student Edition Choose >a Chapter> Excel Spreadsheets. ) The company’s fiscal year end is September 30. Noble’s management wants to estimate the company’s cash balances for the last three months of calendar year 2008, which are the first three months of fiscal year 2009. The questions accompanying the spreadsheet ask you to prepare a monthly cash budget, pro forma financial statements, and a cash flow forecast for the period.