Master budget project

How to fix the format of all yellow cells?

Pg. 1 of 2 Inputs Tab Planning Assumptions To begin reviewing the Inputs tab: If your activated project is not already open-open your activated project workbook that you saved in a file named with your Last and First names. This workbook should be located in the Master Budget 6e folder on your Desktop. If prompted with a question concerning Macros, always Enable Macros (Enable Content) Click the Inputs tab To prepare its Master Budget, MyArmor must make various assumptions (forecasts) about the year being planned for in the Master Budget. The Inputs tab contains all of the inputs (assumptions) that MyArmor must make for the Profit Planning Year Jan. 1 to Dec. 31, 2021. As you "Wire-Up" the budgets that are in MyArmor's Master Budget, you will be referencing the cells on this Inputs tab. What will make your "Wired-Up" Master Budget Model such a great planning tool for My Armor's managers, is the fact that a change to any value in one of the yellow input cells will cascade through all the budgets that make up MyArmor's Master Budget model. For example, if Sales in Units in the 1s quarter is increased, that change will be reflected in all budgets and MyArmor's managers will instantly see the affects of an increase in units sold. As you "Wire- Up your model you will become much more aware of just how many items in MyArmor's Master Budget are impacted by changing just one of the cells on this Inputs tab. Before you begin "Wiring-Up" your first budget on the sales tab, observe the following on the Inputs tab: The yellow cells are the cells in which the input values can be changed. These yellow cells have drop-down arrows, that when clicked show the values that are available for that input cell. The values in the drop-down list reflect the range of values that are reasonable for this cell given MyArmor's current business model and operating capacity. For a real company, the values in these input cells would not be limited by drop-down lists, but would rather be limited by the managers' knowledge of their company The gray cells contain items that will be referenced by your Excel formulas as you "Wire-Up" your model, but these values can not be changed. For example, the Prior Year Balance Sheet items (Cash, Accounts Receivable, etc.) at the bottom of the Inputs tab screen are cells that you will reference, but the values in these cells can not be changed. Each input cell (assumption cell) falls in one of the seven time frame columns given across the top of the Inputs tab worksheet. As you "Wire-Up" your model, make sure that you are referencing the correct time frame. The Set Input Values Back to Original Amounts button at the top left of the screen resets all values in the yellow cells back to their original values. It is important for the yellow cells to be set to their original values because the Check Amounts given on each budget tab assume that the yellow cells are set to their original values. You can never hurt anything by clicking this buttonand you will never loose any of your work by clicking this button. If a budget's Check Amounts say ReSet Inputs rather than showing the numeric Check Amount, this means that the Test My Budget process has been run on another budget. To restore the numeric Check Amounts to all budgets in the project, click the Inputs tab and then click the Set Input Values Back to Original Amounts button. Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2020 Applies to All Qtrs. in 2021 1st Quarter Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st 4th of 2022 Sales Budget Sales in Units Average Selling Price per Unit Cash Collection Pattern for Sales in the: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 10,000 units $30 per unit 60% 18,000 units $32 per unit 14.000 units $30 per unit 10% 60% 16.000 units $32 per unit Remainder Remainder 80% 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 12,000 units 20% 30% 40% 20% 10 oz 20% 15.000 oz 130,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.20 per oz 40% $1.10 per oz 30% 60% $1.10 per oz Remainder 20% 70% Remainder Remainder 80% Remainder Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2020 Applies to All Qtrs. Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st Quarter of 2022 in 2021 1st 4th Direct Labor Budget Direct Labor Hours (hrs) per Finished Unit Average Cost of a Direct Labor Hour 0.20 hrs $18 per hour $20 per hour $20 per hour $18 per hour 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 $6 per DLH $50,000 $7,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 $15,000 $45,000 $15,000 $50,000 $40,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 $2,000 $1,000 $0 $12.000 $12,000 $12,000 $0 $13,000 $12,000 $14.000 Cash Budget Equipment Purchases Dividends Annual Interest Rate Charged by the Bank on the Bank Loan $0 $20,000 $0 $20,000 $10,000 $20,000 $15,000 $20,000 6% 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 Pg. 1 of 2 Inputs Tab Planning Assumptions To begin reviewing the Inputs tab: If your activated project is not already open-open your activated project workbook that you saved in a file named with your Last and First names. This workbook should be located in the Master Budget 6e folder on your Desktop. If prompted with a question concerning Macros, always Enable Macros (Enable Content) Click the Inputs tab To prepare its Master Budget, MyArmor must make various assumptions (forecasts) about the year being planned for in the Master Budget. The Inputs tab contains all of the inputs (assumptions) that MyArmor must make for the Profit Planning Year Jan. 1 to Dec. 31, 2021. As you "Wire-Up" the budgets that are in MyArmor's Master Budget, you will be referencing the cells on this Inputs tab. What will make your "Wired-Up" Master Budget Model such a great planning tool for My Armor's managers, is the fact that a change to any value in one of the yellow input cells will cascade through all the budgets that make up MyArmor's Master Budget model. For example, if Sales in Units in the 1s quarter is increased, that change will be reflected in all budgets and MyArmor's managers will instantly see the affects of an increase in units sold. As you "Wire- Up your model you will become much more aware of just how many items in MyArmor's Master Budget are impacted by changing just one of the cells on this Inputs tab. Before you begin "Wiring-Up" your first budget on the sales tab, observe the following on the Inputs tab: The yellow cells are the cells in which the input values can be changed. These yellow cells have drop-down arrows, that when clicked show the values that are available for that input cell. The values in the drop-down list reflect the range of values that are reasonable for this cell given MyArmor's current business model and operating capacity. For a real company, the values in these input cells would not be limited by drop-down lists, but would rather be limited by the managers' knowledge of their company The gray cells contain items that will be referenced by your Excel formulas as you "Wire-Up" your model, but these values can not be changed. For example, the Prior Year Balance Sheet items (Cash, Accounts Receivable, etc.) at the bottom of the Inputs tab screen are cells that you will reference, but the values in these cells can not be changed. Each input cell (assumption cell) falls in one of the seven time frame columns given across the top of the Inputs tab worksheet. As you "Wire-Up" your model, make sure that you are referencing the correct time frame. The Set Input Values Back to Original Amounts button at the top left of the screen resets all values in the yellow cells back to their original values. It is important for the yellow cells to be set to their original values because the Check Amounts given on each budget tab assume that the yellow cells are set to their original values. You can never hurt anything by clicking this buttonand you will never loose any of your work by clicking this button. If a budget's Check Amounts say ReSet Inputs rather than showing the numeric Check Amount, this means that the Test My Budget process has been run on another budget. To restore the numeric Check Amounts to all budgets in the project, click the Inputs tab and then click the Set Input Values Back to Original Amounts button. Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2020 Applies to All Qtrs. in 2021 1st Quarter Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st 4th of 2022 Sales Budget Sales in Units Average Selling Price per Unit Cash Collection Pattern for Sales in the: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 10,000 units $30 per unit 60% 18,000 units $32 per unit 14.000 units $30 per unit 10% 60% 16.000 units $32 per unit Remainder Remainder 80% 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 12,000 units 20% 30% 40% 20% 10 oz 20% 15.000 oz 130,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.20 per oz 40% $1.10 per oz 30% 60% $1.10 per oz Remainder 20% 70% Remainder Remainder 80% Remainder Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2021 My Armor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2020 Applies to All Qtrs. Fix the Format of All Yellow Cells Quarter in the 2021 Planning Year 2nd 3rd 1st Quarter of 2022 in 2021 1st 4th Direct Labor Budget Direct Labor Hours (hrs) per Finished Unit Average Cost of a Direct Labor Hour 0.20 hrs $18 per hour $20 per hour $20 per hour $18 per hour 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 $6 per DLH $50,000 $7,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 $15,000 $45,000 $15,000 $50,000 $40,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 $2,000 $1,000 $0 $12.000 $12,000 $12,000 $0 $13,000 $12,000 $14.000 Cash Budget Equipment Purchases Dividends Annual Interest Rate Charged by the Bank on the Bank Loan $0 $20,000 $0 $20,000 $10,000 $20,000 $15,000 $20,000 6% 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