plz show excel formulas that were used
Excer Problem s - 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. Six of the points (3 for each budget/schedule) are for the correct accounting and the remaining four are for the spreadsheet correctly recalculating when the grader changes the January production from 150,000 to 200,000 units. 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, using the template in the Excel spreadsheet. Relevant information Required Production o January = 150,000 units o February = 180,000 units o March = 100,000 units o April = 120,000 units Pounds of materials needed per unit = 4 pounds Beginning raw materials inventory on January 1, 20XX = 40,000 pounds. Percentage of the following month's production used to determine desired ending inventory = 20% Raw materials cost per pound = $5.00 per pound 2. PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (3 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 25% of purchases are paid for in the month of purchase . . o April = 120,000 units Pounds of materials needed per unit = 4 pounds Beginning raw materials inventory on January 1, 20XX = 40,000 pounds. Percentage of the following month's production used to determine desired ending inventory = 20% Raw materials cost per pound = $5.00 per pound . . 2. PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (3 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 25% of purchases are paid for in the month of purchase o 60% 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. Check Figures Because this problem has a number of areas that frequently pose problems for students, check figures 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 $3,520,000 $3,280,000 $2,080,000 $8,880,000 Total Cash Disbursements for Materials $1,300,000 $3,112,000 $3,016,000 $7,428,000 A32 D E M 1 1 DIRECT MATERIALES BUDGETAISCHEDULE OF CARE COLLECTIONS 1 Archdau to Febrny Production March Production Aprill Production 150,000 180,000 100.000 120,000 February March Quarter 1 DUCT MATERIALS UDGET 4 January Recured production 6 Pounds of material perut 2 Pounds of materialiended to reproduction Add: desired pounds ording story 5 Teto pounds of raw materia de 10 pounds of beginning water inventory 11 Pounds of rowers to be purcha 12 Cotowi per pound 13 Contrasto be purchaud Cost of raw materials per pound Beginning water story pounds) Pounds of raw materials per Dedred ending raw materials inventory (pounds) $5 40,000 4 20% Beginning accounts payable Aladin A/P paid in February $600,000 70N BOK February March Quarter 15 SOLE OF PICTO CASH DISBURSEMINES FOR MATERIALS FUCIUSES 16 wy 17 Begin Accounts Payable 18 Cath for any Purch 19 Ch for forway Purcha 30 CM Punch 71 Toth the form Payment Schedule Month of purchase Month after purchase Second month after purchase 25 60N 15% 11 24 25 20 22 20
