ACCTG 231 Fall 2022 Excel Problem 3 Directions Guidance Corp is preparing its Master Budget for 20XX. You need to prepare Guidances Direct Materials Budget and Schedule of Cash Payments for Materials Purchases on the excel template provided. Five of the points (2 points for the Direct Materials Budget, 2 points for the Schedule of Cash Payments and 1 point for computing March ending A/P) are for the correct accounting and the remaining five are for the spreadsheet correctly recalculating when the grader changes the January production from 150,000 to 200,000 units. The Direct Materials Budget and the Schedule of Cash Payments are on two separate worksheets, so you need to link the worksheets in your formulas (similar to what was done in Excel Problem #1). Below is all of the given information, which is also input into spreadsheet cells in the Excel template. REQUIRED 1. PREPARE A DIRECT MATERIALS BUDGET (worksheet titled Direct Materials Budget) (2 POINTS) Prepare the Direct Materials Budget for the first three months of Guidances fiscal year (January, February, and March), along with the full quarter, using the template in the Excel spreadsheet. Relevant information Required Production o January = 90,000 units o February = 70,000 units o March = 125,000 units o April = 130,000 units Pounds of materials needed per unit = 3 pounds Beginning raw materials inventory on January 1, 20XX = 50,000 pounds. Desired ending inventory = 20% of the pounds of materials needed to meet production in the following month Raw materials cost = $2.50 per pound 2. PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS in the worksheet titled Schedule of Cash Payments for M (2 POINTS) Prepare the Schedule of Cash Payments for the first three months of Guidances fiscal year (January, February, and March) and for the quarter, using the template in the Excel spreadsheet. HINT: cost of raw materials that Guidance is paying for comes from the last line of your direct materials budget be sure your formulas link spreadsheets DO NOT just use the numbers computed in the first spreadsheet in your formulas on the second spreadsheet. Guidances payment schedule: o 20% of purchases are paid for in the month of purchase o 65% are paid for in the month after purchase o 15% are paid for in the second month after purchase Beginning accounts payable = $600,000. Assume 70% of the accounts payable will be paid in January and 30% in February. 3. In cell A18 of the Cash Payments worksheet, use a formula to compute ending A/P for March. (1 POINT)\

Because this problem has a number of areas that frequently pose problems for students, check figures for the two budgets are provided to give you feedback as to whether your budget/schedule has errors. January February March Quarter Cost of Raw Materials to be Purchased $655,000 $607,500 $945,000 $2,207,500 Total Cash Disbursements for Materials $551,000 $727,250 $682,125 $1,960,375

This is the excel

DIRECT MATERIALS BUDGET AND SCHEDULE OF CASH PAYMENTS FOR MATERIALS PURCHASES DIRECT MATERIALS BUDGET AND SCHEDULE OF CASH PAYMENTS FOR MATERIALS PURCHASES