Master Budgeting P8-A* Apricot Company is preparing budgets for the quarter ended March 31. The following relates to the company's budgets: Budgeted sales, in units, for the next five months are: December (actual) 10,000 March 10,000 January 8,000 April 9,000 February 12,000 May 11,000 The selling price of the company's product is $20 for the calendar year. The company would like the following budgets prepared for the first quarter: Sales (with schedule of cash collections) All sales are on account. The company's collection pattern is: 70% collected in the month of sale; 25% collected in the month following sale; the remaining 5% is uncollectible. Production The company desires to have inventory on hand at the end of each month equal to 20 percent of the following month's budgeted sales in units. On December 31, 1,600 units were on hand. Materials purchases (with cash payments) Ten pounds of material are required per unit of product. Each pound costs the company $1.50. The company desires to have materials on hand at the end of each month equal to 30 percent of the following month's production needs. This requirement was met on December 31. Purchases for the month of December amounted to $120,000. The company pays for its materials: 50% in the month of purchase and 50% in the month following the purchase. Required (* using an electronic spreadsheet) 1) Prepare five budgets (Sales, Cash collections, Productions, Materials purchases, and Cash payments) for the quarter ended March 31. (Check Figure: Total Cash Payments for the quarter are budgeted to be $443,100.) 2) Assume that Sales will increase 10% for each month, beginning January 1, and prepare a second set of budgets. (Note: Accounts Receivable and Accounts Payable balances, for the first month, remain the same for both spreadsheets.) Master Budgeting (Chapter 8) Swift Company Sales Expected sales (units) Selling price per unit Budgeted sales April 40,000 10 $ 400,000 May 60,000 $ 10 $ 600,000 June Quarter 70,000 170,000 $ 10 10 $ 700,000 $ 1.700.000 May June Cash collections April Accounts receivable $ 70,000 April sales 240,000 May sales June sales Total cash collections $ 310,000 $ 140,000 360,000 Quarter $ 70,000 380,000 570,000 420,000 $ 1,440,000 $ 210,000 420,000 $ 630,000 $ 500.000 Production Budgeted sales (units) Add: Desired end inv Total needs Deduct: Beg iny Units to be produced April 40,000 18.000 58,000 (12.000) 46,000 May 60,000 21,000 81,000 (18,000) 63,000 June 70,000 18,000 88,000 (21,000) 67.000 July 60,000 15.000 75,000 (18.000 57.000 Materials purchases Units to be produced Raw mtis needed Production needs (lb.) Add: Desired end inv Total needs Deduct: Beg inv Required purch (Ib.) Cost purch ($0.60/1b.) April 46,000 5 230,000 31,500 261,500 (23.000) 238,500 143.100 May 63,000 5 315,000 33,500 348,500 (31,500 317,000 190,200 June 67,000 5 335,000 28.500 363,500 (33.500) 330,000 $ 198,000 Quarter 176,000 5 880,000 28.500 908,500 (23,000) 885,500 531,300 $ $ May June $ $ Cash payments April Accounts payable $ 45,000 April purchases 71,550 May purchases June purchases Total cash payments $ 116,550 71,550 95,100 $ 95,100 99.000 $ 194,100 Quarter 45,000 143,100 190,200 99,000 477,300 $ 166,650 $