How do you calculate total cash receipts per month for a cash receipts schedule statement? (Refer to 4-19).

Chapter 04: Financial Forecasting Chapter 4 Financial Forecasting Discussion Questions 4-1. What are the basic benefits and purposes of developing pro forma statements and a cash budget? The pro-forma financial statements and cash budget enable the firm to determine its future level of asset needs and the associated financing that will be required. Furthermore, one can track actual events against the projections. Bankers and other lenders also use these financial statements as a guide in credit decisions. 4-2. Explain how the collections and purchases schedules are related to the borrowing needs of the corporation. The collections and purchase schedules measure the speed at which receivables are collected and purchases are paid. To the extent collections do not cover purchasing costs and other financial requirements, the firm must look to borrowing to cover the deficit. 4-3. With inflation, what are the implications of using LIFO and FIFO inventory methods? How do they affect the cost of goods sold? LIFO inventory valuation assumes the latest purchased inventory becomes part of the cost of goods sold, while the FIFO method assigns inventory items that were purchased first to the cost of goods sold. In an inflationary environment, the LIFO method will result in a higher cost of goods sold figure and one that more accurately matches the sales dollars recorded at current dollars. 4-4. Explain the relationship between inventory turnover and purchasing needs. The more rapid the turnover of inventory, the greater the need for purchase and replacement. Rapidly turning inventory makes for somewhat greater ease in foreseeing future requirements and reduces the cost of carrying inventory. 4-5. Rapid corporate growth in sales and profits can cause financing problems. Elaborate on this statement. Rapid growth in sales and profits is often associated with rapid growth in asset commitment. A $100,000 increase in sales may cause a $50,000 increase in assets, with perhaps only $10,000 of the new financing coming from profits. It is very seldom that incremental profits from sales expansion can meet new financing needs. 4-1 Chapter 04: Financial Forecasting 4-6. Discuss the advantage and disadvantage of level production schedules in firms with cyclical sales. Level production in a cyclical industry has the advantage of allowing for the maintenance of a stable work force and reducing inefficiencies caused by shutting down production during slow periods and accelerating work during crash production periods. A major drawback is that a large stock of inventory may be accumulated during the slow sales period. This inventory may be expensive to finance, with an associated danger of obsolescence. 4-7. What conditions would help make a percent-of-sales forecast almost as accurate as pro forma financial statements and cash budgets? The percent-of-sales forecast is only as good as the functional relationship of assets and liabilities to sales. To the extent that past relationships accurately depict the future, the percent-of-sales method will give values that reasonably represent the values derived through the pro-forma statements and the cash budget. Chapter 4 Problems 1. Growth and financing (LO4) Philip Morris is excited because sales for his clothing company are expected to double from $500,000 to $1,000,000 next year. Philip notes that net assets (Assets Liabilities) will remain at 50 percent of Sales. His clothing firm will enjoy a 9 percent return on total sales. He will start the year with $100,000 in the bank and is already bragging about the two Mercedes he will buy and the European vacation he will take. Does his optimistic outlook for his cash position appear to be correct? Compute his likely cash balance or deficit for the end of the year. Start with beginning cash and subtract the asset buildup (equal to 50 percent of the sales increase) and add in profit. 4-2 Chapter 04: Financial Forecasting 4-1. Solution: Philip Morris Beginning cash - Asset buildup Profit Ending cash 2. 4-2. $100,000 (250,000) 90,000 ($60,000) (1/2 $500,000) (9% $1,000,000) Deficit Growth and financing (LO4) In Problem 1 if there had been no increase in sales and all other facts were the same, what would Philip's ending cash balance be? What lesson do the examples in Problems 1 and 2 illustrate? Solution: Philip Morris (continued) Beginning cash No asset buildup Profit Ending cash $100,000 ----45,000 (9% $500,000) $145,000 The lesson to be learned is that increased sales can increase the financing requirements and reduce cash even for a profitable firm. 4-3 Chapter 04: Financial Forecasting 3. 4-3. Growth and financing (LO4) Galehouse Gas Stations Inc., expects sales to increase from $1,500,000 to $1,700,000 next year. Mr. Galehouse believes that net assets (Assets Liabilities) will represent 70% of sales. His firm has a 10 percent return on sales and pays 40% of profits out as dividends. a. What effect will this growth have on funds? b. If the dividend payout is only 15%, what effect will this growth have on funds? Solution: Galehouse Gas Stations, Inc. a. Asset buildup Profit Dividends Change in cash ($140,000) (70% $200,000) 170,000 (10% $1,700,000) (68,000) (40% $170,000) (38,000) The cash balance will be reduced by $38,000. b. Dividends would only be $25,500 (15% $170,000). The change in cash would be a positive $4,500. Asset buildup Profit Dividends Change in cash ($140,000) 170,000 (25,500) $4,500 The cash balance will increase by $4,500. 4. Sales projections (LO2) The Alliance Corp. expects to sell the following number of units of copper cables at the prices indicated, under three different scenarios in the economy. The probability of each outcome is indicated. What is the expected value of the total sales projection? Outcome A B C Probability 0.30 0.50 0.20 Units 200 320 410 4-4 Price $15 30 40 Chapter 04: Financial Forecasting 4-4. Solution: Alliance Corporation (1) Outcome A B C 5. 4-5. (2) (3) (4) (5) Total Probability Units Price Value .30 200 $15 $3,000 .50 320 $30 9,600 .20 410 $40 16,400 Total expected value (6) Expected Value (2 5) $ 900 4,800 3,280 $8,980 Sales projections (LO2) Bronco Truck Parts expects to sell the following number of units at the prices indicated under three different scenarios in the economy. The probability of each outcome is indicated. What is the expected value of the total sales projection? Outcome Probability Units Price A 0.20 300 $16 B 0.50 500 $25 C 0.30 1,000 $30 Solution: Bronco Truck Parts (1) Outcome A B C (2) (3) (6) Expected Value Total Probability Units Price Value (2 5) .20 300 $16 $4,80 $ 960 0 .50 500 25 12,50 6,250 0 .30 1,000 30 30,00 9,000 0 Total expected value $16,210 4-5 (4) (5) Chapter 04: Financial Forecasting 6. 4-6. Sales projections (LO2) Cyber Security Systems had sales of 3,000 units at $50 per unit last year. The marketing manager projects a 20 percent increase in unit volume sales this year with a 10 percent price increase. Returned merchandise will represent 6 percent of total sales. What is your net dollar sales projection for this year? Solution: Cyber Security Systems Unit volume 3,000 1.20......................... 3,600 Price $50 1.10..................... $55 Total Sales......................................... $198,000 Returns (6%)...................................... 11,880 Net Sales.......................................... $186,120 7. 4-7. Sales projections (LO2) Dodge Ball Bearings had sales of 10,000 units at $20 per unit last year. The marketing manager projects a 30 percent increase in unit volume sales this year with a 5 percent price decrease (due to a price reduction by a competitor). Returned merchandise will represent 3 percent of total sales. What is your net dollar sales projection for this year? Solution: Dodge Ball Bearings Unit volume 10,000 1.30....................... 13,000 Price $20 .95.............................. $19 Total Sales.................................................. $247,000 Returns (3%).............................................. 7,410 Net Sales.................................................... $239,590 4-6 Chapter 04: Financial Forecasting 8. 4-8. Production requirements (LO2) Sales for Western Boot Stores are expected to be 40,000 units for October. The company likes to maintain 15 percent of unit sales for each month in ending inventory (i.e., the end of October). Beginning inventory for October is 8,500 units. How many units should Western Boot produce for the coming month? Solution: Western Boot Stores + Projected sales................................... + Desired ending inventory.................. - Beginning inventory.......................... Units to be produced............................. 9. 4-9. 40,000 units 6,000 (15% 40,000) 8,500 37,500 Production requirements (LO2) Vitale Hair Spray had sales of 8,000 units in March. A 50 percent increase is expected in April. The company will maintain 5 percent of expected unit sales for April in ending inventory. Beginning inventory for April was 400 units. How many units should the company produce in April? Solution: Vitale Hair Spray + Projected sales............................. + Desired ending inventory............ - Beginning inventory.................... Units to be produced....................... 10. 12,000 units (8,000 1.50) 600 (5% 12,000) 400 12,200 Production requirements (LO2) Delsing Plumbing Company has beginning inventory of 14,000 units, will sell 50,000 units for the month, and desires to reduce ending inventory to 40 percent of beginning inventory. How many units should Delsing produce? 4-10. Solution: 4-7 Chapter 04: Financial Forecasting Delsing Plumbing Company + Projected sales............................. + Desired ending inventory............ - Beginning inventory.................... Units to be produced....................... 11. 50,000 units 5,600 (40% 14,000) 14,000 41,600 Cost of goods soldFIFO (LO2) On December 31 of last year, Wolfson Corporation had in inventory 400 units of its product, which cost $21 per unit to produce. During January, the company produced 800 units at a cost of $24 per unit. Assuming that Wolfson Corporation sold 700 units in January, what was the cost of goods sold (assume FIFO inventory accounting)? 4-11. Solution: Wolfson Corporation Cost of goods sold on 700 units Old inventory: Quantity (Units).................. Cost per unit....................... Total.................................... 400 $ 21 $ 8,400 New inventory: Quantity (Units).................. Cost per unit....................... Total Total Cost of Goods Sold...... 300 $ 24 $ 7,200 $15,600 4-8 Chapter 04: Financial Forecasting 12. Cost of goods soldFIFO (LO2) At the end of January, Higgins Data Systems had an inventory of 600 units, which cost $16 per unit to produce. During February the company produced 850 units at a cost of $19 per unit. If the firm sold 1,100 units in February, what was its cost of goods sold (assume LIFO inventory accounting)? 4-12. Solution: Higgins Data System Cost of goods sold on 1,100 units New inventory: Quantity (Units)............... Cost per unit.................... Total................................. Old inventory: Quantity (Units)............... Cost per unit.................... Total................................. Total Cost of Goods Sold... 13. 850 $ 19 $16,150 250 $ 16 $ 4,000 $20,150 Cost of goods soldLIFO and FIFO (LO2) At the end of January, Mineral Labs had an inventory of 725 units, which cost $10 per unit to produce. During February the company produced 650 units at a cost of $14 per unit. If the firm sold 1,000 units in February, what was the cost of goods sold? a. Assume LIFO inventory accounting. b. Assume FIFO inventory accounting. 4-13. Solution: 4-9 Chapter 04: Financial Forecasting Mineral Labs a. LIFO Accounting Cost of goods sold on 1,000 units New inventory: Quantity (Units).................................... Cost per unit.......................................... Total...................................................... Old inventory: Quantity (Units).................................... Cost per unit.......................................... Total...................................................... Total Cost of Goods Sold........................... 650 $ 14 $ 9,100 350 $ 10 $ 3,500 $12,600 b. FIFO Accounting Cost of goods sold on 1,000 units Old inventory: Quantity (Units).................................... Cost per unit.......................................... Total...................................................... New inventory: Quantity (Units).................................... Cost per unit.......................................... Total...................................................... Total Cost of Goods Sold........................... 14. 725 $ 10 $ 7,250 275 $ 14 $ 3,850 $11,100 Gross profit and ending inventory (LO2) The Bradley Corporation produces a product with the following costs as of July 1, 2011: Material................. Labor..................... Overhead............... 4-10 $ 2 per unit 4 per unit 2 per unit Chapter 04: Financial Forecasting Beginning inventory at these costs on July 1 was 3,000 units. From July 1 to December 1, 2011, Bradley produced 12,000 units. These units had a material cost of $3, labor of $5, and overhead of $3 per unit. Bradley uses FIFO inventory accounting. Assuming that Bradley sold 13,000 units during the last six months of the year at $16 each, what is its gross profit? What is the value of ending inventory? 4-14. Solution: Bradley Corporation Sales (13,000 @ $16) Cost of goods sold: Old inventory: Quantity (units)............ Cost per unit................. Total............................... New inventory: Quantity (units)............ Cost per unit................. Total............................... Total cost of goods sold............................. Gross profit.................... Value of ending inventory: Beginning inventory (3,000 $8)................. + Total production (12,000 $11).............. Total inventory available for sale.......... - Cost of good sold..... Ending inventory............ Or 2,000 units $11 = $22,000 $208,000 $ 3,000 8 $ 24,000 10,000 $ 11 $ 110,000 $134,000 $ 74,000 $ 24,000 $132,000 $156,000 $134,000 $ 22,000 4-11 Chapter 04: Financial Forecasting 15. Gross profit and ending inventory (LO2) Assume in Problem 14 that the Bradley Corporation used LIFO accounting instead of FIFO; what would its gross profit be? What would be the value of ending inventory? 4.15. 4.16. Solution: Bradley Corporation(Continued) Sales (13,000 @ $16) Cost of goods sold: New inventory: Quantity (units)............ Cost per unit................. Total............................... Old inventory: Quantity (units)............ Cost per unit................. Total............................... Total cost of goods sold............................. Gross profit.................... Value of ending inventory: Beginning inventory (3,000 $8)................. + Total production (12,000 $11).............. Total inventory available for sale.......... - Cost of good sold..... Ending inventory............ Or 2,000 units $8 = $16,000 $208,000 12,000 $ 11 $132,000 $ 1,000 8 $ 8,000 $140,000 $ 68,000 $ 24,000 $132,000 $156,000 $140,000 $ 16,000 4-12 Chapter 04: Financial Forecasting 16. Gross profit and ending inventory (LO2) Sprint Shoes, Inc., had a beginning inventory of 9,000 units on January 1, 2010. The costs associated with the inventory were: Material................. Labor..................... Overhead............... $13.00 per unit 8.00 per unit 6.10 per unit During 2010, the firm produced 42,500 units with the following costs: Material................. Labor..................... Overhead............... $15.50 per unit 7.80 per unit 8.30 per unit Sales for the year were 47,250 units at $39.60 each. Sprint Shoes uses LIFO accounting. What was the gross profit? What was the value of ending inventory? 4-16. Solution: Sprint Shoes, Inc. Sales (47,250 @ $39.60) $1,871,100 Cost of goods sold: New inventory: Quantity (units)............ 42,500 Cost per unit................. $ 4-13 31.60 Chapter 04: Financial Forecasting Total............................... $1,343,000 Old inventory: Quantity (units)............ 4,750 Cost per unit................. $ Total............................... $ 128,725 Total cost of goods sold............................. $1,471,725 Gross profit.................... $ 399,375 4-14 27.10 Chapter 04: Financial Forecasting Value of ending inventory: Beginning inventory (9,000 $27.10)........... $ 243,900 4-15 Chapter 04: Financial Forecasting + Total production (42,500 $31.60)......... $1,343,000 Total inventory available for sale.......... $1,586,900 - Cost of good sold..... $1,471,725 Ending inventory............ $ 115,175 Or 42,500 units $27.10 = $115,175 17. Schedule of cash receipts (LO2) Victoria's Apparel has forecast credit sales for the fourth quarter of the year as: September (actual).......... Fourth Quarter October............................ November........................ 4-16 $50,000 $40,000 35,000 Chapter 04: Financial Forecasting December........................ 60,000 Experience has shown that 20 percent of sales receipts are collected in the month of sale, 70 percent in the following month, and 10 percent are never collected. Prepare a schedule of cash receipts for Victoria's Apparel covering the fourth quarter (October through December). 4-17. Solution: Victoria's Apparel Credit sales 20% Collected in month of sales 70% Collected in month after sales Total cash receipts September October November December $50,000 $40,000 $35,000 $60,000 8,000 7,000 12,000 35,000 24,500 $43,000 4-17 28,000 $35,000 $36,500 Chapter 04: Financial Forecasting 18. Schedule of cash receipts (LO2) Simpson Glove Company has made the following sales projections for the next six months. All sales are credit sales. March.............................. April................................ May................................. June................................. July.................................. August............................. $36,000 45,000 27,000 42,000 53,000 57,000 Sales in January and February were $41,000 and $39,000 respectively. Experience has shown that of total sales receipts 10 percent are uncollectible, 40 percent are collected in the month of sale, 30 percent are collected in the following month, and 20 percent are collected two months after sale. Prepare a monthly cash receipts schedule for the firm for March through August. 4-18 Chapter 04: Financial Forecasting 4-18. Solution: Sales Collections (40% of current sales) Collections (30% of prior month's sales) Collections (20% of sales 2 months earlier) Total cash receipts Simpson Glove Company Cash Receipts Schedule January February March April May June July August $41,000 $39,000 $36,000 $45,000 $27,000 $42,000 $53,000 $57,000 14,400 18,000 10,800 16,800 21,200 22,800 11,700 10,800 13,500 8,100 12,600 15,900 8,200 7,800 7,200 9,000 5,400 8,400 $34,300 4-19 $36,600 $31,500 $33,900 $39,200 $47,100 Chapter 04: Financial Forecasting 19. Schedule of cash receipts (LO2) Watt's Lighting Stores made the following sales projection for the next six months. All sales are credit sales. March.............................. April................................ May................................. June................................. July.................................. August............................. $30,000 36,000 25,000 34,000 42,000 44,000 Sales in January and February were $33,000 and $32,000, respectively. Experience has shown that of total sales, 10 percent are uncollectible, 30 percent are collected in the month of sale, 40 percent are collected in the following month, and 20 percent are collected two months after sale. Prepare a monthly cash receipts schedule for the firm for March through August. Of the sales expected to be made during the six months from March through August, how much will still be uncollected at the end of August? How much of this is expected to be collected later? 4-20 Chapter 04: Financial Forecasting 4-19. Solution: Sales Watt's Lighting Stores Cash Receipts Schedule January Februar March April May y $33,000 $32,000 $30,00 $36,00 $25,000 0 0 9,000 10,800 7,500 June July $34,00 0 10,200 $42,00 0 12,600 August $44,000 Collections 13,200 (30% of current sales) Collections 12,800 12,000 14,400 10,000 13,600 16,800 (40% of prior month's sales) Collections 6,600 6,400 6,000 7,200 5,000 6,800 (20% of sales 2 months earlier) Total cash $28,40 $29,20 $27,900 $27,40 $31,20 $36,800 receipts 0 0 0 0 Still due (uncollected) in August: Bad debts: ($30,000 + 36,000 + 25,000 + 34,000 + 42,000 + 44,000) .1 = (211,000) .1 = $21,100 To be collected from July sales: ($42,000 .20) = $8,400 To be collected from August sales: ($44,000 .60) = $26,400 4-21 Chapter 04: Financial Forecasting $21,100 + $8,400 + $26,400 = $55,900 due Expected to be collected: $55,900 due - $21,100 bad debts = $34,800 4-22 Chapter 04: Financial Forecasting 20. Schedule of cash payments (LO2) Ultravision, Inc., anticipates sales of $240,000 from January through April. Materials will represent 50 percent of sales and because of level production, material purchases will be equal for each month during the four months of January, February, March, and April. Materials are paid for one month after the month purchased. Materials purchased in December of last year were $20,000 (half of $40,000 in sales). Labor costs for each of the four months are slightly different due to a provision in the labor contract in which bonuses are paid in February and April. The labor figures are: January............................ February.......................... March.............................. April................................ $10,000 13,000 10,000 15,000 Fixed overhead is $6,000 per month. Prepare a schedule of cash payments for January through April. 4-23 Chapter 04: Financial Forecasting 4-20. Solution: Ultravision, Inc. Cash Payment Schedule * Purchases ** Payment to material purchases Labor Fixed overhead Total Cash Payments Dec. $20,000 Jan. $30,000 20,000 10,000 6,000 $36,000 Feb. $30,000 30,000 13,000 6,000 $49,000 For January through April * Monthly purchases equal ($240,000 50%)/4 or $120,000/4 $30,000 ** Payment is equal to prior month's purchases. 4-24 March $30,000 30,000 10,000 6,000 $46,000 April $30,000 30,000 15,000 6,000 $51,000 Chapter 04: Financial Forecasting 21. Schedule of cash payments (LO2) The Denver Corporation has forecast the following sales for the first seven months of the year: January................. February............... March................... April..................... $10,000 12,000 14,000 20,000 May......... $10,000 June......... 16,000 July........ 18,000 Monthly material purchases are set equal to 30 percent of forecasted sales for the next month. Of the total material costs, 40 percent are paid in the month of purchase and 60 percent in the following month. Labor costs will run $4,000 per month, and fixed overhead is $2,000 per month. Interest payments on the debt will be $3,000 for both March and June. Finally, the Denever salesforce will receive a 1.5 percent commission on total sales for the first six months of the year, to be paid on June 30. Prepare a monthly summary of cash payments for the six-month period from January through June. (Note: Compute prior December purchases to help get total material payments for January.) 4-25 Chapter 04: Financial Forecasting 4-21. Solution: Sales Purchases (30% of next month's sales) Payment (40% of current purchases) Material payment (60% of previous month's purchases) Total payment for materials Labor costs Fixed overhead Interest payments Sales commission (1.5% of $82,000) Total payments Denver Corporation Cash Payments Schedule Dec. Jan. Feb. March April $10,000 $12,00 $14,000 $20,000 0 3,000 3,600 4,200 6,000 3,000 May June July $10,00 $16,000 $18,000 0 4,800 5,400 1,440 1,680 2,400 1,200 1,920 2,160 1,800 2,160 2,520 3,600 1,800 2,880 3,240 3,840 4,920 4,800 3,720 5,040 4,000 2,000 4,000 2,000 4000 2,000 3,000 4,000 2,000 4,000 2,000 4,000 2,000 3,000 1,230 $ 9,240 $ 9,840 $13,920 $10,800 4-26 $ $15,270 9,720 Chapter 04: Financial Forecasting 22. Schedule of cash payments (LO2) The Boswell Corporation forecasts its sales in units for the next four months as follows: March................... April.................... May..................... June..................... 6,000 8,000 5,500 4,000 Boswell maintains an ending inventory for each month in the amount of one and one-half times the expected sales in the following month. The ending inventory for February (March's beginning inventory) reflects this policy. Materials cost $5 per unit and are paid for in the month after production. Labor cost is $10 per unit and is paid for in the month incurred. Fixed overhead is $12,000 per month. Dividends of $20,000 are to be paid in May. Five thousand units were produced in February. Complete a production schedule and a summary of cash payments for March, April, and May. Remember that production in any one month is equal to sales plus desired ending inventory minus beginning inventory. 4-22. Solution: Boswell Corporation Production Schedule Forecasted unit sales +Desired ending inventory -Beginning inventory Units to be produced Units produced Materials ($5/unit) month after production Labor ($10/unit) month of production Fixed overhead Dividends March 6,000 12,000 April 8,000 8,250 May 5,500 6,000 9,000 12,000 9,000 4,250 Cash Payments Feb March 5,000 9,000 $25,000 8,250 3,250 April 4,250 $45,000 May 3,250 $21,250 90,000 42,500 32,500 12,000 12,000 12,000 20,000 4-27 June 4,000 Chapter 04: Financial Forecasting Total Cash Payments $127,000 4-28 $99,500 $85,750 Chapter 04: Financial Forecasting 23. Schedule of cash payments (LO2) The Volt Battery Company has forecast its sales in units as follows: January................. February............... March................... April..................... 800 650 600 1,100 May......... June......... July......... 1,350 1,500 1,200 Volt Battery always keeps an ending inventory equal to 120 percent of the next month's expected sales. The ending inventory for December (January's beginning inventory) is 960 units, which is consistent with this policy. Materials cost $12 per unit and are paid for in the month after purchase. Labor cost is $5 per unit and is paid in the month the cost is incurred. Overhead costs are $6,000 per month. Interest of $8,000 is scheduled to be paid in March, and employee bonuses of $13,200 will be paid in June. Prepare a monthly production schedule and a monthly summary of cash payments for January through June. Volt produced 600 units in December. 4-29 Chapter 04: Financial Forecasting 4-23. Solution: Volt Battery Company Production Schedule Jan. Feb. March April May June July Forecasted unit sales 800 650 600 1,100 1,350 1,500 1,200 + Desired ending inventory 780 720 1,320 1,620 1,800 1,440 - Beginning inventory 960 780 720 1,320 1,620 1,800 = Units to be produced 620 590 1,200 1,400 1,530 1,140 Summary of Cash Payments Dec. Jan. Feb. March April May June Units produced 600 620 590 1,200 1,400 1,530 1,140 Material cost ($12/unit) $ $ 7,440 $ 7,080 $14,400 $16,800 $18,360 month after purchase 7,200 Labor cost ($5/unit) month 3,100 2,950 6,000 7,000 7,650 $ 5,700 incurred Overhead cost 6,000 6,000 6,000 6,000 6,000 6,000 Interest 8,000 Employee bonuses 13,200 Total Cash Payments $16,30 $16,390 $27,080 $27,400 $30,450 $43,260 0 4-30 Chapter 04: Financial Forecasting 24. Cash Budget (LO2) Lansing Auto Parts, Inc., has projected sales of $25,000 in October, $35,000 in November, and $30,000 in December. Of the company's sales, 20 percent are paid for by cash and 80 percent are sold on credit. The credit sales are collected one month after sale. Determine collections for November and December. Also assume that the company's cash payments for November and December are $30,400 and $29,800, respectively. The beginning cash balance in November is $6,000, which is the desired minimum balance. Prepare a cash budget with borrowing needed or repayments for November and December. (You will need to prepare a cash receipts schedule first.) 4-24. Solution: Lansing Auto Parts, Inc. Cash Receipts Schedule Sales Cash sales (20%) Collections (80% of previous month's sales) Total cash receipts October $25,000 November $35,000 7,000 December $30,000 6,000 20,000 $27,000 28,000 $34,000 Lansing Auto Parts, Inc. Cash Budget November $27,000 30,400 (3,400) 6,000 2,600 3,400 3,400 $ 6,000 Cash Receipts Cash Payments Net Cash Flow Beginning Cash Balance Cumulative Cash Balance Monthly Loan or (Repayment) Cumulative Loan Balance Ending Cash Balance 4-31 July $34,000 29,800 4,200 6,000 10,200 (3,400) -0$ 6,800 Chapter 04: Financial Forecasting 25. Complete cash budget (LO2) Harry's Carryout Stores has eight locations. The firm wishes to expand by two more stores and needs a bank loan to do this. Mr. Wilson, the banker, will finance construction if the firm can present an acceptable three-month financial plan for January through March. The following are actual and forecasted sales figures: Actual November........... $200,000 December............ 220,000 Forecast Additional Information January............ $280,000 April forecast...... $330,000 February.......... 320,000 March........... 340,000 Of the firm's sales, 40 percent are for cash and the remaining 60 percent are on credit. Of credit sales, 30 percent are paid in the month after sale and 70 percent are paid in the second month after the sale. Materials cost 30 percent of sales and are purchased and received each month in an amount sufficient to cover the following month's expected sales. Materials are paid for in the month after they are received. Labor expense is 40 percent of sales and is paid for in the month of sales. Selling and administrative expense is 5 percent of sales and is also paid in the month of sales. Overhead expense is $28,000 in cash per month. Depreciation expense is $10,000 per month. Taxes of $8,000 will be paid in January, and dividends of $2,000 will be paid in March. Cash at the beginning of January is $80,000, and the minimum desired cash balance is $75,000. For January, February, and March, prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowings and repayments. 4-32 Chapter 04: Financial Forecasting 4-25. Solution: November Sales $200,000 Cash sales (40%) 80,000 Credit sales (60%) 120,000 Collections (month after credit sales) 30% Collections (two months after credit sales) 70% Total Cash Receipts Harry's Carry-Out Stores Cash Receipts Schedule December January February March April $220,000 $ 280,000 $320,000 $340,000 $330,000 88,000 112,000 128,000 136,000 132,000 132,000 168,000 192,000 204,000 198,000 36,000 39,600 50,400 57,600 61,200 84,000 $235,600 4-33 92,400 117,600 $270,800 $311,200 134,400 Chapter 04: Financial Forecasting 4-25. (Continued) Harry's Carry-Out Stores Cash Payments Schedule January Payments for Purchases (30% of next month's $ 84,000 sales paid in month after purchasesequivalent to 30% of current sales)....................................... Labor Expense (40% of sales)............................. 112,000 Selling and Admin. Exp. (5% of sales)................ 14,000 Overhead............................................................. 28,000 Taxes................................................................... 8,000 Dividends............................................................ Total Cash Payments*......................................... $246,600 February $ 96,000 March $102,000 128,000 16,000 28,000 136,000 17,000 28,000 $268,000 2,000 $285,000 *The $10,000 of depreciation is excluded because it is not a cash expense. 4-34 Chapter 04: Financial Forecasting 4-25. (Continued) Harry's Carry-Out Stores Cash Budget January February Total Cash Receipts............................. $235,600 $270,800 Total Cash Payments........................... 246,000 268,000 Net Cash Flow.................................... (10,400) 2,800 Beginning Cash Balance..................... 80,000 75,000 Cumulative Cash Balance................... 69,600 77,800 Monthly Loan or (repayment)............. 5,400 (2,800) Cumulative Loan Balance................... 5,400 2,600 Ending Cash Balance.......................... $ 75,000 $ 75,000 4-35 March $311,200 285,000 26,200 75,000 101,200 (2,600) -0$ 98,600 Chapter 04: Financial Forecasting 26. Complete cash budget (LO2) Archer Electronics Company's actual sales and purchases for April and May are shown here along with forecasted sales and purchases for June through September. Sales April (actual)..................................... May (actual)...................................... June (forecast)................................... July (forecast)................................... August (forecast).............................. September (forecast)......................... $320,000 300,000 275,000 275,000 290,000 330,000 Purchases $130,000 120,000 120,000 180,000 200,000 170,000 The company makes 10 percent of its sales for cash and 90 percent on credit. Of the credit sales, 20 percent are collected in the month after the sale and 80 percent are collected two months later. Archer pays for 40 percent of its purchases in the month after purchase and 60 percent two months after. Labor expense equals 10 percent of the current month's sales. Overhead expense equals $12,000 per month. Interest payments of $30,000 are due in June and September. A cash dividend of $50,000 is scheduled to be paid in June. Tax payments of $25,000 are due in June and September. There is a scheduled capital outlay of $300,000 in September. Archer Electronics's ending cash balance in May is $20,000. The minimum desired cash balance is $10,000. Prepare a schedule of monthly cash receipts, monthly cash payments, and a complete monthly cash budget with borrowing and repayments for June through September. The maximum desired cash balance is $50,000. Excess cash (above $50,000) is used to buy marketable securities. Marketable securities are sold before borrowing funds in case of a cash shortfall (less than $10,000). 4-36 Chapter 04: Financial Forecasting 4-26. Solution: Archer Electronics Cash Receipts Schedule Sales Cash Sales (10%) Credit Sales (90%) Collections (month after sale) 20% Collections (second month after sale) 80% Total Cash Receipts April $320,000 32,000 288,000 May $300,000 30,000 270,000 57,600 June $ 275,000 27,500 247,500 54,000 July $275,000 27,500 247,500 49,500 Aug. $290,000 29,000 261,000 49,500 Sept. $330,000 33,000 297,000 52,200 230,400 216,000 198,000 198,000 $311,900 $293,000 $276,500 $283,200 4-37 Chapter 04: Financial Forecasting 4-26. (Continued) Archer Electronics Cash Payments Schedule Purchases Payments (month after purchase40%) Payments (second month after purchase60%) Labor Expense (10% of sales) Overhead Interest Payments Cash Dividend Taxes Capital Outlay Total Cash Payments April $130,000 May $120,000 52,000 June July $120,000 $180,000 48,000 48,000 Aug. Sept. $200,000 $170,000 72,000 80,000 78,000 72,000 72,000 108,000 27,500 27,500 29,000 33,000 12,000 30,000 50,000 25,000 12,000 12,000 12,000 30,000 $270,500 $159,500 4-38 25,000 300,000 $185,000 $588,000 Chapter 04: Financial Forecasting 4-26. (Continued) Archer Electronics Cash Budget Cash Receipts......................................... Cash Payments........................................ Net Cash Flow........................................ Beginning Cash Balance......................... Cumulative Cash Balance....................... Monthly Borrowing or (Repayment) ...... Cumulative Loan Balance....................... Marketable Securities Purchased............ (Sold) Cumulative Marketable Securities.......... Ending Cash Balance.............................. June $311,900 270,500 41,400 20,000 61,400 --11,400 11,400 50,000 *Cumulative Marketable Sec. (Aug) $236,400 Cumulative Cash Balance (Sept) -254,800 Required (ending) Cash Balance -10,000 Monthly Borrowing -$28,400 4-39 July $293,000 159,500 133,500 50,000 183,500 --133,500 -144,900 50,000 August September $276,500 $283,200 185,000 588,000 91,500 (304,800) 50,000 50,000 141,500 (254,800) -*28,400 -28,400 91,500 --- (236,400) 236,400 -50,000 10,000 Chapter 04: Financial Forecasting 27. Percent-of-sales method (LO3) Owen's Electronics has 9 operating plants in seven southwestern states. Sales for last year were $100 million, and the balance sheet at year-end is similar in percentage of sales to that of previous years (and this will continue in the future). All assets (including fixed assets) and current liabilities will vary directly with sales. The firm is working at full capacity. Balance Sheet (in $ millions) Assets Liabilities and Stockholders' Equity Cash............................................ $2 Accounts payable....................... $15 Accounts receivable................... 20 Accrued wages........................... 2 Inventory.................................... 23 Accrued taxes............................. 8 Current assets........................... $45 Current liabilities...................... $25 Fixed assets................................. 40 Notes payable............................. 10 Common stock............................ 15 Retained earnings....................... 35 Total liabilities and stockholders' equity.................. $85 Total assets.................................. $85 Owen's has an after tax profit margin of 7 percent and a dividend payout ratio of 40 percent. If sales grow by 10 percent next year, determine how many dollars of new funds are needed to finance the growth. 4-40 Chapter 04: Financial Forecasting 4-27. Solution: Owen's Electronics At Full Capacity Spontaneous Assets = Current Assets Fixed Assets Spontaneous Liabilities = Acc. Pay. + Accrued Wages & Taxes Required New Funds = A L S S PS2 1 D S S S = 10% $100 mil. S = $10,000,000 RNF (millions) = 85 25 $10,000,000 $10,000,000 .07 100 100 $110,000,000 1 .40 .85 $10,000,000 .25 $10,000,000 .07 $110,000,000 .60 $8,500,000 $2,500,000 $4,620,000 RNF=$1,380,000 4-41 Chapter 04: Financial Forecasting 28. Percent-of-sales method (LO3) The Manning Company has financial statements as shown below, which are representative of the company's historical average. The firm is expecting a 20 percent increase in sales next year, and management is concerned about the company's need for external funds. The increase in sales is expected to be carried out without any expansion of fixed assets, but rather through more efficient asset utilization in the existing store. Among liabilities, only current liabilities vary directly with sales. Using the percent-of-sales method, determine whether the company has external financing needs, or a surplus of funds. (Hint: A profit margin and payout ratio must be found from the income statement.) Income Statement Sales............................................................. Expenses....................................................... Earnings before interest and taxes................ Interest.......................................................... Earnings before taxes................................... Taxes............................................................. Earnings after taxes...................................... Dividends..................................................... Assets Cash.............................................. Accounts receivable...................... Inventory....................................... Current assets.............................. Fixed assets................................... Total assets.................................... $200,000 158,000 $ 42,000 7,000 $ 35,000 15,000 $ 20,000 $ 6,000 Balance Sheet Liabilities and Stockholders' Equity $ 5,000 Accounts payable.......................... $ 25,000 40,000 Accrued wages.............................. 1,000 75,000 Accrued taxes............................... 2,000 $120,000 Current liabilities........................ $ 28,000 80,000 Notes payable............................... 7,000 Long-term debt............................. 15,000 Common stock.............................. 120,000 Retained earnings......................... 30,000 Total liabilities and $200,000 stockholders' equity.................... $200,000 4-42 Chapter 04: Financial Forecasting 4-28. Solution: Manning Company Earnings after taxes $20,000 10% Sales $200,000 Dividends $6,000 Payout ratio = 30% Earnings 20,000 Profit margin = Change in Sales 20% $200,000 $40,000 SpontaneousAssets Cash Acc. Rec. Inventory Spontaneous Liabilities Acc. Payable Accrued Wages & Taxes A L RNF=S S PS 1 2 D S S $120,000 $28,000 = $40,000 $40,000 .10 $240,000 1 .30 $200,000 $200,000 =.60 $40,000 .14 $40,000 .10 $240,000 .70 =$24,000 $5,600 $16,800 RNF = $1,600 The firm needs $1,600 in external funds. 4-43 Chapter 04: Financial Forecasting 29. Percent-of-sales method (LO3) Conn Man's Shops, Inc., a national clothing chain, had sales of $300 million last year. The business has a steady net profit margin of 8 percent and a dividend payout ratio of 25 percent. The balance sheet for the end of last year is shown below. Balance Sheet End of Year (in $ millions) Assets Cash................................................. Accounts receivable........................ Inventory......................................... Plant and equipment........................ $ 20 25 75 120 Total assets...................................... $240 Liabilities and Stockholders' Equity Accounts payable.......................... $ 70 Accrued expenses......................... 20 Other payables.............................. 30 Common stock.............................. 40 Retained earnings......................... 80 Total liabilities and Stockholders' equity.................. $240 The firm's marketing staff has told the president that in coming year there will be a large increase in the demand for overcoats and wool slacks. A sales increase of 15 percent is forecast for the company. All balance sheet items are expected to maintain the same percent-of-sales relationships as last year, except for common stock and retained earnings. No change is scheduled in the number of common stock shares outstanding, and retained earnings will change as dictated by the profits and dividend policy of the firm. (Remember the net profit margin is 8 percent.) a. Will external financing be required for the company during the coming year? b. What would be the need for external financing if the net profit margin went up to 9.5 percent and the dividend payout ratio was increased to 50 percent? Explain. This included fixed assets as the firm is at full capacity. 4-44 Chapter 04: Financial Forecasting 4-29. Solution: Conn Man's Shops, Inc. a. Required New Funds = A L S S PS2 1 D S S S = 15% $300,000,000 = $45,000,000 RNF 240 120 $45,000,000 $45,000,000 .08 300 300 $345,000,000 1 .25 .80 $45,000,000 .40 $45,000,000 .08 $345,000,000 .75 $36,000,000 $18,000,000 $20,700,000 RNF = $2,700,000 A negative figure for required new funds indicates that an excess of funds ($2.7 mil.) is available for new investment. No external funds are needed. b. RNF $36,000,000 $18,000,000 .095($345,000,000) 1 .5 $36,000,000 $18,000,000 $16,387,500 = $1,612,500 external funds required The net profit margin increased slightly, from 8% to 9.5%, which decreases the need for external funding. The dividend payout ratio increased tremendously, however, from 25% to 50%, necessitating more external financing. The effect of the 4-45 Chapter 04: Financial Forecasting dividend policy change overpowered the effect of the net profit margin change. 4-46 Chapter 04: Financial Forecasting COMPREHENSIVE PROBLEM Comprehensive Problem 1. Mansfield Corporation (external funds requirement) (LO4) Mansfield Corporation had 2010 sales of $100 million. The balance sheet items that vary directly with sales and the profit margin are as follows: Percent 5% Cash.................................................................. Accounts receivable................................................... 15 Inventory.................................................................... 20 Net fixed assets.......................................................... 40 Accounts payable....................................................... 15 Accruals..................................................................... 10 Profit margin after taxes............................................. 10% The dividend payout rate is 50 percent of earnings, and the balance in retained earnings at the end of 2010 was $33 million. Notes payable are currently $7 million. Long-term bonds and common stock are constant at $5 million and $10 million, respectively. a. b. c. How much additional external capital will be required for next year if sales increase 15 percent? (Assume that the company is already operating at full capacity.) What will happen to external fund requirements if Mansfield Corporation reduces the payout ratio, grows at a slower rate, or suffers a decline in its profit margin? Discuss each of these separately. Prepare a pro forma balance sheet for 2011 assuming that any external funds being acquired will be in the form of notes payable. Disregard the information in part b in answering this question (that is, use the original information and part a in constructing your pro forma balance sheet). 4-47 Chapter 04: Financial Forecasting CP 4-1. Solution: Mansfield Corporation Sales .15 $100 million =15 million Spontaneous assets 5% 15% 20% 40% 80% Spontaneous liabilities 15% 10% 25% a. A L S S PS2 1 D S S .8 $15million .25 $15 million .10 $115 1 .5 RNF= $12million $3.75million $5.75million = $2.5 million b. If Mansfield reduces the payout ratio, the company will retain more earnings and need less external funds. A slower growth rate means that less assets will have to be financed and in this case, less external funds would be needed. A declining profit margin will lower retained earnings and force Mansfield Corporation to seek more external funds. c. Balance SheetDecember 31, 2011 (Dollars in Millions) Cash............................. Accounts Receivable.... Inventory...................... Net Fixed Assets........... 1 2 $ 5.75 17.25 23.00 46.00 Accounts Payable........ Accruals....................... Notes Payable.............. Long-Term Bonds........ Common Stock............ _____ Retained Earnings........ $92.00 $ 17.25 11.50 17.501 5.00 10.00 38.752 $92.00 Original notes payable plus required new funds. This is the plug figure. 2011 retained earnings (end of 2010) + PS2 (1-D) 4-48 Chapter 04: Financial Forecasting Comprehensive Problem 2 Marsh Corporation (financial forecasting with seasonal production) (LO5) The difficult part of solving a problem of this nature is to know what to do with the information contained within a story problem. Therefore, this problem will be easier to complete if you rely on Chapter 4 for the format of all required schedules. The Marsh Corporation makes standard-size 2-inch fasteners, which it sells for $155 per thousand. Mr. Marsh is the majority owner and manages the inventory and finances of the company. He estimates sales for the following months to be: January............... February............. March................. April................... May..................... $263,500 (1,700,000 fasteners) $186,000 (1,200,000 fasteners) $217,000 (1,400,000 fasteners) $310,000 (2,000,000 fasteners) $387,500 (2,500,000 fasteners) Last year Marsh Corporation's sales were $175,000 in November and $232,500 in December (1,500,000 fasteners). Mr. Marsh is preparing for a meeting with his banker to arrange the financing for the first quarter. Based on his sales forecast and the following information he has provided, your job as his new financial analyst is to prepare a monthly cash budget, monthly and quarterly pro forma income statements, a pro forma quarterly balance sheet, and all necessary supporting schedules for the first quarter. Past history shows that Marsh Corporation collects 50 percent of its accounts receivable in the normal 30-day credit period (the month after the sale) and the other 50 percent in 60 days (two months after the sale). It pays for its materials 30 days after receipt. In general, Mr. Marsh likes to keep a two-month supply of inventory in anticipation of sales. Inventory at the beginning of December was 2,600,000 units. (This was not equal to his desired twomonth supply.) The major cost of production is the purchase of raw materials in the form of steel rods, which are cut, threaded, and finished. Last year raw material costs were $52 per 1,000 fasteners, but Mr. Marsh has just been notified that material costs have risen, effective January 1, to $60 per 1,000 fasteners. The Marsh Corporation uses FIFO inventory accounting. Labor costs are relatively constant at $20 per thousand fasteners, since workers are paid on a piecework basis. Overhead is allocated at $10 per thousand units, and selling and administrative expense is 20 percent of sales. Labor expense and overhead are direct cash outflows paid in the month incurred, while interest and taxes are paid quarterly. The corporation usually maintains a minimum cash balance of $25,000, and it puts its excess cash into marketable securities. The average tax rate is 40 percent, and Mr. Marsh usually pays out 50 percent of net income in dividends to stockholders. Marketable securities are sold before funds are borrowed when a cash shortage is faced. Ignore the interest on any short-term borrowings. Interest on the long-term debt is paid in March, as are taxes and dividends. 4-49 Chapter 04: Financial Forecasting As of year-end, the Marsh Corporation balance sheet was as follows: MARSH CORPORATION Balance Sheet December 31, 201X Assets Current assets: Cash.............................................................. Accounts receivable...................................... Inventory....................................................... Total current assets..................................... Fixed assets: Plant and equipment...................................... Less: Accumulated depreciation................ Total assets...................................................... $ 30,000 320,000 237,800 $ 587,800 1,000,000 200,000 Liabilities and Stockholders' Equity Accounts payable............................................ Notes payable.................................................. Long-term debt, 8 percent............................... Common stock................................................ Retained earnings............................................ Total liabilities and stockholders' equity........ $ 93,600 0 400,000 504,200 390,000 $1,387,800 4-50 800,000 $1,387,800 Chapter 04: Financial Forecasting CP 4-2. Solution: Marsh Corporation Forecasting with Seasonal Production Projected Unit Sales +Desired Ending Inventory (2 months supply) Beginning Inventory Units to be Produced Dec. 1,500,000 Jan. 1,700,000 Feb. 1,200,000 Mar. 1,400,000 2,900,000 2,600,000 3,400,000 4,500,000 2,600,000 2,900,000 2,600,000 3,400,000 1,800,000 1,400,000 2,000,000 2,500,000 4-51 Chapter 04: Financial Forecasting CP 4-2. (Continued) Monthly Cash Payments Dec. Units to be produced Materials (from previous month) Labor ($20 per thousand units) Overhead ($10 per thousand units) Selling & adm. expense (20% of sales) Interest Taxes (40% tax rate) Dividends Total Payments Jan. 2,000,000 Mar. 2,500,00 0 $ 84,000 $ 120,000 $ 40,000 $ 50,000 $ 14,000 $ 20,000 $ 25,000 $ 52,700 $ 37,200 $ 43,400 $ 8,000 1,800,000 1,400,000 $ 93,600 $ 28,000 Feb. $64,560* $48,420* $188,300 $181,200 $359,380 *See the pro forma income statement, which follows this material later on, for the development of these values. 4-52 Chapter 04: Financial Forecasting CP 4-2. (Continued) Marsh Corporation Monthly Cash Receipts Sales Collection s (50% of Previous month) Collection s (50% of 2 months earlier) Total Collection s Nov. $175,000 Dec. $232,500 87,500 Jan. $263,500 116,250 Feb. $186,000 131,750 Mar. $217,000 93,000 87,500 116,250 131,750 $203,750 $248,000 $224,750 Monthly Cash Flow Cash Receipts Cash Payments Net Cash Flow January $203,750 188,300 15,450 February $248,000 181,200 66,800 4-53 March $224,750 359,380 (134,630) Chapter 04: Financial Forecasting CP 4-2. (Continued) Marsh Corporation Cash Budget Net Cash Flow Beginning Cash Balance Cumulative Cash Balance Loans and (Repayments) Cumulative Loans Marketable Securities Cumulative Marketable Securities Ending Cash Balance January February $15,450 $66,800 30,000 25,000 $45,450 $91,800 -0-0-0-020,450 66,800 20,450 87,250 $25,000 $25,000 March $(134,630) 25,000 ($109,630) 47,380 47,380 (87,250) -0$25,000 Marsh Corporation Pro Forma Income Statement Sales Cost of Goods Sold Gross Profit Selling and Admin. Expense Interest Expense Net Profit Before Tax Taxes Net Profit After Tax Less: Common Dividends Increase in Retained Earnings Jan. Feb. Mar. Total $263,500 $186,000 $217,000 $666,500 139,400 98,400 126,000 363,800 124,100 87,600 91,000 302,700 52,700 37,200 43,400 133,300 2,667 $ 68,733 27,493 $ 41,240 2,667 $ 47,733 19,093 $ 28,640 2,666 8,000 $ 44,934 $161,400 17,974 64,560 $ 26,960 $ 96,840 48,420 $ 48,420 4-54 Chapter 04: Financial Forecasting CP 4-2. (Continued) Marsh Corporation Cost of Goods Sold Material........... Labor............... Overhead......... Unit Cost per thousand Unit cost per thousand before January 1st after January 1st $52 $60 20 20 10 10 $82 $90 Ending inventory as of December 31 was 2,900,000; therefore, sales for January and February had a cost of goods sold per thousand units of $82, and March sales reflect the increased cost of $90 per thousand units using FIFO inventory methods. Pro Forma Balance Sheet (March) Assets Current Assets: Cash Accounts Receivable Inventory Plant & Equip: Net Plan Total Assets $ 25,000 310,00 405,00 800,00 0 $1,540,00 0 Liabilities & Stockholders' Equity Current Liabilities: Accounts Payable Notes Payable Long-Term Debt Stockholders' Equity: Common Stock Retained Earnings, Total Liabilities & Stockholders' Equity 4-55 $ 150,000 47,380 400,000 504,200 438,420 $1,540,000 Chapter 04: Financial Forecasting CP 4-2. (Continued) Explanation of Changes in the Balance Sheet: Cash = ending cash balance from cash budget in March Accounts receivable = $217,000 all of March sales 93,000 plus 50% of Feb. $310,000 sales Inventory = ending inventory in March of 4,500,000 units at $90 per thousand Plant and equipment did not change since we did not include depreciation. RE Old RE NI dividends $390,000 $96,840 $48, 240 $438, 420 4-56