Accounting Question

Instructions
Navigator Corp is preparing its Master Budget for 20XX. You need to prepare Navigator’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. Be sure to use formulas throughout your spreadsheet (do not hard-code numbers in).
2. PREPARE A DIRECT MATERIALS BUDGET
Prepare the Direct Materials Budget for the first time three months of Navigators’ 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
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.50 per pound
3. PREPARE A SCHEDULE OF CASH PAYMENTS FOR RAW MATERIALS
Prepare the Schedule of Cash Payments for the first three months of Navigator’s fiscal year
(January, February, and March) and for the quarter, using the template in the Excel spreadsheet.
HINT: Cost of raw materials that Navigator is paying for comes from the last line of your direct
materials budget.
–
Navigator’s payment schedule:
o 20% of purchases are paid for in the month of purchase.
o 65% are paid for in the months after purchase.
o 15% are paid for in the second month after purchase.
–
Beginning accounts payable = $600,000. Assume 80% of the beginning accounts
payable will be paid in January and 20% 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.
Cost of raw materials to be
purchased
Total Cash Disbursements
for Materials
January
$655,000
February
$607,500
March
$945,500
Quarter
$2,207,500
$611,000
$667,250
$682,125
$1,960,375
4. Create an INDEX and MATCH nested function so that the user can enter a month
(‘January’, ‘February’, ‘March’ or ‘Quarter’) and excel returns the amount of
Direct Materials purchase for that month in cell B4 and how much cash is being
spent in C4 on sheet “3) Management Lookup”.
DIRECT MATERIALS BUDGET AND SCHEDULE OF CASH PAYMENTS FOR MATERIALS PURCHASES
1) DIRECT MATERIALS BUDGET
(4 Points)
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
February
March
Quarter
January Production
February Production
March Production
April Production
90,000
70,000
125,000
130,000
Pounds of raw materials (per unit)
Beginning raw materials inventory (pounds)
Desired ending raw materials inventory (pounds)
Cost of raw materials per pound
3
50,000
20%
$2.50
2)
SCHEDULE OF EXPECTED CASH PAYMENTS FOR MATERIALS PURCHASES
January
Beginning Accounts Payable
Cash for January Purchases
Cash for February Purchases
Cash for March Purchases
Total Cash Disbursements for Materials
February
March
(4 POINTS)
A/P paid in January (%)
A/P paid in February (%)
80%
20%
Quarter
Beginning accounts payable
Payment Schedule (%)
Month of purchase
Month after purchase
Second month after purchase
$600,000
20%
65%
15%
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).
3) Create a INDEX and MATCH nested function so that the user can enter a month (‘January’, ‘February’, ‘March’ or ‘Quarte
Month
Quarter
Direct Materials Purchased
Cash Paid for Materials
’February’, ‘March’ or ‘Quarter’) and excel returns the amount Direct Materials purchases for that month in cell B4 and how much cash
ell B4 and how much cash is being spent in C4 (4 POINTS)