Guidance Corp is preparing its Master Budget for $20$XX$.$ 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 $(7$ 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

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,20$XX $=50,000$ pounds.

$$ Percentage of the following month$$s production used to determine desired ending

inventory $=20\%$

$$ Raw materials cost per pound $=$ $$2\mathrm{.}5$ per pound

$2.$ PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS $(8$ 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

o $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.

Check Figures

Because this problem has several 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 $$655,000$ $$607,500$ $$945,000$ $$2,207,500$

Total Cash Disbursements for Materials $$581,00$ $$697,250$ $$682,125$ $$1,960,375$