Please see the attachment. Only Question 20 (the last question).

Required:

1. Work in Excel

2. need explanations of answer

3. show calculations process.

Indirect Planning Assumptions - Example 4.7 (page 150) 13. The Winthrop Company is constructing a five-year plan. The firm's ACP is currently 90 days, while its inventory turnover ratio is 3 based on COGS. The company has forecast aggressive revenue growth along with efficiency improvements in manufacturing and credit and collections as follows: (Year 0 is the current year.) Year 0 1 2 3 4 5 Revenue ($000) $50.0 $57.5 $66.0 $76.0 $87.5 $100.0 Cost Ratio 60% 59% 58% 57% 56% 55% ACP (days) 90 70 60 50 45 40 Inventory Turn 3 4 5 6 6.5 7 For each planned year: a. Calculate the COGS. b. Calculate the A/R balance at year-end. c. Calculate the inventory balance at year-end. SOLUTION: a. Year: Revenue ($000) Cost Ratio COGS b. ACP 0 $50.0 60% $30,000 A/R 360 Sales 1 2 3 4 5 $57.5 $66.0 $76.0 $87.5 $100.0 59% 58% 57% 56% 55% $33,925 $38,280 $43,320 $49,000 $55,000 Hence, A/R 1. ACP Sales 360 2. A/R 70 $57,500 $11,181 360 3. A/R 60 $66,000 $11,000 360 4. A/R 50 $76,000 10,556 360 A/R 45 $87,500 $10,938 360 5. A/R 40 $100,000 $11,111 360 c. Inv. Turnover COGS Inv. 1. Hence, Inventory COGS Inv.Turnover 2. Inventory $33,925 $8,481 4 Inventory $38,280 $7,656 5 3. 4. Inventory $43,320 $7,720 6 Inventory $49,000 $7,538 6.5 5. Inventory $55,000 $7,857 7 19. Blue & Noble is a small law firm that does all of its business through billings (no cash sales). Historically the firm has collected 40% of its revenue in the month of billing, 50% during the first month after billing and 8% during the second month after billing. Two percent typically remains uncollectable. Revenue projections for the coming year are $47,500 for January and $50,000 for February. Cash receipts of $50,600 are expected in March. What revenues are the projected for March? SOLUTION: $50,600 = .4(March Rev.) + .5($50,000) + .08($47,500) $50,600 = .4 (March Rev) + $25,000 + $3,800 $21,800 = .4(March Rev.) March Rev = $54,500 20. The Winthrop Company expects to finish the current year with the financial results indicated on the worksheet on the next page. Develop next year's income statement and ending balance sheet using that information and the following planning assumptions and facts. Work to the nearest thousand dollars. PLANNING ASSUMPTIONS and FACTS Income Statement Items 1. Revenue will grow by 20%. 2. The cost ratio will improve by 2%. 3. Spending in the Marketing Department will be held to 20% of revenue. 4. Finance and Engineering expenses will each increase by 10%. 5. The combined income tax rate will be 40%. 6. Interest on all long term borrowing will be 10%. Cash balances will remain constant. Balance Sheet Items 7. 8. 9. 10. 11. 12. 13. The ACP will be 35 days. (Use ending balances.) The Inventory Turnover Ratio based on COGS will be 4.5X. (Use ending balances.) Capital spending is expected to be $5M. The average amortization life of the assets to be acquired is 5 years and straight-line amortization is used. Amortization expense for old assets will be $1.5M. Accounts payable is expected to be 30% of inventory. Accruals will not change. No dividends will be paid and no new shares will be sold