ACCTG-Spring 2021 Excel Problem 2 - Directions Para Corp is preparing its Master Budget for 20XX. You need to prepare Para'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 required production from 120,000 to 200,000 units. The given information is already input into the spreadsheet in cells indicated. REQUIRED 1. PREPARE A DIRECT MATERIALS BUDGET (3 POINTS) Prepare the Direct Materials Budget for the first three months of Para's fiscal year (January, February, and March), along with the full quarter using the template in the Excel spreadsheet. O O O Relevant information Required Production January = 120,000 units - cell H2 February = 150,000 units - cell H3 March = 100,000 units - cell H4 o April = 80,000 units (necessary to compute March ending inventory) - cell 45 Pounds of materials needed per unit = 4 pounds-cell H7 Beginning raw materials inventory on January 1, 20XX = 60,000 pounds - cell H8. Percentage of the following month's production used to determine desired ending inventory = 20%-cell H9 Raw materials cost per pound = $2.00 per pound - cell H10 . 2. . PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (3 POINTS) Prepare the Schedule of Cash Payments for the first three months of Para's fiscal year (January, February, and March) and for the quarter using the template in the Excel spreadsheet. Cost of raw materials to be purchased (the last line of your direct materials budget - cells B13, C13, D13 for January, February and March, respectively). Para's payment schedule: o in the month of purchase = 30%-cell H12 o in the month after purchase = 50%-cell H13 and in the second month after purchase = 20% - cell H14 Beginning accounts payable = $750,000 in cell H16. 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 a heads up as to whether your budget/schedule has errors. January February March Quarter Cost of Raw Materials to be purchased $1,080,000 $1,120,000 $ 768,000 $2,968,000 Total Cash Disbursements for Materials $ 849,000 $1,101,000 $1,006,400 $2,956,400 A B D E F I DIRECT MATERIALS BUDGET AND SCHEDULE OF CASH COLLECTIONS January Production February Production March Production April Production 120,000 150,000 100,000 80,000 February March Quarter 4 DIRECT MATERIALS BUDGET January Required production (units) Pounds of material per unit Pounds of material needed to meet production Add: desired pounds of ending raw materials inventory Total pounds of raw materials needed Less: pounds of beginning raw materials inventory Pounds of raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased 60,000 20% $2 Pounds of raw materials (per unit) Beginning raw materials inventory Desired ending raw materials inventory (9 Cost of raw materials per pound Payment Schedule (%) Month of purchase Month after purchase Second month after purchase 30% 50% 20% February March Quarter SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIALS PURCHASES January Beginning Accounts Payable Cash for January Purchases Cash for February Purchases Cash for March Purchases Total Cash Disbursements for materials ACCTG-Spring 2021 Excel Problem 2 - Directions Para Corp is preparing its Master Budget for 20XX. You need to prepare Para'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 required production from 120,000 to 200,000 units. The given information is already input into the spreadsheet in cells indicated. REQUIRED 1. PREPARE A DIRECT MATERIALS BUDGET (3 POINTS) Prepare the Direct Materials Budget for the first three months of Para's fiscal year (January, February, and March), along with the full quarter using the template in the Excel spreadsheet. O O O Relevant information Required Production January = 120,000 units - cell H2 February = 150,000 units - cell H3 March = 100,000 units - cell H4 o April = 80,000 units (necessary to compute March ending inventory) - cell 45 Pounds of materials needed per unit = 4 pounds-cell H7 Beginning raw materials inventory on January 1, 20XX = 60,000 pounds - cell H8. Percentage of the following month's production used to determine desired ending inventory = 20%-cell H9 Raw materials cost per pound = $2.00 per pound - cell H10 . 2. . PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS (3 POINTS) Prepare the Schedule of Cash Payments for the first three months of Para's fiscal year (January, February, and March) and for the quarter using the template in the Excel spreadsheet. Cost of raw materials to be purchased (the last line of your direct materials budget - cells B13, C13, D13 for January, February and March, respectively). Para's payment schedule: o in the month of purchase = 30%-cell H12 o in the month after purchase = 50%-cell H13 and in the second month after purchase = 20% - cell H14 Beginning accounts payable = $750,000 in cell H16. 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 a heads up as to whether your budget/schedule has errors. January February March Quarter Cost of Raw Materials to be purchased $1,080,000 $1,120,000 $ 768,000 $2,968,000 Total Cash Disbursements for Materials $ 849,000 $1,101,000 $1,006,400 $2,956,400 A B D E F I DIRECT MATERIALS BUDGET AND SCHEDULE OF CASH COLLECTIONS January Production February Production March Production April Production 120,000 150,000 100,000 80,000 February March Quarter 4 DIRECT MATERIALS BUDGET January Required production (units) Pounds of material per unit Pounds of material needed to meet production Add: desired pounds of ending raw materials inventory Total pounds of raw materials needed Less: pounds of beginning raw materials inventory Pounds of raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased 60,000 20% $2 Pounds of raw materials (per unit) Beginning raw materials inventory Desired ending raw materials inventory (9 Cost of raw materials per pound Payment Schedule (%) Month of purchase Month after purchase Second month after purchase 30% 50% 20% February March Quarter SCHEDULE OF EXPECTED CASH DISBURSEMENTS FOR MATERIALS PURCHASES January Beginning Accounts Payable Cash for January Purchases Cash for February Purchases Cash for March Purchases Total Cash Disbursements for materials