Please complete problem #2 only. I have attached the answer to #1 and the questions. However I just need #2 completed in excel. thank you

Cost Accounting Project - due November 4 1. Jones Corporation has the following budgeted sales for the selected four-month period: Month July August September October Unit Sales 20,000 35,000 25,000 30,000 Sales price per unit is $180 Plans are to have an inventory of finished product equal to 20% of the unit sales for the next month. There was 4,000 units in beginning inventory on July 1st. Three pounds of materials are required for each unit produced. Each pound of material costs $20. Inventory levels for materials equal 30% of the needs for the next month. Desired ending inventory for September is 25,200 pounds of material. Beginning inventory for July was 20,700 pounds of material. Each unit requires 0.6 hours of direct labor and the average wage rate is $16 per hour. Variable overhead rate is $3.50 per direct labor hour. There is also fixed overhead of $22,000 per month. The company pays a 3% commission on sales. Company has fixed selling and administrative expenses as follows: Rent $6,000/month Utilities $1,200/month Advertising $400/month Office Salaries $35,000/month Required: A. Prepare a sales budget for July, August, and September and in total for the quarter. B. Prepare production budgets for July, August, and September and in total for the quarter. C. Prepare a direct materials purchases budget in pounds and dollars for July, August, and September and in total for the quarter. D. Prepare a direct labor budget in hours and total cost for July, August and September and in total for the quarter. E. Prepare an overhead budget for July, August and September and in total for the quarter. F. Prepare a selling and administrative expenses budget for July, August and September and in total for the quarter. G. Prepare an ending finished goods inventory budget for the quarter (Hint: You have already calculated the desired ending finished goods inventory amount. Assume a stable per unit rate and round the per unit fixed factory overhead rate to two decimal places.) H. Prepare a cost of goods sold budget for the quarter I. Prepare a budged income statement for the quarter-the company falls into the 35% tax bracket for income taxes. 2. Prepare a cash budget for July, August, and September given a cash balance at the beginning of July of $250,000 and given the following: Sales are collected 80 percent in the month of sale and 20 in the following month. The accounts receivable balance at the end of June (collected in July) was $800,000. Direct material purchases are paid 80 percent in the month of purchase and 20 percent in the following month. The accounts payable balance at the end of June (paid in July) was $320,000. All other costs were paid in the month incurred. Dividends paid are expected to be $10,000 per month. A. Prepare a sales budget for July, August, and September and in total for the quarter. Units Sales Sales Price - per unit Sales in dollars B. Prepare production budgets for July, August, and September and in total for the quarter. Units Sales Add: Desired ending Inventory Total units required Less: Beginning Inventory Units to be produced C. Sales Budget July August September Total 20,000 35,000 25,000 80,000 $180 $180 $180 $180 $3,600,000 $6,300,000 $4,500,000 $14,400,000 Production Budget July 20,000 7,000 27,000 4,000 23,000 August September 35,000 25,000 5,000 6,000 40,000 31,000 7,000 5,000 33,000 26,000 Prepare a direct materials purchases budget in pounds and dollars for July, August, and September and in total for the quarter. Direct Material Purchases Budget July August September Units to be produced 23,000 33,000 26,000 Pound required - per unit 3 3 3 Total pounds required for production 69,000 99,000 78,000 Add: Desired ending Inventory 29,700 23,400 25,200 Total pounds required 98,700 122,400 103,200 Less: Beginning Inventory 20,700 29,700 23,400 Pounds to be purchased 78,000 92,700 79,800 Price per pound $20 $20 $20 Cost of material purchased $1,560,000 $1,854,000 $1,596,000 D. August September 33,000 26,000 0.6 0.6 19,800 15,600 $16 $16 $316,800 $249,600 Total 82,000 0.6 49,200 $16 $787,200 Prepare an overhead budget for July, August and September and in total for the quarter. Overhead Budget July Total Labor hours required for production 13,800 Variable overhead - per direct labor hour $3.50 Total Variable overhead $48,300 Fixed Overhead $22,000 Total Overhead cost $70,300 F. Total 82,000 3 246,000 25,200 271,200 20,700 250,500 $20 $5,010,000 Prepare a direct labor budget in hours and total cost for July, August and September and in total for the quarter. Direct Labor Budget July Units to be produced 23,000 Labor hours required - per unit 0.6 Total Labor hours required for production 13,800 Labor rate per hour $16 Total Cost of Direct Labor $220,800 E. Total 80,000 6,000 86,000 4,000 82,000 August September 19,800 15,600 $3.50 $3.50 $69,300 $54,600 $22,000 $22,000 $91,300 $76,600 Total 49,200 $3.50 $172,200 $66,000 $238,200 Prepare a selling and administrative expenses budget for July, August and September and in total for the quarter. Sales - in dollars Sales commission rate Sales commission in dollars Fixed selling & Admin Expenses Rent Utilities Advertising Office Salaries Total Fixed expenses Total Selling & Administrative cost Selling & Administrative Budget July August September 3,600,000 6,300,000 4,500,000 3% 3% 3% 108,000 189,000 135,000 Total 14,400,000 3% 432,000 6,000 1,200 400 35,000 $42,600 6,000 1,200 400 35,000 $42,600 6,000 1,200 400 35,000 $42,600 $127,800 $150,600 $231,600 $177,600 $559,800 G. Prepare an ending finished goods inventory budget for the quarter (Hint: You have already calculated the desired ending finished goods inventory amount. Assume a stable per unit rate and round the per unit fixed factory overhead rate to two decimal places.) Desired Ending Inventory Variable cost - per unit Cost of Ending Inventory H. August September 5,000 6,000 $71.70 $71.70 $358,500 $430,200 Prepare a cost of goods sold budget for the quarter Number of Units sold Variable cost - per unit Cost of Goods Sold I. Ending Inventory Budget July 7,000 $71.70 $501,900 Variable cost - per unit Direct Material 3 pounds @ $20 Direct labor 0.60 hours @ $16 Variable Overhead 0.60 hours @ $3.50 Total Cost of Goods Sold July August September 20,000 35,000 25,000 $71.70 $71.70 $71.70 $1,434,000 $2,509,500 $1,792,500 Total 80,000 $71.70 $5,736,000 Prepare a budged income statement for the quarter-the company falls into the 35% tax bracket for income taxes. Budgeted Income Statement July August Sales $3,600,000 $6,300,000 Less: Cost of Goods Sold $1,434,000 $2,509,500 Gross Margin $2,166,000 $3,790,500 Less: Fixed Overhead $22,000 $22,000 Selling & Administrative Expense $150,600 $231,600 Income before Tax $1,993,400 $3,536,900 Less: Income Tax - 35% $697,690 $1,237,915 Net Income $1,295,710 $2,298,985 September Total $4,500,000 $14,400,000 $1,792,500 $5,736,000 $2,707,500 $8,664,000 = $66,000 $177,600 $559,800 $2,507,900 $8,038,200 $877,765 $2,813,370 $1,630,135 $5,224,830 $60.00 $9.60 $2.10 $71.70