Excel Edit View Insert Format Tools Data Window Help W Q 8 Fri May 9 4:30 PM OO AutoSave W H E Y V C ... ACG3080_Mod5_Carrillo Q Search (Cmd + Ctri + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share Times New Roman 12 A A ab, General Insert v x Delete Paste IUV A = v % 9 Conditional Format Cell Sort & Find & Sensitivity Add-ins Analyze Formatting as Table Styles Format DV Filter Select Data KZ X V fx B c D G H L M N O P Q R Primrose Outdoor Company manufactures grill covers . The company is preparing detailed budgets for the third quarter and has assembled the following information to assist in the budget preparation: Marketing Department has estimated sales as follows for the remainder of the year (in units) July 35,000 October 25,00 August 52,500 November 15,00 September 41,000 December 10,000 The selling price of the grill cover is $15 per unit Sales for June totaled $420,000 All sales are on account. Based on past experience, sales are collected in the following pattern: 40% in the month of sale $5% in the month 5% uncollectible The company maintains finished goods inventories equal to 20% of the following month's sales. This requirement will be met at the end of June. 16 Each grill cover requires 2 feet of fire proof material, a material that is sometimes hard to acquire. Therefore, the company requires that the ending inventory of fire proof material be equal to 40% of the following month's production needs. The inventory of fire proof material on hand at the beginning June 30th 60000 feet 18 Fire proof material costs $1.25 per foot. One-half of a month's purchases of the material is paid for in the month of purchase; the remainder is paid for in the following month. The accounts payable on July 1 for purchases of fire proof material during June will be $83,500. Required: 1. Calculate the estimated sales, by month and in total, for the third quarter. 2. Calculate the expected cash collections, by month and in total, for the third quarter. 24 3. Calculate the estimated quantity of grill covers that need to be produced in July, August, September, and October. 4. Calculate the quantity of fire proof material (in feet) that needs to be purchased by month and in total, for the third quarter. 5. Calculate the cost of the raw material purchases by month and in total, for the third quarter. 6. Calculate the expected cash disbursements for raw material purchases, by month and in total, for the third quarter. 28 Sheet1 Ready Accessibility: Good to go 100% Feb 6, 2025 . Learn how to capture the entire screen, a window, or a portion of the screen on your MAY O 9 tvExcel Edit View Insert Format Tools Data Window Help W Q 8 Fri May 9 4:31 PM AutoSave W H E Y V C ... ACG3080_Mod5_Carrillo Q Search (Cmd + Ctri + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share Times New Roman 12 A A ab, General Insert v Six Delete Paste BIUV A += v % 9 Conditional Format Cell Sort & Find & Sensitivity Add-ins Analyze Formatting as Table Styles Format DV Filter Select Data K7 X V fx B C D E F G H L M N P Q R 1. The estimated sales for the third quarter: Month July August September Quarter Budgeted Unit Sales 35,00 52,500 41,000 128,500 Selling price per unit $15 $15 $15 $45 Budgeted Sales $525,000 $787,500 $615,000 $5,782,500 2. The expected cash collections from sales for the third quarter: Month July August September Quarter Accounts Receivable, June 30th 420000 x 55% $ 231,000 July Sales: 52500 x 40% , 55% 21,000 $ 28,875 August Sales September Sales Budgeted Sales 252,000 $ 28,875 $ $ 3. The production budget (quantity of grill covers) for July-October: Month July August September October Budgeted unit sales 35,000 52,50 41,000 Add desired units of ending finished goods inventory 10,500 8,200 Total Inventory needed 5,500 50,700 Less units of beginning finished goods inventory 7,000 10,50 Required production in units 38,50 50,200 0 0 4 & 5. The production budget (quantity of beach umbrellas) for July-October: Month Sheet1 Ready Accessibility: Good to go 100% Feb 6, 2025 . Learn how to capture the entire screen, a window, or a portion of the screen on your MAY O 9 tvExcel Edit View Insert Format Tools Data Window Help W Q 8 Fri May 9 4:31 PM OO AutoSave W H E Y V C ... ACG3080_Mod5_Carrillo Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share Times New Roman 12 A A ab, General Insert v Six Delete Paste BIUV A = += - V v % 9 Conditional Format Cell Sort & Find & Sensitivity Add-ins Analyze Formatting as Table Styles Format DV Filter Select Data K7 X V fx B C D E F G H L M N O P Q R 4 & 5. The production budget (quantity of beach umbrellas) for July-October: Month July August September Quarter Required production in units of finished goods 38,500 Units of raw materials needed per unit of finished goods 2 Units of raw materials needed to meet production 77,000 Add desired units of ending raw materials inventory* 10,160 Total units of raw materials needed 117,160 Less units of beginning raw materials inventory 30,800 Units of raw materials to be purchased 86,360 Unit cost of raw materials $1.25 Unit cost of raw materials $107,950 50 6. The expected cash disbursements for materials purchases for the third quarter: Month July August September Quarter Accounts Payable, June 30th: $83,500 July Purchases 107950*50%, 50% $53,975 $53,975 August Purchases September Purchases Total Cash Disbursements $137,475 $53,975 SO $0 Sheet1 Ready Accessibility: Good to go Feb 6, 2025 . Learn how to capture the entire screen, a window, or a portion of the screen on your MAY O 13 9 "tv