Solution
Suvrat answered on
Apr 13 2021
Start
2019-20 - Windows
Master Budget Modeling Project First Name: Trevo
for MyArmor, Inc. - 2021 Planning Year Last Name: Wade 4/10/20 Activation Date
ID Code: 85147607 3:49 PM Activation Time
Instructo
Project is Activated Class Time
Seat# or Student ID#
Save your project by following these directions - - - - - - - - - - - - - - - -
As you work on your project, be sure to save your file often.
This workbook contains the starting shell of a Master Budget model for the hypothetical company
MyArmor, Inc. The individual budgets that make up MyArmor's Master Budget are on the Sales
tab through the BalSht tab. You are responsible for completing the Master Budget model by
"wiring-up" the individual budgets. You will "wire-up" the budgets by entering Excel formulas in
all of the white cells on each of the budget tabs (Sales through BalSht).
Every white cell must contain an Excel formula.
Do not enter an actual number in any of the white cells.
Tips for completing your project:
_ An introduction to Master Budgets and to MyArmor, Inc. are in the PDF document titled
01 - Introduction. This PDF document was part of your Master Budget software download.
If you have not already read this document, read it now before you begin your project.
_ Detailed directions for activating this project are in the PDF document titled
02 - Activate Project. This PDF document was part of your Master Budget software download.
If you have not already read this document, read it now before you activate your project.
_ To learn about each of the budgets that make-up a company's Master Budget, and to
complete each budget, follow the detailed directions in each budget's PDF document.
To get started working on your project, read the PDF document 03 - Inputs Tab.
This document was part of your software download.
_ If the way cells are displayed gets changed due to an inco
ect cell copy operation, click the
Fix the Format of All White Cells button to fix the display problems on that tab. You can
never hurt any thing by clicking this button. You will never loose any work by clicking this button.
_ The Check Amounts given on each budget tab assume that the values on the Inputs
tab are set to the original values. To set the Input Amounts back to the original values,
click the Set Input Values Back to Original Amounts button on the Inputs tab.
You will never loose any of your work by clicking this button.
_ Complete the budgets in the sequence given by the tabs; that is, complete the Sales Budget
first and the Balance Sheet last. Before moving to the next budget, be sure that the amounts
in your "wired-up" budget match that budget's Check Amounts.
_ To run an additional test on each completed budget, click the Test My Budget button on each
budget screen. For information about this test, see the section Testing in each budget's
PDF document. You will never loose any of your work by clicking this button.
_ After you have finished "wiring-up" all budgets and comparing your models' amounts to the
Check Amounts -- then Save your model one last time.
_ For information on how to submit your project and how your project will be graded,
read the PDF document 16 - Submitting Your Project.
SAVING YOUR PROJECT -- Immediately after Activation
Save your project in a file that uses your name as the file name. The file name should be in
the form: LastNameFirstName-MB For example, Mary Smith would save her project
with the file name SmithMary-MB
Excel 365, Excel 2019, and Excel 2016 Windows, follow these directions:
- Click File (top left)
- Click Save As (in list on left)
- Click This PC (or click Computer)
- Click C:> Master Budget 5e (center of screen)
- In File name: enter LastNameFirstName-MB
- Save as type: must be Excel Macro-Enabled Workbook
- Click Save
Excel 2010 Windows, follow these directions:
- Click File (top left)
- Click Save As (in list on left)
- In File name: enter LastNameFirstName-MB
- Save as type: must be Excel Macro-Enabled Workbook
- Click Save
Excel 2013 Windows, follow these directions:
- Click File (top left)
- Click Save As (in list on left)
- Click Computer (or click This PC)
- Click Master Budget 5e (under Cu
ent Folder)
- In File name: enter LastNameFirstName-MB
- Save as type: must be Excel Macro-Enabled Workbook
- Click Save
Inputs
Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021
MyArmor, Inc.
As of Applies to
Dec. 31 All Qtrs. Quarter in the 2021 Planning Year 1st Quarte
2020 in 2021 1st 2nd 3rd 4th of 2022
Sales Budget
Sales in Units 14,000 units 12,000 units 18,000 units 18,000 units
Average Selling Price per Unit $30 per unit $28 per unit $34 per unit $34 per unit
Cash Collection Pattern for Sales in the: 1st Quarter 60% 10% Remainde
2nd Quarter 40% Remainde
3rd Quarter 70% Remainde
4th Quarter 80% Remainde
Production Budget
Finished Units in Inventory - as of 12/31/2020 (the prior year) 3,000 units
Forecasted Sales in Units - 1st Quarter of 2022 (next year) 16,000 units
% of the Next Qtr.'s Sales in Units to Have in This Qtr.'s Ending Inventory 30%
Raw Materials Budget
Ounces of PlasticSteel Powder (PsP) Required per Finished Unit 10 oz
% of Next Qtr.'s "Ounces of PsP Required" to Have in This Qtr's Ending Inventory 20% 30% 30% 20%
Ounces of PsP in Raw Materials Inventory - as of 12/31/2020 (the prior year) 15,000 oz
Forecasted "Ounces of PsP Required" in the 1st Qtr of 2022 (next year) 130,000 oz
Cost of PsP per Ounce $1.20 per oz $1.10 per oz $1.10 per oz $1.20 per oz
Cash Payment Pattern for PsP Purchased in the: 1st Quarter 40% 30% Remainde
2nd Quarter 50% 30% Remainde
3rd Quarter 80% Remainde
4th Quarter 70% Remainde
Direct Labor Budget
Direct Labor Hours (hrs) per Finished Unit 0.20 hrs
Average Cost of a Direct Labor Hour $18 per hour $18 per hour $20 per hour $20 per hou
Manufacturing Overhead Budget
Variable Overhead Cost Rate per Direct Labor Hour (DLH) $8 per DLH
Total Fixed Manufacturing Overhead Cost per Quarter $50,000
Depreciation per Quarter Included in Fixed Manufacturing Overhead $9,000
Finished Goods Inventory Budget
No Required Input Data
Cost of Goods Sold Budget
Only Required Input Data is on the Prior Year Balance Sheet below
Selling, General, and Administrative (SG&A) Budget
Variable SG&A Cost per Unit Sold $6 per unit
Advertising Fixed Cost $5,000 $5,000 $15,000 $15,000
Salary Fixed Cost $40,000 $40,000 $45,000 $45,000
Mall Kiosk Rental Fixed Cost $1,000 $1,000 $2,000 $2,000
Utilities Fixed Cost $1,000 $1,000 $2,000 $2,000
Property Taxes $0 $12,000 $0 $12,000
Depreciation $12,000 $12,000 $13,000 $14,000
Cash Budget
Equipment Purchases $0 $0 $10,000 $10,000
Dividends per Quarter $15,000
Annual Interest Rate Charged by the Bank on the Bank Loan 6%
Income Statement
No Required Input Data
Prior Year Balance Sheet as of Dec. 31, 2020
Cash $24,000
Accounts Receivable $125,000
Raw Material Inventory $18,000
Finished Goods Inventory $60,000
Property, Plant & Equipment, gross $723,000
Accumulated Depreciation ($260,000)
Total Assets $690,000
Accounts Payable $28,000
Bank Loan $60,000
Common Stock $160,000
Retained Earnings $442,000
Total Liabilities & Equity $690,000
3
Set Input Values Back
to Original Amounts
Fix the Format of All Yellow Cells
Sales
Sales Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021
MyArmor, Inc.
Quarter in the 2021 Planning Year Full Yea
1st 2nd 3rd 4th Total
Sales in Units 14,000 units 12,000 units 18,000 units 18,000 units 62,000 units
times Average Selling Price per Unit $30 per unit $28 per unit $34 per unit $34 per unit
Total Dollar Sales $420,000 $336,000 $612,000 $612,000 $1,980,000
Check Amount $420,000 Check Amount $1,980,000
Calculation of Cash Collections - for the Cash Budget
1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr.
Collection of Dec. 31, 2020 Accounts Rec. $125,000
Collection of 1st Qtr Sales $252,000 $42,000 $126,000
Collection of 2nd Qtr Sales $134,400 $201,600
Collection of 3rd Qtr Sales $428,400 $183,600
Partial Collection of 4th Qtr Sales $489,600
Total Cash Collections $377,000 $176,400 $756,000 $673,200
Check Amounts $377,000 $176,400 $756,000 $673,200
Calculation of the December 31, 2021 Accounts Receivable - for the Balance Sheet
Total Dollar Sales 4th Quarter (per above) $612,000
less 4th Quarter Sales Already Collected (per above) ( ) ($489,600)
Accounts Receivable as of December 31, 2021 $122,400
Check Amount $122,400
Multiple Choice Questions
1. Which one of the following statements is co
ect? Answer: A
A. All white cells in the Sales Budget, and in the other budgets, must contain Excel formulas.
B. Some white cells in the Sales Budget, and in the other budgets, can be left blank.
C. All white cells in the Sales Budget, and in the other budgets, must contain numbers.
D. None of the above statements are co
ect.
2. Which one of the following statements is co
ect? Answer: B
A. The Sales Budget uses data from the Cash Budget.
B. Sales in Units on the Sales Budget will be an input to the Production Budget.
C. Total Dollar Sales on the Sales Budget will be an input to the Forecasted Balance Sheet.
D. None of the above statements are co
ect.
A
B
C
D
E
F
G
Fix the Format of All White Cells
Test My Budget
Prod
Production Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021
MyArmor, Inc.
Quarter in the 2021 Planning Year Full Yea
All Units are Finished Units (Finished Cell Phone Covers) 1st 2nd 3rd 4th Total Units
Sales in Units (from Sales Budget) 14,000 12,000 18,000 18,000
plus Units Needed in Ending Inventory 3,600 5,400 5,400 4,800
less Units Available from Beginning Inventory ( ) (3,000) (3,600) (5,400) (5,400)
Finished Units to Produce 14,600 13,800 18,000 17,400
Check Amounts 14,600 units 13,800 units 18,000 units 17,400 units
Multiple Choice Questions
1. Using the data in the simple example on Pg. 1 of the Production Budget.pdf document, Answer: B
what would be Finished Units to Produce if Units in Beginning Inventory were 5 rather than 20?
A. 80
B. 90
C. 100
D. 110
2. Which one of the following statements is co
ect? Answer: C
A. Finished Units to Produce on the Production Budget will be an input to the Cash Budget.
B. Total Dollar Sales on the Sales Budget is an input to the Production Budget.
C. Finished Units to Produce on the Production Budget will be an input to the Direct Labor Budget.
D. None of the above statements are co
ect.
A
B
Fix the Format of All White Cells
Test My Budget
RawMat
Raw Materials Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021
MyArmor, Inc.
Quarter in the 2021 Planning Year Full Yea
1st 2nd 3rd 4th Total
Finished Units to Produce (from Production Budget) 14,600 units 13,800 units 18,000 units 17,400 units
times Ounces of PlasticSteel Powder (PsP) Required per Finished Unit 10 oz per unit 10 oz per unit 10 oz per unit 10 oz per unit
Ounces of PsP Required to Produce the Finished Units 146,000 oz 138,000 oz 180,000 oz 174,000 oz
Ounces of PsP Required to Produce the Finished Units 146,000 oz 138,000 oz 180,000 oz 174,000 oz
plus Ounces of PsP Needed in Ending Raw Materials Inventory 27,600 oz 54,000 oz 52,200 oz 26,000 oz
less Ounces of PsP Available from Beginning Raw Materials Inventory ( ) (15,000) oz (27,600) oz (54,000) oz (52,200) oz
Total Ounces of PsP to Purchase 158,600 oz 164,400 oz 178,200 oz 147,800 oz
Total Ounces of PsP to Purchase 158,600 oz 164,400 oz 178,200 oz 147,800 oz
times Cost of PsP per Ounce $1.20 per oz $1.10 per oz $1.10 per oz $1.20 per oz
Total Cost of PsP Purchases $190,320 $180,840 $196,020 $177,360 $744,540
Check Amount $190,320 Check Amount $744,540
Calculation of Cash Payments for Raw Materials - for the Cash Budget
1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr.
Payment of December 31, 2020 Accounts Payable $28,000
Payment of 1st Qtr PsP Purchases $76,128 $57,096 $57,096
Payment of 2nd Qtr PsP Purchases $90,420 $54,252 $36,168
Payment of 3rd Qtr PsP Purchases $156,816 $39,204
Partial Payment of 4th Qtr PsP...