AC306 Unit 5 Assignment template [Protected View] - Excel File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do i PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing B 61 4. Calculate the quantity of material (in feet) that needs to be purchased by month and in total. Also calculate the raw material purchases for each month. 62 The direct materials budget for the third quarter: 63 July August September October Required production in units 64 of finished goods Units of raw materials needed 65 per unit of finished goods Units of raw materials needed 66 to meet production Add desired units of ending 67 raw materials inventory Total units of raw materials 68 needed Less units of beginning raw 69 materials inventory Units of raw materials to be 70 purchased 71 Unit cost of raw materials Cost of raw materials to be 72 purchased 73 74 75 76 77 78 + 79 80 81 82 83 84 85 86 87 28AC306 Unit 5 Assignment template [Protected View] - Excel File Home Insert Draw Page Layout Formulas Data leview View Help Tell me what you want to do PROTECTED VIEW Be careful- files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing B m G Enter titles and accounts in the green cells Enter Numbers in the blue cells Enter your Calculations in the yellow cells Uni Dallas Company manufactures table cloths. The company is in the process of developing next quarter's budgets and has gathered the following information for the budget preparation: a. The Marketing Department has estimated sales as follows for the remainder of the year (in units): July 25,000 October 20,000 August 40,000 November 25,000 10 September 39,000 December 37,500 11 12 a. The selling price. 25 per unit + 13 14 b. All sales are on account. Based on past experience, sales are collected in the following pattern: 15 16 17 25% in the month of sale 18 70% in the month following sale 19 5% uncollectible 20 21 c. Sales for June totaled $525,000. 525000 22 23 d. The company desires to keep a finished goods inventory equal to 20% of the following month's sales. The June ending inventory will meet this requirement. 24 e. Each table cloth requires 6 feet of a special linen, can be hard to source at times. The company requires that the ending inventory of linen be equal to 60% of the 25 following month's production needs. 25 Inventory on hand 27 30-Jun 95,000 feet 28 30-Sep feet 29 30 f. The linen costs $1.00 per foot. 31 Problem 1 Problem 2AC306 Unit 5 Assignment template [Protected View] - Excel File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do i PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing B3 f. The linen costs $1.00 per foot. 31 32 33 Instructions 34 1. Calculate the budgeted sales, by month and in total, for th quarter. 35 July August September Quarter 36 Budgeted units sales 37 Selling price per unit 38 Budgeted sales 39 40 41 2. Calculate the net expected cash balances, by month and in total, for the quarter. 42 The expected cash collections from sales for the third quarter 43 44 July August September Quarter 45 Accounts receivable, June 30: 46 July sales: 47 August sales: 48 September sales: 49 Total cash collections 50 51 3. Calculate the estimated quantity of beach umbrellas that need to be produced in July, August, September, and October. 52 The production budget (quantity of beach umbrellas) for July-October: 53 July August September October 54 Budgeted unit sales Add desired units of ending 55 finished goods inventory 56 Total needs Less units of beginning 57 finished goods inventory 58 Required production in units 59 60