please solve all

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 January =90,000 units February =70,000 units March =125,000 units April =130,000 units - Pounds of materials needed per unit =3 pounds - Beginning raw materials inventory on January 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 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: PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (4 POINIS) 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: 20% of purchases are paid for in the month of purchase 65% are paid for in the month after purchase 15% are paid for in the second month after purchase - Beginning accounts payable =$600,000. Assume 75% of the accounts payable will be paid in January and 25% in February. Figures cause this problem has several areas that frequently pose problems for students, check figures are suided tn oive vou feedhack as to whether vour budget/schedule has errors. 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) DIHECT MATERAMIS BUDGET AND SCHEDURE OF CASH PAYMENTS FOR MATERLMS PURCHASES DiRECT MATERIALS BUDGET (3 Points) lasuary February March Quarter Singuired production (units) Pounds of material per unit Pounds of material nended to meet production Pounds of raw materials (per anit) Adt: de-tired pounds of endirg taw mateials impentory Beginning raw materials inventory (pounda) Total pounds of raw manerials needed Deaired ending raw materiala inventory (pounds) Less: pounds of beginnine raw materialy inmentory Cost of row materials per pound Pounds of riw inuterials to be puiztined 12. Cost ol raw materials per pound 13. Cost of raw maderiah to be purchased 2) SCHEDUE OF EXPECTLD CASH PAYMENTS FOR MATTRIALS PUROHASES (4 poisis) January Fobruary March Quarter Gepinning Accounts Payable Beginning accounts pyyable $600,000 Cahh for lanuary Purchuses Cash for Fedruary Purchases Payment Schedule (s) Cash for March Purchases Total Cash Dithursements for Materiats MonthofpurchaseMonthafterpurchaseSecondmonthafterpurchase20K65N15K You need to connect the amounts from the Direct Materials Budget to this bedget in your formulas (l.e. do not use dolar values times percentages, but cell relerences). 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 January =90,000 units February =70,000 units March =125,000 units April =130,000 units - Pounds of materials needed per unit =3 pounds - Beginning raw materials inventory on January 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 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: PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (4 POINIS) 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: 20% of purchases are paid for in the month of purchase 65% are paid for in the month after purchase 15% are paid for in the second month after purchase - Beginning accounts payable =$600,000. Assume 75% of the accounts payable will be paid in January and 25% in February. Figures cause this problem has several areas that frequently pose problems for students, check figures are suided tn oive vou feedhack as to whether vour budget/schedule has errors. 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) DIHECT MATERAMIS BUDGET AND SCHEDURE OF CASH PAYMENTS FOR MATERLMS PURCHASES DiRECT MATERIALS BUDGET (3 Points) lasuary February March Quarter Singuired production (units) Pounds of material per unit Pounds of material nended to meet production Pounds of raw materials (per anit) Adt: de-tired pounds of endirg taw mateials impentory Beginning raw materials inventory (pounda) Total pounds of raw manerials needed Deaired ending raw materiala inventory (pounds) Less: pounds of beginnine raw materialy inmentory Cost of row materials per pound Pounds of riw inuterials to be puiztined 12. Cost ol raw materials per pound 13. Cost of raw maderiah to be purchased 2) SCHEDUE OF EXPECTLD CASH PAYMENTS FOR MATTRIALS PUROHASES (4 poisis) January Fobruary March Quarter Gepinning Accounts Payable Beginning accounts pyyable $600,000 Cahh for lanuary Purchuses Cash for Fedruary Purchases Payment Schedule (s) Cash for March Purchases Total Cash Dithursements for Materiats MonthofpurchaseMonthafterpurchaseSecondmonthafterpurchase20K65N15K You need to connect the amounts from the Direct Materials Budget to this bedget in your formulas (l.e. do not use dolar values times percentages, but cell relerences)