80.2K

Verified Solution

Link Copied!

Master Budget Modeling Project for MyArmor, Inc. - 2021 Planning Year Project is Activated 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 My Armor, Inc. The individual budgets that make up My Armor'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 My Armor, 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 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 incorrect 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. Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 Applies to All Qtrs. Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st Quarter of 2022 2020 in 2021 1st 4th Sales Budget Sales in Units Average Selling Price per Unit Cash Collection Patter for Sales in the 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 14.000 units $32 per unit 60% 16,000 units $34 per unit 10,000 units $28 per unit 10% 60% 18,000 units $34 per unit Remainder 30% 80% Remainder Remainder 70% Remainder 3,000 units Production Budget Finished Units in Inventory - as of 12/31/2020 (the prior year) Forecasted Sales in Units - 1st Quarter of 2022 (next year) % of the Next Qtr.'s Sales in Units to Have in This Qtr.'s Ending Inventory 16,000 units 40% 10 oz 20% 30% 20% 10% 15,000 oz 110,000 oz Raw Materials Budget Ounces of Plastic Steel Powder (PSP) Required per Finished Unit % of Next Qtr.'s "Ounces of PSP Required" to Have in This Qtr's Ending Inventory Ounces of Psp in Raw Materials Inventory - as of 12/31/2020 (the prior year Forecasted "Ounces of Psp Required in the 1st Qtr of 2022 (next year) Cost of Psp per Ounce Cash Payment Pattern for PSP Purchased in the 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter $1.20 per oz $1.10 per oz 40% $1.10 per oz 30% 40% $1.10 per oz Remainder Remainder 70% Remainder 80% Remainder Direct Labor Budget Direct Labor Hours (hrs) per Finished Unit Average cost of a Direct Labor Hour 0.20 hrs $18 per hour $18 per hour $20 per hour $20 per hour Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2020 Applies to All Qtrs. in 2021 Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st Quarter of 2022 1st 4th Manufacturing Overhead Budget Variable Overhead Cost Rate per Direct Labor Hour (DLH) Total Fixed Manufacturing Overhead Cost per Quarter Depreciation per Quarter Included in Fixed Manufacturing Overhead $4 per DLH $60,000 $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 $4 per unit $5,000 $40.000 $5,000 $40,000 $10.000 $45,000 $10,000 $50,000 Selling, General, and Administrative (SG&A) Budget Variable SG&A Cost per Unit Sold Advertising Fixed Cost Salary Fixed Cost Mall Kiosk Rental Fixed Cost per Quarter Utilities Fixed Cost per Quarter Property Taxes Depreciation $1,000 $2,000 $0 $12.000 $14,000 $12,000 $0 $13,000 $14,000 $14,000 Cash Budget Equipment Purchases Dividends Annual Interest Rate Charged by the Bank on the Bank Loan SO $10.000 $0 $10,000 $10,000 $15,000 $15,000 $15,000 8% Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 Applies to All Qtrs. 1st Quarter Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 2020 in 2021 1st 4th of 2022 Income Statement No Required Input Data Prior Year Balance Sheet as of Dec. 31, 2020 Cash Accounts Receivable Raw Material Inventory Finished Goods Inventory Property, Plant & Equipment, gross Accumulated Depreciation Total Assets $24,000 $125,000 $18,000 $60,000 $723,000 ($260,000) $690,000 Accounts Payable Bank Loan Common Stock Retained Earnings Total Liabilities & Equity $28,000 $60,000 $160,000 $442,000 $690,000 Sales Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th A Sales in Units times Average Selling Price per Unit Total Dollar Sales Check Amount $448,000 Check Amount $1,884,000 Fix the Format of All White Cells 3rd Otr 4th Otc E Calculation of Cash Collections - for the Cash Budget D 1st otr. 2nd Otr Collection of Dec. 31, 2020 Accounts Rec. Collection of 1st Qtr Sales Collection of 2nd Qtr Sales Collection of 3rd Qtr Sales Partial Collection of 4th Qtr Sales otal Cash Collection Check Amounts $393,800 $212,800 $708,000 $531,200 Calculation of the December 31, 2021 Accounts Receivable - for the Balance Sheet Total Dollar Sales 4th Quarter (per above) less 4th Quarter Sales Already Collected (per above) Accounts Receivable as of December 31, 2021 Check Amount $163,200 Multiple Choice Questions 1. Which one of the following statements is correct? Answer: A. All white cells in the Sales Budget, and in the other budgets, must contain numbers. B. All white cells in the Sales Budget, and in the other budgets, must contain Excel formulas. C. Some white cells in the Sales Budget, and in the other budgets, can be left blank. D. None of the above statements are correct. 2. Which one of the following statements is correct? Answer: A. Sales in Units on the Sales Budget will be an input to the Production Budget. B. The Sales Budget uses data from the Cash 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 correct. Production Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget | A Quarter in the 2021 Planning Year 2nd 3rd Full Year Total Units 1st 4th All Units are Finished Units (Finished Cell Phone Covers) Sales in Units (from Sales Budget) plus Units Needed in Ending Inventory less Units Available from Beginning Inventory () Finished Units to Produce Check Amounts 15,000 units 13,200 units 17,200 units 16,000 units Fix the Format of All White Cells Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 1 of the Production Budget.pdf document, what would be Finished Units to Produce if Units in Beginning Inventory were 35 rather than 20? A. 110 B. 100 C. 90 D. 80 Answer: 2. Which one of the following statements is correct? A. Total Dollar Sales on the Sales Budget is an input to the Production Budget. B. Finished Units to Produce on the Production Budget will be an input to the Direct Labor Budget. C. Finished Units to Produce on the Production Budget will be an input to the Cash Budget. D. None of the above statements are correct. Raw Materials Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Finished Units to Produce (from Production Budget; times Ounces of Plastic Steel Powder (PsP) Required per Finished Unit Ounces of PSP Required to produce the Finished Units Ounces of PsP Required to produce the Finished Units plus Ounces of PsP Needed in Ending Raw Materials Inventory less Ounces of Psp Available from Beginning Raw Materials Inventory Total Ounces of PsP to Purchase A Total Ounces of PsP to Purchase times Cost of PsP per Ounce Total Cost of Psp Purchases Check Amount $177,540 Check Amount $684,900 Fix the Format of All White Cells 3rd Qtr 4th Qtr. Calculation of Cash Payments for Raw Materials - for the Cash Budget 1st Qtr. 2nd Otr. Payment of December 31, 2020 Accounts Payable Payment of 1st Otr Psp Purchases Payment of 2nd Qtr PSP Purchases Payment of 3rd Otr Psp Purchases Partial Payment of 4th Otr Psp Purchases Total Cash Payments for Psp Check Amounts $99,016 $122,430 $274,362 $183,732 Calculation of December 31, 2021 Accounts Payable - for the Balance Sheet 4th Quarter Total Cost of Psp Purchases less Partial Payment of 4th Qtr Psp Purchases () Accounts Payable as of December 31, 2021 Check Amount $33,360 Calculation of Dec. 31, 2021 Raw Materials Inventory (FIFO) for the Cost of Goods Sold Budget and the Balance Sheet 4th Quarter Ounces of Psp in Ending Raw Materials Inventory times 4th Quarter Cost of Psp per Ounce Raw Materials Inventory as of December 31, 2021 Check Amount $13,200 Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 1 of the Raw Materials Budget.pdf document, what would be Total Cost of Raw Material Purchases if Pounds of Raw Material Needed in Ending Raw Materials Inventory were 20 rather than 25? A. $635 B. $615 C. $625 D. $645 Answer: 2. Which one of the following statements is correct? A. Total Cost of Psp Purchases on the Raw Materials Budget will be an input to the Forecasted Income Statement. B. Total Cost of Psp Purchases on the Raw Materials Budget will be an input to the Cost of Goods Sold Budget. C. Total Cost of Psp Purchases on the Raw Materials Budget will be an input to the Forecasted Balance Sheet. D. None of the above statements are correct. Direct Labor Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Finished Units to Produce (from Production Budget) times Direct Labor Hours per Finished Unit equals Total Direct Labor Hours Total Direct Labor Hours times Average cost of a Direct Labor Hour Total Direct Labor $'s Check Amount $54,000 Check Amount $234,320 Fix the Format of All White Cells Cash Payments for Direct Labor - for the Cash Budget 1st Otr. 2nd Qtr. 3rd Otr. 4th Qtr. Total Cash Payments for Direct Labor My Armor pays all direct labor cost in the period incurred; thus Total Cash Payments = Total Direct Labor $'s Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 1 of the Direct Labor Budget.pdf document, what would be Total Direct Labor $'s if Finished Units to Produce were 120 rather than 100? A. $400 B. $500 C. $700 D. $600 Answer: 2. Which one of the following statements is correct? A. Total Direct Labor $'s is an input to the Manufacturing Overhead Budget. B. Total Direct Labor Hours is an input to the Cost of Goods Sold Budget. C. The Direct Labor Budget is more difficult than the Raw Materials Budget. D. None of the above statements are correct. Manufacturing Overhead Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Total Direct Labor Hours (from Direct Labor Budget) times Variable Overhead Cost Rate per Direct Labor Hour (DLH) equals Total Variable Manufacturing Overhead Cost plus Total Fixed Manufacturing Overhead Cost Total Manufacturing Overhead Cost Check Amount $72,000 Check Amount $289,120 Full Year Total 3rd Oc. 4th Otr Calculation of Cash Payments for Manufacturing Overhead - for the Cash Budget 1st Qtr. 2nd Qtc Total Manufacturing Overhead Cost (per above) less Depreciation Included in Manufacturing Overhead() Total Cash Payments for Manufacturing Overhead Check Amounts $63,000 $61,560 $64,760 $63,800 Calculation of the Predetermined Overhead Rate - for the Finished Goods Ending Inventory Budget Fix the Format of All White Cells and Restore the Formula in Cell D28 Full Year Total Manufacturing Overhead Cost divided by Full Year Total Direct Labor Hours equals Predetermined Overhead Rate per Direct Labor Hour (DLH) Check Amount $0 per DLH $24 Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 3 of the Manufacturing Overhead Budget.pdf document, what would be Total Manufacturing Overhead Cost if Total Direct Labor Hours were 300 rather than 100? A. $650 B. $450 C. $600 D. $400 Answer: 2. Which one of the following statements is correct? A. Total Fixed Overhead Cost does not change when the level of manufacturing activity changes. B. Total Cash Payments for Manufacturing Overhead is an input to the Cost of Goods Sold Budget. C. Total Manufacturing Overhead Cost is an input to the Cash Budget. D. Total Variable Overhead Cost does not change when the level of manufacturing activity changes. Finished Goods Inventory Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Total Cost per Finished Unit Calculation of 4th Quarter Production Cost per Finished Unit Quantity Required A per Finished Unit 4th otr. Cost PSP (Raw Materials) Direct Labor Manufacturing Overhead (applied based on Direct Labor Hours) 4th Qtr. Production Cost per Finished Unit Check Amount $19.80 Calculation of Finished Goods Inventory (FIFO) as of Dec. 31, 2021 Units in 4th Qtr. Finished Goods Ending Inventory (from Production Budget) times 4th Qtr. Production Cost per Finished Unit Finished Goods Inventory as of Dec. 31, 2021 Check Amount + $126,720 Fix the Format of All White Cells 2 Incorrect Check Amounts Blank Multiple Choice Question(s) Multiple Choice Questions 1. Using the data in the simple example on Pg. 1 of the Finished Goods Inventory Budget.pdf Answer: document, what would be the value of the 100 Finished Units if 4th qtr. cost of an hour of Direct Labor was $12 rather than $10? A. $8,400 B. $7,600 C. $6,800 D. $6,000 Answer: 2. Which one of the following statements is correct? A. Finished Goods Inventory as of Dec. 31, 2021 is an input to the Cash Budget. B. Finished Goods Inventory as of Dec. 31, 2021 is an input to the Raw Materials Budget. C. Finished Goods Inventory as of Dec. 31, 2021 is an input to the Cost of Goods Sold Budget. D. None of the above statements are correct. Cost of Goods Sold Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Raw Materials Inventory Raw Materials Inventory as of January 1, 2021 plus Total Cost of Psp Purchases for 2021 (from Raw Materials Budget) less Raw Materials Inventory as of December 31, 2021 (from Raw Materials Budget) () equals Cost of Raw Materials Transferred to Work-in-Process Inventory Work-in-Process Inventory (WIP) $0 Fix the Format of All White Cells and Restore the Zeros in Cells D14 and D18 Work-in-Process Inventory as of January 1, 2021 plus Cost of Raw Materials Transferred to Work-in-Process Inventory plus Total Direct Labor $'s for 2021 (from Direct Labor Budget) plus Total Manufacturing Overhead Cost for 2021 (from Manufacturing Overhead Budget) less Work-in-Process Inventory as of December 31, 2021() equals Cost of Work-in-Process Transferred to Finished Goods Inventory (0) Finished Goods Inventory and Cost of Goods Sold Finished Goods Inventory as of January 1, 2021 plus Cost of Work-in-Process Transferred to Finished Goods Inventory less Finished Goods Inventory as of Dec. 31, 2021 (from Finished Goods Inventory Budget) Cost of Goods Sold Check Amount $1,102,660 1 Incorrect Check Amount Blank Multiple Choice Question(s) Answer: Multiple Choice Questions 1. Using the data in the simple example on Pg. 1 of the Cost of Goods Sold Budget.pdf document, what would be cost of Goods Sold if Raw Materials Inventory as of December 31, 2021 was $15 rather than $20? A. $1,005 B. $1,000 C. $995 D. $990 Answer: 2. Which one of the following statements is correct? A. Cost of Goods Sold, on the Cost of Goods Sold Budget, is an input to the Cash Budget. B. Cost of Goods Sold, on the Cost of Goods Sold Budget, is an input to the Forecasted Income Statement C. Cost of Goods Sold, on the Cost of Goods Sold Budget, is an input to the Forecasted Balance Sheet. D. None of the above statements are correct. Selling, General, and Administrative (SG&A) Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th SG&A Variable Cost Sales in Units (from Sales Budget) times Variable SG&A Cost per Unit Sold Total SG&A Variable Cost SG&A Fixed Costs Advertising Cost Salary Cost Mall Kiosk Rental Cost Utilities Cost Property Taxes Depreciation Total SG&A Fixed Cost Total SG&A Cost Check Amounts $108,000 Fix the Format of All White Cells $163,000 4 Incorrect Check Amounts Blank Multiple Choice Question(s) Full Year 3rd Qtr. 4th Otr Total Calculation of Cash Payments - for the Cash Budget 1st Otr 2nd Otc Total SG&A Cost (per above) less Depreciation Included in Total SG&A Cost() Total Cash Payments for SG&A Check Amounts $110,000 $114,000 Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 2 of the SG&A Budget.pdf document, what would be Total SG&A Cost if Variable SG&A Cost per Unit Sold was $4 rather than $3? A. $450 B. $350 C. $250 D. $150 Answer 2. Which one of the following statements is correct? A. Total SG&A Fixed Cost changes based on the level of activity. B. Total SG&A Variable Cost changes based on the level of activity. C. Total SG&A Cost is an input to the Cash Budget. D. None of the above statements are correct. Cash Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget IMPORTANT Before using the Check Amounts, you must complete all 4 quarters of the Cash Budget and the Loan Schedule located below the Cash Budget. Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Beginning Cash Balance plus Cash Collections (from Sales Budget) less Payments for PSP (Raw Materials) (from Raw Materials Budget) () less Payments for Direct Labor (from Direct Labor Budget) () less Payments for Manufacturing Overhead (from Manufacturing OH Budget) less Payments for SG&A (from SG&A Budget) () less Payments for Equipment Purchases () less Payments for Dividends () Excess Cash (Deficiency of Cash) before Borrowings, Repayments, Interest plus Additional Borrowings from the Bank during the quarter 0 0 0 A less Repayments Made to the Bank during the quarter ( 0 0 0 B Cash Balance before paying Interest less Interest Paid on the Bank Loan at the End of the Quarter() 0 0 Ending Cash Balance $23,177 Check Amounts $20,263 See IMPORTANT box above 3 Incorrect Check Amounts Fix the Format of All White Cells and Restore the Formulas in the Gray Cells Blank Multiple Choice Question(s) Loan Schedule -- Calculation of the Bank Loan Balance for the Interest Calculation and for the Balance Sheet 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Beginning Loan Balance Before Additional Borrowings and Repayments plus Additional Borrowings less Repayments () Ending Loan Balance After Additional Borrowings and Repayments Check Amount $174,817 Multiple Choice Questions Answer: 1. Using the 1st Qtr. data in the simple example on Pg. 2 of the Cash Budget.pdf document, what would be 1st Qtr. Interest Paid on the Bank Loan if 1st Qtr. Cash Collections were $100 rather than $500? A. $12 B. $14 C. $16 D. $18 Answer: 2. Which one of the following statements is correct? A. Ending Cash Balance on the Cash Budget is an input to the Sales Budget. B. Ending Cash Balance on the Cash Budget is an input to the SG&A Budget. C. Ending Cash Balance on the Cash Budget is an input to the Forecasted Income Statement. D. None of the above statements are correct. Forecasted Income Statement for the Profit Planning Year Ending December 31, 2021 MyArmor, Inc. Test My Budget 2021 Dollars % v 0.00% Sales (from Sales Budget) less Cost of Goods Sold (from Cost of Goods Sold Budget) () Gross Profit 0.00% 0.00% less SG&A Cost (from SG&A Budget) () 0.00% Operating Income 0.00% less Interest Expense (from Cash Budget) () 0.00% 0.00% Net Income (Net Loss) Check Amount ($98,626) Fix the Format of All White Cells 1 Incorrect Check Amount Forecasted Balance Sheet for the Profit Planning Year Ending December 31, 2021 My Armor, Inc. Test My Budget IMPORTANI Before "Wiring-Up" the Balance Sheet, complete the PP&E, Accumulated Depreciation, and Retained Earnings schedules located below the Balance Sheet as of December 31, 2021 Dollars % 0.00% 0.00% 0.00% Assets Current Assets Cash (from Cash Budget) Accounts Receivable (from Sales Budget) Raw Materials Inventory (FIFO) (from Raw Materials Budget) Finished Goods Inventory (FIFO) (from Finished Goods Budget) Total Current Assets Property, Plant, and Equipment, gross (from schedule below) less Accumulated Depreciation () (from schedule below) Property, Plant, and Equipment, net 0.00% 0.00% 0.00% 0.00% 0.00% Total Assets 0.00% Check Amount $660,883 Liabilities and Stockholders' Equity Current Liabilities Accounts Payable (from Raw Materials Budget) Bank Loan (from Cash Budget) Total Current Liabilities Stockholders' Equity Common Stock Retained Earnings (from schedule below) Total Stockholders' Equity 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Total Liabilities and Stockholders' Equity 0.00% Check Amount $660,883 2 Incorrect Check Amounts Fix the Format of All White Cells 2 Incorrect Check Amounts Fix the Format of All White Cells Amounts (not calculated on other Budgets) Needed for the Balance Sheet Property. Plant, and Equipment, gross Property, Plant, and Equipment, gross as of Dec. 31, 2020 plus Equipment Purchases - Full Year Total (from Cash Budget) Property, Plant, and Equipment, gross as of Dec. 31, 2021 Accumulated Depreciation Accumulated Depreciation as of Dec. 31, 2020 () add Depreciation - Full Year Total (from Manufacturing Overhead Budget) () add Depreciation - Full Year Total (from SG&A Budget) () Accumulated Depreciation as of Dec. 31, 2021 () Retained Earnings Retained Earnings as of Dec. 31, 2020 plus Net Income (Net Loss) (from Income Statement) less Dividends - Full Year Total (from Cash Budget) () Retained Earnings as of Dec. 31, 2021 Master Budget Modeling Project for MyArmor, Inc. - 2021 Planning Year Project is Activated 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 My Armor, Inc. The individual budgets that make up My Armor'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 My Armor, 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 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 incorrect 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. Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 Applies to All Qtrs. Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st Quarter of 2022 2020 in 2021 1st 4th Sales Budget Sales in Units Average Selling Price per Unit Cash Collection Patter for Sales in the 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 14.000 units $32 per unit 60% 16,000 units $34 per unit 10,000 units $28 per unit 10% 60% 18,000 units $34 per unit Remainder 30% 80% Remainder Remainder 70% Remainder 3,000 units Production Budget Finished Units in Inventory - as of 12/31/2020 (the prior year) Forecasted Sales in Units - 1st Quarter of 2022 (next year) % of the Next Qtr.'s Sales in Units to Have in This Qtr.'s Ending Inventory 16,000 units 40% 10 oz 20% 30% 20% 10% 15,000 oz 110,000 oz Raw Materials Budget Ounces of Plastic Steel Powder (PSP) Required per Finished Unit % of Next Qtr.'s "Ounces of PSP Required" to Have in This Qtr's Ending Inventory Ounces of Psp in Raw Materials Inventory - as of 12/31/2020 (the prior year Forecasted "Ounces of Psp Required in the 1st Qtr of 2022 (next year) Cost of Psp per Ounce Cash Payment Pattern for PSP Purchased in the 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter $1.20 per oz $1.10 per oz 40% $1.10 per oz 30% 40% $1.10 per oz Remainder Remainder 70% Remainder 80% Remainder Direct Labor Budget Direct Labor Hours (hrs) per Finished Unit Average cost of a Direct Labor Hour 0.20 hrs $18 per hour $18 per hour $20 per hour $20 per hour Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2020 Applies to All Qtrs. in 2021 Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st Quarter of 2022 1st 4th Manufacturing Overhead Budget Variable Overhead Cost Rate per Direct Labor Hour (DLH) Total Fixed Manufacturing Overhead Cost per Quarter Depreciation per Quarter Included in Fixed Manufacturing Overhead $4 per DLH $60,000 $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 $4 per unit $5,000 $40.000 $5,000 $40,000 $10.000 $45,000 $10,000 $50,000 Selling, General, and Administrative (SG&A) Budget Variable SG&A Cost per Unit Sold Advertising Fixed Cost Salary Fixed Cost Mall Kiosk Rental Fixed Cost per Quarter Utilities Fixed Cost per Quarter Property Taxes Depreciation $1,000 $2,000 $0 $12.000 $14,000 $12,000 $0 $13,000 $14,000 $14,000 Cash Budget Equipment Purchases Dividends Annual Interest Rate Charged by the Bank on the Bank Loan SO $10.000 $0 $10,000 $10,000 $15,000 $15,000 $15,000 8% Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 Applies to All Qtrs. 1st Quarter Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 2020 in 2021 1st 4th of 2022 Income Statement No Required Input Data Prior Year Balance Sheet as of Dec. 31, 2020 Cash Accounts Receivable Raw Material Inventory Finished Goods Inventory Property, Plant & Equipment, gross Accumulated Depreciation Total Assets $24,000 $125,000 $18,000 $60,000 $723,000 ($260,000) $690,000 Accounts Payable Bank Loan Common Stock Retained Earnings Total Liabilities & Equity $28,000 $60,000 $160,000 $442,000 $690,000 Sales Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th A Sales in Units times Average Selling Price per Unit Total Dollar Sales Check Amount $448,000 Check Amount $1,884,000 Fix the Format of All White Cells 3rd Otr 4th Otc E Calculation of Cash Collections - for the Cash Budget D 1st otr. 2nd Otr Collection of Dec. 31, 2020 Accounts Rec. Collection of 1st Qtr Sales Collection of 2nd Qtr Sales Collection of 3rd Qtr Sales Partial Collection of 4th Qtr Sales otal Cash Collection Check Amounts $393,800 $212,800 $708,000 $531,200 Calculation of the December 31, 2021 Accounts Receivable - for the Balance Sheet Total Dollar Sales 4th Quarter (per above) less 4th Quarter Sales Already Collected (per above) Accounts Receivable as of December 31, 2021 Check Amount $163,200 Multiple Choice Questions 1. Which one of the following statements is correct? Answer: A. All white cells in the Sales Budget, and in the other budgets, must contain numbers. B. All white cells in the Sales Budget, and in the other budgets, must contain Excel formulas. C. Some white cells in the Sales Budget, and in the other budgets, can be left blank. D. None of the above statements are correct. 2. Which one of the following statements is correct? Answer: A. Sales in Units on the Sales Budget will be an input to the Production Budget. B. The Sales Budget uses data from the Cash 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 correct. Production Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget | A Quarter in the 2021 Planning Year 2nd 3rd Full Year Total Units 1st 4th All Units are Finished Units (Finished Cell Phone Covers) Sales in Units (from Sales Budget) plus Units Needed in Ending Inventory less Units Available from Beginning Inventory () Finished Units to Produce Check Amounts 15,000 units 13,200 units 17,200 units 16,000 units Fix the Format of All White Cells Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 1 of the Production Budget.pdf document, what would be Finished Units to Produce if Units in Beginning Inventory were 35 rather than 20? A. 110 B. 100 C. 90 D. 80 Answer: 2. Which one of the following statements is correct? A. Total Dollar Sales on the Sales Budget is an input to the Production Budget. B. Finished Units to Produce on the Production Budget will be an input to the Direct Labor Budget. C. Finished Units to Produce on the Production Budget will be an input to the Cash Budget. D. None of the above statements are correct. Raw Materials Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Finished Units to Produce (from Production Budget; times Ounces of Plastic Steel Powder (PsP) Required per Finished Unit Ounces of PSP Required to produce the Finished Units Ounces of PsP Required to produce the Finished Units plus Ounces of PsP Needed in Ending Raw Materials Inventory less Ounces of Psp Available from Beginning Raw Materials Inventory Total Ounces of PsP to Purchase A Total Ounces of PsP to Purchase times Cost of PsP per Ounce Total Cost of Psp Purchases Check Amount $177,540 Check Amount $684,900 Fix the Format of All White Cells 3rd Qtr 4th Qtr. Calculation of Cash Payments for Raw Materials - for the Cash Budget 1st Qtr. 2nd Otr. Payment of December 31, 2020 Accounts Payable Payment of 1st Otr Psp Purchases Payment of 2nd Qtr PSP Purchases Payment of 3rd Otr Psp Purchases Partial Payment of 4th Otr Psp Purchases Total Cash Payments for Psp Check Amounts $99,016 $122,430 $274,362 $183,732 Calculation of December 31, 2021 Accounts Payable - for the Balance Sheet 4th Quarter Total Cost of Psp Purchases less Partial Payment of 4th Qtr Psp Purchases () Accounts Payable as of December 31, 2021 Check Amount $33,360 Calculation of Dec. 31, 2021 Raw Materials Inventory (FIFO) for the Cost of Goods Sold Budget and the Balance Sheet 4th Quarter Ounces of Psp in Ending Raw Materials Inventory times 4th Quarter Cost of Psp per Ounce Raw Materials Inventory as of December 31, 2021 Check Amount $13,200 Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 1 of the Raw Materials Budget.pdf document, what would be Total Cost of Raw Material Purchases if Pounds of Raw Material Needed in Ending Raw Materials Inventory were 20 rather than 25? A. $635 B. $615 C. $625 D. $645 Answer: 2. Which one of the following statements is correct? A. Total Cost of Psp Purchases on the Raw Materials Budget will be an input to the Forecasted Income Statement. B. Total Cost of Psp Purchases on the Raw Materials Budget will be an input to the Cost of Goods Sold Budget. C. Total Cost of Psp Purchases on the Raw Materials Budget will be an input to the Forecasted Balance Sheet. D. None of the above statements are correct. Direct Labor Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Finished Units to Produce (from Production Budget) times Direct Labor Hours per Finished Unit equals Total Direct Labor Hours Total Direct Labor Hours times Average cost of a Direct Labor Hour Total Direct Labor $'s Check Amount $54,000 Check Amount $234,320 Fix the Format of All White Cells Cash Payments for Direct Labor - for the Cash Budget 1st Otr. 2nd Qtr. 3rd Otr. 4th Qtr. Total Cash Payments for Direct Labor My Armor pays all direct labor cost in the period incurred; thus Total Cash Payments = Total Direct Labor $'s Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 1 of the Direct Labor Budget.pdf document, what would be Total Direct Labor $'s if Finished Units to Produce were 120 rather than 100? A. $400 B. $500 C. $700 D. $600 Answer: 2. Which one of the following statements is correct? A. Total Direct Labor $'s is an input to the Manufacturing Overhead Budget. B. Total Direct Labor Hours is an input to the Cost of Goods Sold Budget. C. The Direct Labor Budget is more difficult than the Raw Materials Budget. D. None of the above statements are correct. Manufacturing Overhead Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Total Direct Labor Hours (from Direct Labor Budget) times Variable Overhead Cost Rate per Direct Labor Hour (DLH) equals Total Variable Manufacturing Overhead Cost plus Total Fixed Manufacturing Overhead Cost Total Manufacturing Overhead Cost Check Amount $72,000 Check Amount $289,120 Full Year Total 3rd Oc. 4th Otr Calculation of Cash Payments for Manufacturing Overhead - for the Cash Budget 1st Qtr. 2nd Qtc Total Manufacturing Overhead Cost (per above) less Depreciation Included in Manufacturing Overhead() Total Cash Payments for Manufacturing Overhead Check Amounts $63,000 $61,560 $64,760 $63,800 Calculation of the Predetermined Overhead Rate - for the Finished Goods Ending Inventory Budget Fix the Format of All White Cells and Restore the Formula in Cell D28 Full Year Total Manufacturing Overhead Cost divided by Full Year Total Direct Labor Hours equals Predetermined Overhead Rate per Direct Labor Hour (DLH) Check Amount $0 per DLH $24 Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 3 of the Manufacturing Overhead Budget.pdf document, what would be Total Manufacturing Overhead Cost if Total Direct Labor Hours were 300 rather than 100? A. $650 B. $450 C. $600 D. $400 Answer: 2. Which one of the following statements is correct? A. Total Fixed Overhead Cost does not change when the level of manufacturing activity changes. B. Total Cash Payments for Manufacturing Overhead is an input to the Cost of Goods Sold Budget. C. Total Manufacturing Overhead Cost is an input to the Cash Budget. D. Total Variable Overhead Cost does not change when the level of manufacturing activity changes. Finished Goods Inventory Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Total Cost per Finished Unit Calculation of 4th Quarter Production Cost per Finished Unit Quantity Required A per Finished Unit 4th otr. Cost PSP (Raw Materials) Direct Labor Manufacturing Overhead (applied based on Direct Labor Hours) 4th Qtr. Production Cost per Finished Unit Check Amount $19.80 Calculation of Finished Goods Inventory (FIFO) as of Dec. 31, 2021 Units in 4th Qtr. Finished Goods Ending Inventory (from Production Budget) times 4th Qtr. Production Cost per Finished Unit Finished Goods Inventory as of Dec. 31, 2021 Check Amount + $126,720 Fix the Format of All White Cells 2 Incorrect Check Amounts Blank Multiple Choice Question(s) Multiple Choice Questions 1. Using the data in the simple example on Pg. 1 of the Finished Goods Inventory Budget.pdf Answer: document, what would be the value of the 100 Finished Units if 4th qtr. cost of an hour of Direct Labor was $12 rather than $10? A. $8,400 B. $7,600 C. $6,800 D. $6,000 Answer: 2. Which one of the following statements is correct? A. Finished Goods Inventory as of Dec. 31, 2021 is an input to the Cash Budget. B. Finished Goods Inventory as of Dec. 31, 2021 is an input to the Raw Materials Budget. C. Finished Goods Inventory as of Dec. 31, 2021 is an input to the Cost of Goods Sold Budget. D. None of the above statements are correct. Cost of Goods Sold Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Raw Materials Inventory Raw Materials Inventory as of January 1, 2021 plus Total Cost of Psp Purchases for 2021 (from Raw Materials Budget) less Raw Materials Inventory as of December 31, 2021 (from Raw Materials Budget) () equals Cost of Raw Materials Transferred to Work-in-Process Inventory Work-in-Process Inventory (WIP) $0 Fix the Format of All White Cells and Restore the Zeros in Cells D14 and D18 Work-in-Process Inventory as of January 1, 2021 plus Cost of Raw Materials Transferred to Work-in-Process Inventory plus Total Direct Labor $'s for 2021 (from Direct Labor Budget) plus Total Manufacturing Overhead Cost for 2021 (from Manufacturing Overhead Budget) less Work-in-Process Inventory as of December 31, 2021() equals Cost of Work-in-Process Transferred to Finished Goods Inventory (0) Finished Goods Inventory and Cost of Goods Sold Finished Goods Inventory as of January 1, 2021 plus Cost of Work-in-Process Transferred to Finished Goods Inventory less Finished Goods Inventory as of Dec. 31, 2021 (from Finished Goods Inventory Budget) Cost of Goods Sold Check Amount $1,102,660 1 Incorrect Check Amount Blank Multiple Choice Question(s) Answer: Multiple Choice Questions 1. Using the data in the simple example on Pg. 1 of the Cost of Goods Sold Budget.pdf document, what would be cost of Goods Sold if Raw Materials Inventory as of December 31, 2021 was $15 rather than $20? A. $1,005 B. $1,000 C. $995 D. $990 Answer: 2. Which one of the following statements is correct? A. Cost of Goods Sold, on the Cost of Goods Sold Budget, is an input to the Cash Budget. B. Cost of Goods Sold, on the Cost of Goods Sold Budget, is an input to the Forecasted Income Statement C. Cost of Goods Sold, on the Cost of Goods Sold Budget, is an input to the Forecasted Balance Sheet. D. None of the above statements are correct. Selling, General, and Administrative (SG&A) Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Test My Budget Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th SG&A Variable Cost Sales in Units (from Sales Budget) times Variable SG&A Cost per Unit Sold Total SG&A Variable Cost SG&A Fixed Costs Advertising Cost Salary Cost Mall Kiosk Rental Cost Utilities Cost Property Taxes Depreciation Total SG&A Fixed Cost Total SG&A Cost Check Amounts $108,000 Fix the Format of All White Cells $163,000 4 Incorrect Check Amounts Blank Multiple Choice Question(s) Full Year 3rd Qtr. 4th Otr Total Calculation of Cash Payments - for the Cash Budget 1st Otr 2nd Otc Total SG&A Cost (per above) less Depreciation Included in Total SG&A Cost() Total Cash Payments for SG&A Check Amounts $110,000 $114,000 Multiple Choice Questions Answer: 1. Using the data in the simple example on Pg. 2 of the SG&A Budget.pdf document, what would be Total SG&A Cost if Variable SG&A Cost per Unit Sold was $4 rather than $3? A. $450 B. $350 C. $250 D. $150 Answer 2. Which one of the following statements is correct? A. Total SG&A Fixed Cost changes based on the level of activity. B. Total SG&A Variable Cost changes based on the level of activity. C. Total SG&A Cost is an input to the Cash Budget. D. None of the above statements are correct. Cash Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget IMPORTANT Before using the Check Amounts, you must complete all 4 quarters of the Cash Budget and the Loan Schedule located below the Cash Budget. Quarter in the 2021 Planning Year 2nd 3rd Full Year Total 1st 4th Beginning Cash Balance plus Cash Collections (from Sales Budget) less Payments for PSP (Raw Materials) (from Raw Materials Budget) () less Payments for Direct Labor (from Direct Labor Budget) () less Payments for Manufacturing Overhead (from Manufacturing OH Budget) less Payments for SG&A (from SG&A Budget) () less Payments for Equipment Purchases () less Payments for Dividends () Excess Cash (Deficiency of Cash) before Borrowings, Repayments, Interest plus Additional Borrowings from the Bank during the quarter 0 0 0 A less Repayments Made to the Bank during the quarter ( 0 0 0 B Cash Balance before paying Interest less Interest Paid on the Bank Loan at the End of the Quarter() 0 0 Ending Cash Balance $23,177 Check Amounts $20,263 See IMPORTANT box above 3 Incorrect Check Amounts Fix the Format of All White Cells and Restore the Formulas in the Gray Cells Blank Multiple Choice Question(s) Loan Schedule -- Calculation of the Bank Loan Balance for the Interest Calculation and for the Balance Sheet 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Beginning Loan Balance Before Additional Borrowings and Repayments plus Additional Borrowings less Repayments () Ending Loan Balance After Additional Borrowings and Repayments Check Amount $174,817 Multiple Choice Questions Answer: 1. Using the 1st Qtr. data in the simple example on Pg. 2 of the Cash Budget.pdf document, what would be 1st Qtr. Interest Paid on the Bank Loan if 1st Qtr. Cash Collections were $100 rather than $500? A. $12 B. $14 C. $16 D. $18 Answer: 2. Which one of the following statements is correct? A. Ending Cash Balance on the Cash Budget is an input to the Sales Budget. B. Ending Cash Balance on the Cash Budget is an input to the SG&A Budget. C. Ending Cash Balance on the Cash Budget is an input to the Forecasted Income Statement. D. None of the above statements are correct. Forecasted Income Statement for the Profit Planning Year Ending December 31, 2021 MyArmor, Inc. Test My Budget 2021 Dollars % v 0.00% Sales (from Sales Budget) less Cost of Goods Sold (from Cost of Goods Sold Budget) () Gross Profit 0.00% 0.00% less SG&A Cost (from SG&A Budget) () 0.00% Operating Income 0.00% less Interest Expense (from Cash Budget) () 0.00% 0.00% Net Income (Net Loss) Check Amount ($98,626) Fix the Format of All White Cells 1 Incorrect Check Amount Forecasted Balance Sheet for the Profit Planning Year Ending December 31, 2021 My Armor, Inc. Test My Budget IMPORTANI Before "Wiring-Up" the Balance Sheet, complete the PP&E, Accumulated Depreciation, and Retained Earnings schedules located below the Balance Sheet as of December 31, 2021 Dollars % 0.00% 0.00% 0.00% Assets Current Assets Cash (from Cash Budget) Accounts Receivable (from Sales Budget) Raw Materials Inventory (FIFO) (from Raw Materials Budget) Finished Goods Inventory (FIFO) (from Finished Goods Budget) Total Current Assets Property, Plant, and Equipment, gross (from schedule below) less Accumulated Depreciation () (from schedule below) Property, Plant, and Equipment, net 0.00% 0.00% 0.00% 0.00% 0.00% Total Assets 0.00% Check Amount $660,883 Liabilities and Stockholders' Equity Current Liabilities Accounts Payable (from Raw Materials Budget) Bank Loan (from Cash Budget) Total Current Liabilities Stockholders' Equity Common Stock Retained Earnings (from schedule below) Total Stockholders' Equity 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Total Liabilities and Stockholders' Equity 0.00% Check Amount $660,883 2 Incorrect Check Amounts Fix the Format of All White Cells 2 Incorrect Check Amounts Fix the Format of All White Cells Amounts (not calculated on other Budgets) Needed for the Balance Sheet Property. Plant, and Equipment, gross Property, Plant, and Equipment, gross as of Dec. 31, 2020 plus Equipment Purchases - Full Year Total (from Cash Budget) Property, Plant, and Equipment, gross as of Dec. 31, 2021 Accumulated Depreciation Accumulated Depreciation as of Dec. 31, 2020 () add Depreciation - Full Year Total (from Manufacturing Overhead Budget) () add Depreciation - Full Year Total (from SG&A Budget) () Accumulated Depreciation as of Dec. 31, 2021 () Retained Earnings Retained Earnings as of Dec. 31, 2020 plus Net Income (Net Loss) (from Income Statement) less Dividends - Full Year Total (from Cash Budget) () Retained Earnings as of Dec. 31, 2021

Answer & Explanation Solved by verified expert

See Answer