Home » Accounting Question

Accounting Question

InstructionsNavigator 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)

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more