master budget modeling project for MyArmor, Inc. - 2021 Planning Year
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 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 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 ProjectManufacturing Overhead Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2021 MyArmor, Inc. Test My Budget Quarter in the 2021 Planning Year Full Year 1st 2nd 3rd 4th Total 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 Calculation of Cash Payments for Manufacturing Overhead - for the Cash Budget Full Year 1st Qtr. 2nd Qtr 3rd Qtr 4th Qtr Total 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 Full Year Total Manufacturing Overhead Cost Restore the Formula in Cell D28 divided by Full Year Total Direct Labor Hours equals Predetermined Overhead Rate per Direct Labor Hour (DLH) $0 per DLH
