I need help in this hw and please I need it in excel so i can see the formulas.

I have the answers already. I need in Excel and show me the formulas please

Please must show formulas

I need step 3, 4, 5 and Step 6, 7 and 8

3. Materials Purchases Budget: Two sheets of metal are required to produce a of each month equal to 10 percent of the following month's production needs. The materials inventory on June 30 is 2,500 sheets of metal. October production is budgeted for 15.125 units. Required: Prepare a direct materials purchases budget for metal sheets for the quarter ending September 30. Show how many sheets of metal should be purchased each month as well as for the quarter in total Check Figure: Total budgeted units of material to purchase for the quarter 83,02:5 4. Cash Payments of Materials Purchases: Johnson Inc. purchases raw materials on account for use in production. Sheets of metal used in production cost S3.00 each. Forty percent of a month's purchases are paid for in the month of purchase; the other 60 percent is paid for in the following month. No discount terms are available. The accounts payable balance on June 30 is $43,200 Required: Prepare a cash payment for materials purchases budget and highlight your answer for the following questions: I. 2. 3. 4. How much cash is required for payments made on account, payable in the month of July? How much cash is required for payments made on account, payable in the month of August? How much cash is required for payments made on account, payable in the month of September? What is the expected accounts payable balance on September 30, 2017? Check Figure: Total budgeted cash payments made for materials purchases for the quarter S239,445 5. Cash Payments for Direct Labor: Each unit produced requires 120 minutes (2.0 hours) of direct labor. Employees are paid at a rate of $7.00 per hour. Required: Prepare a cash payments for direct labor budget, showing total labor cost by month and in total for the quarter. Check Figure: Total budgeted cash payments made for direct labor for the quarter S577,500 6. Cash Payments for Manufacturing Overhead Variable manufacturing overhead is $2.00 per unit produced and fixed manufacturing overhead is $25,000 per month. Fixed manufacturing overhead includes $5,000 in depreciation which does not require a cash outflow. Required: Prepare a cash payments for manufacturing overhead budget, showing total manufacturing overhead cost by month and in total for the quarter. Check Figure: Total budgeted cash payments made for manufacturing overhead for the quarter S142,500 7. Cash Pavments for Selling and Administrative (S&A)Expenses Budget: Variable selling and administrative expenses are $2.50 per unit sold and fixed selling and administrative expenses require a cash outflow. Required: Prepare a cash payments for S&A expenses budget, showing total S&A expense by month and in total for the quarter. Check Figure: Total budgeted cash payments made for S&A expenses for the quarter S185,250 8. Cash Receipts Budget: All sales are on account. Johnson's collection pattern is: 50 percent collected in month of sale 30 percent collected in month after sale 20 percent collected in the second month after sale June sales were $350,000. Accounts receivable on June 30 is $175,000, all of which is collectible. Required: Prepare a cash receipts budget (schedule of cash receipts) and highlight your answer for the following questions: 1. How much total cash is Johnson Inc. expecting to receive in the month of July? 2. How much total cash is Johnson Inc. expecting to receive in the month of August? 3. How much total cash is Johnson Inc. expecting to receive in the month of September? 4 What is the expected accounts receivable balance on September 30, 2017 Check Figure: Total budgeted cash receipts for the quarter -S1,240,750 1 1. Sales Budget September Total 3 Budgeted sales in units 4 xPrice per unit 5Projected sales revenue 12,000 14,000 14 15,000 14.00S 7 2. Production Budget tember Total 9 Budgeted product sales in units 0Desired produet units in ending inventory 12,000 3,500 15.500 40.500 3,750 44.250 14,000 14,500 3,750 18.250 = Total product units needed 2-Prodoct units in beginning inventory 13Product units to produce 14 15 3. Material Purchases Budget 16 17 Units to produce 18 x Material needad per unit 19 = Material needed for units to produce 20Desired units of material in ending inventory 21 Total units of material needad 22Units of material in beginning inventory 23Units of material to porchase 17.625 12.500 14,625 tember Total 12.500 14,125 14,625 41,250 25.000 28,250 29.250 2.500 25 4. Cash Payments for Material Purchases 53. Material Purchases Budget 16 7 Units to produce 18 Material needed per unit 19Material needed for units to produce 20 +Desired units of material in ending inventory 21Total units of material needed 22-Units of material in beginning inventory 3Units of material to purchase ber Total 12,500 14,125 14,625 41,250 25,000 28.250 2.825 2.925 25 4. Cash Payments for Material Purchases 26 27 Material purchases (sheets) 28 Cost per unit 29Total cost 30 1 Payables from June 32 July purchases 33 August purchases 34 September purchases 5 Total payments in month 36 ber Total Accounts Pavable 36 375. Cash Payments for Direct Labor 38 39 Units to produce 40 Hours per unit July August September -Total hours required 42x Wage rate per hour 3-Direct labor cost 45 6. Cash Payments for Manufacturing Overhead 46 7 Units to produce 48 x Variable overhead rate 9Variable overhead cost 50 Fixed overhead 51Total manufacturing overhead cost 2 -Deduct depreciation 53Manufacturing overhead- cash 54 July AugustSeptember Total Clipboard Alignment Cells Editing H21 55 7. Cash Payments for S&AExpenses 56 57 Budgeted unit sales 58 Variable S&A per unit 9Variable S&A expense 60Fixed S&A expense 61Total S&A expense 62- Deduct depreciation 63S&A expense cash 64 65 S. Cash Receipts Budget July August ber Total July mber Total 67 Budgeted sales in units 68 x Price per unit 69Projected sales revenue 70 71 Receipts from June sales 72 Receipts from July sales 73 Receipts from August sales 74 Receipts from September sales 75 Total cash receipts 76 Accounts Receivable