ACCTG 231 - 5pring 2023 Excel Problem 3 - Directions Guidance Corp is preparing its Master Budget for 20XX. You need to prepare Guidance's Direct Materials Budget and Schedule of Cash Disbursements for Materials Purchases on the excel template provided and create a function that allows management to enter a month and get amount of materials purchased and cash spent for that month. The given information is already input into the spreadsheet. REQUIRED 1. PREPARE A DIRECT MATERIALS BUDGET (3 POINTS) Prepare the Direct Materials Budget for the first three months of Guidance's fiscal year (January, February, and March), along with the full quarter. Relevant information - Required Production 0 January =90,000 units 0. 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 lanuary 1,20XX=50,000 pounds. - Percentage of the following month's production used to determine desired ending inventory =20% - Raw materials cost per pound =$2.5 per pound 2. PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (4 POINTS) Prepare the Schedule of Cash Payments for the first three months of Guidance's 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. - Guidance's payment schedule: o 20% of purchases are paid for in the month of purchase o 65% are paid for in the month after purchase 0 15% are paid for in the second month after purchase - Beginning accounts payable =5600,000. Assume 75% of the accounts payable will be paid in January and 25% in February. Check Figures Because this problem has several areas that frequently pose problems for students, check figures are orovided to give vou feedback as to whether vour budget/schedule has errors. 3. Create a INDEX and MATCH nested function so that the user can enter a month ('January', 'February', 'March' or 'Quarter'] and excel returns the amount Direct Materials purchases for that month in cell B4 and how much cash is being spent in C4 on sheet "3) Management Lookup". (2 POINTS) You need to connect the amounts from the Direct Materials Budget to this budget in your formulas (i.e. do not use dollar values times percentages, but cell references). 1) Direct Materials Budget 2) Schedule of Cash Payments 3) Management Lookup ACCTG 231 - 5pring 2023 Excel Problem 3 - Directions Guidance Corp is preparing its Master Budget for 20XX. You need to prepare Guidance's Direct Materials Budget and Schedule of Cash Disbursements for Materials Purchases on the excel template provided and create a function that allows management to enter a month and get amount of materials purchased and cash spent for that month. The given information is already input into the spreadsheet. REQUIRED 1. PREPARE A DIRECT MATERIALS BUDGET (3 POINTS) Prepare the Direct Materials Budget for the first three months of Guidance's fiscal year (January, February, and March), along with the full quarter. Relevant information - Required Production 0 January =90,000 units 0. 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 lanuary 1,20XX=50,000 pounds. - Percentage of the following month's production used to determine desired ending inventory =20% - Raw materials cost per pound =$2.5 per pound 2. PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (4 POINTS) Prepare the Schedule of Cash Payments for the first three months of Guidance's 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. - Guidance's payment schedule: o 20% of purchases are paid for in the month of purchase o 65% are paid for in the month after purchase 0 15% are paid for in the second month after purchase - Beginning accounts payable =5600,000. Assume 75% of the accounts payable will be paid in January and 25% in February. Check Figures Because this problem has several areas that frequently pose problems for students, check figures are orovided to give vou feedback as to whether vour budget/schedule has errors. 3. Create a INDEX and MATCH nested function so that the user can enter a month ('January', 'February', 'March' or 'Quarter'] and excel returns the amount Direct Materials purchases for that month in cell B4 and how much cash is being spent in C4 on sheet "3) Management Lookup". (2 POINTS) You need to connect the amounts from the Direct Materials Budget to this budget in your formulas (i.e. do not use dollar values times percentages, but cell references). 1) Direct Materials Budget 2) Schedule of Cash Payments 3) Management Lookup