Using the PDF File, the Excel sheet needs to be completed.

Master Budget Modeling Project for MyArmor, Inc. - 2017 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: _ Information about Master Budgets, and detailed directions for completing MyArmor's Master Budget model, are in the PDF file titled Master Budgeting. You received this file when you received your Software ID Code. _ Before you begin your project: Read the section Introduction to Master Budgets in the Master Budgeting.pdf file. Read the section Introduction to MyArmor, Inc. in the Master Budgeting.pdf file. _ To learn about each of the budgets that make-up a company's Master Budget, and to complete your Master Budget Modeling Project, follow the detailed directions in the section Understanding Master Budgets by Completing Your Master Budget Modeling Project in the Master Budgeting.pdf file. _ If the way cells are displayed gets changed due to an incorrect cell copy operation, click the Fix How Cells Display button to fix the display problems on that tab. You can never hurt any thing by clicking this button. You will never loose any of your 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 (if you have changed any of the Input Amounts), 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 your completed model, click the Test My Model button on the Inputs tab. For information about this test, see the section Final Test of Your Completed Model in the Master Budgeting.pdf file. 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 to your Instructor, see the section Submitting Your Project -- How Your Project Will be Graded in the Master Budgeting.pdf file. Inputs for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. As of Applies to Dec. 31 All Qtrs. 2016 in 2017 1st Quarter in the 2017 Planning Year 2nd 3rd 1st Quarter 4th of 2018 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 14,000 units $28 per unit 60% 12,000 units $28 per unit 10% 50% 18,000 units $34 per unit Remainder 20% 70% 18,000 units $34 per unit Remainder Remainder 80% Remainder Production Budget Units in Finished Goods Inventory - as of 12/31/16 (the prior year) Forecasted Sales in Units - 1st Quarter of 2018 (next year) % of the Next Qtr.'s Unit Sales to Have in This Qtr.'s Ending Inventory 3,000 units 14,000 units 20% 30% 30% 20% Raw Materials Budget Ounces (oz) of PlasticSteel Powder (PsP) Needed per Finished Unit % of Next Qtr.'s "Total Oz of PsP Needed" to Have in This Qtr.'s Ending Inventory Ounces of PsP in Raw Materials Inventory - as of 12/31/16 (the prior year) Forecasted "Total Oz of PsP Needed" in the 1st Qtr of 2018 (next year) Cost of PsP per Ounce Cash Payment Pattern for PsP (Raw Material) Purchased in the: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 10 oz 20% 15,000 oz 130,000 oz $1.20 per oz 40% $1.10 per oz 30% 40% $1.10 per oz Remainder Remainder 80% $1.10 per oz Remainder 70% 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 $5,000 $40,000 $1,000 $1,000 $0 $10,000 $5,000 $40,000 $1,000 $1,000 $12,000 $10,000 $10,000 $45,000 $2,000 $1,000 $0 $11,000 $5,000 $45,000 $2,000 $2,000 $12,000 $12,000 $0 $0 $10,000 $10,000 Manufacturing Overhead Budget Variable Overhead Rate per Direct Labor Hour (DLH) Fixed Manufacturing Overhead per Quarter Depreciation per Quarter Included in Fixed Manufacturing Overhead $8 per DLH $40,000 $5,000 Finished Goods Ending 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 Advertising Fixed Cost Salary Fixed Cost Mall Kiosk Rental Fixed Cost Utilities Fixed Cost Property Taxes Depreciation $6 per unit Cash Budget Equipment Purchases Dividends per Quarter Annual Interest Rate Charged by the Bank on the Bank Loan $15,000 4% Income Statement No Required Input Data Prior Year Balance Sheet as of Dec. 31, 2016 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 $28,000 Remainder Bank Loan Common Stock Retained Earnings Total Liabilities & Equity 2 $60,000 $160,000 $442,000 $690,000 Sales Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year Sales in Units times Average Selling Price per Unit Total Dollar Sales Check Amount 1st 14,000 units $28 per unit $392,000 2nd 12,000 units $28 per unit $336,000 3rd 18,000 units $34 per unit $612,000 $392,000 Full Year 4th 18,000 units $34 per unit $612,000 Check Amount Calculation of Cash Collections - for the Cash Budget 1st Qtr. 2nd Qtr. Collection of Dec. 31, 2016 Accounts Rec. $690,000 Collection of 1st Qtr Sales of $392,000 $235,200 $33,600 3rd Qtr. 4th Qtr. $123,200 Collection of 2nd Qtr Sales of $336,000 Collection of 3rd Qtr Sales of $612,000 Partial Collection of 4th Qtr Sales of $612,000 Total Cash Collections Check Amounts $360,200 $207,200 $613,200 Calculation of the December 31, 2017 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, 2017 Check Amount $122,400 ( ) indicates that the cell value must be negative or 0 $774,000 Total 62,000 units $1,952,000 $1,952,000 Production Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year Sales in Units (from Sales Budget) less Beginning Inventory of Finished Units ( ) equals Finished Units to Produce for This Qtr.'s Sales plus Finished Units to Produce for Finished Goods Ending Inventory Total Finished Units to Produce Check Amounts Full Year 1st 14,000 (3,000) 11,000 2,400 13,400 2nd 12,000 3rd 18,000 4th 18,000 13,400 units 15,000 units 18,000 units 15,400 units ( ) indicates that the cells in this row must be negative or 0 Total Units Raw Materials Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 1st 2nd 3rd Full Year 4th Total Total Finished Units to Produce (from Production Budget) times Ounces of PlasticSteel Powder (PsP) Needed per Finished Unit equals Total Ounces of PsP Needed for Total Finished Units to Produce less Beginning Inventory of PsP in Ounces ( ) equals Ounces of PsP to Purchase for Total Finished Units to Produce plus Ounces of PsP to Purchase for Ending Raw Materials Inventory equals Total Ounces of PsP to Purchase times Cost of PsP per Ounce Total $'s of PsP (Raw Materials) Purchases Check Amount $178,800 Check Amount Calculation of Cash Payments for Raw Materials - for the Cash Budget 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. $310,424 $153,340 Payment of December 31, 2016 Accounts Payable Payment of 1st Qtr PsP Purchases of $0,000 Payment of 2nd Qtr PsP Purchases of $0,000 Payment of 3rd Qtr PsP Purchases of $0,000 Partial Payment of 4th Qtr PsP Purchases of $0,000 Total Cash Payments for PsP (Raw Materials) Check Amounts $99,520 $122,280 Calculation of December 31, 2017 Accounts Payable - for the Balance Sheet Total 4th Quarter $'s of PsP Purchases (per above) less 4th Quarter PsP Purchases Already Paid for (per above) ( ) Accounts Payable as of December 31, 2017 Check Amount $49,236 Calculation of Dec. 31, 2017 Raw Materials Ending Inventory (FIFO) for the Cost of Goods Sold Budget and the Balance Sheet 4th Quarter Ending PsP (Raw Materials) Inventory in Ounces times 4th Quarter Cost of PsP per Ounce Raw Materials Ending Inventory as of December 31, 2017 Check Amount $28,600 $706,800 Direct Labor Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 1st 2nd 3rd Full Year 4th Total Total Finished Units to Produce (from Production Budget) times Direct Labor Hours per Finished Unit equals Total Direct Labor Hours times Average Cost of a Direct Labor Hour Total Direct Labor $'s Check Amount $48,240 Cash Payments for Direct Labor - for the Cash Budget 1st Qtr. 2nd Qtr. Total Cash Payments for Direct Labor * * MyArmor pays all direct labor cost in the period incurred; thus Cash Payments = Total Direct Labor Cost Check Amount 3rd Qtr. 4th Qtr. $235,680 Manufacturing Overhead Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 1st 2nd 3rd Full Year 4th Total Total Direct Labor Hours (from Direct Labor Budget) times Variable Overhead Rate per Direct Labor Hour (DLH) equals Total Variable Manufacturing Overhead plus Fixed Manufacturing Overhead Total Manufacturing Overhead $'s Check Amount $61,440 Check Amount Calculation of Cash Payments for Manufacturing Overhead - for the Cash Budget 1st Qtr. 2nd Qtr. Total Manufacturing Overhead $'s (per above) less Depreciation Included in Manufacturing Overhead ( ) Total Cash Payments for Manufacturing Overhead Check Amounts $56,440 $59,000 3rd Qtr. 4th Qtr. $63,800 $59,640 Calculation of the Predetermined Overhead Rate - for the Finished Goods Ending Inventory Budget Full Year Total Manufacturing Overhead $'s divided by Full Year Total Direct Labor Hours equals Predetermined Overhead Rate per Direct Labor Hour (DLH) Check Amount $0 per DLH $21 $258,880 Finished Goods Ending Inventory Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Calculation of 4th Quarter Production Cost per Finished Unit Quantity Required Total Cost per Finished Unit 4th Qtr. Cost x x x PsP (Raw Materials) Direct Labor Manufacturing Overhead (applied based on Direct Labor Hours) per Finished Unit = = = 4th Qtr. Production Cost per Finished Unit Check Amount Calculation of Finished Goods Ending Inventory (FIFO) as of Dec. 31, 2017 Finished Units in Finished Goods Ending Inventory (from Production Budget) times 4th Qtr. Production Cost per Finished Unit Finished Goods Ending Inventory as of Dec. 31, 2017 Check Amount $52,640 $18.80 Cost of Goods Sold Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Raw Materials Inventory Raw Materials Beginning Inventory Jan. 1, 2017 plus Total $'s of PsP (Raw Materials) Purchases for 2017 (from Raw Materials Budget) equals Raw Materials Available for Transfer to Work-in-Process Inventory less Raw Materials Ending Inventory December 31, 2017 (from Raw Materials Budget) ( ) equals Raw Materials Transferred to Work-in-Process Inventory Work-in-Process Inventory (WIP) Beginning Work-in-Process Inventory Jan. 1, 2017 $0 plus Raw Materials Transferred to Work-in-Process plus Total Direct Labor $'s for 2017 (from Direct Labor Budget) plus Total Manufacturing Overhead $'s for 2017 (from Manufacturing Overhead Budget) equals WIP Available for Transfer to Finished Goods Inventory less Ending Work-in-Process Inventory Dec. 31, 2017 ( ) (0) equals Work-in-Process Inventory Transferred to Finished Goods Inventory Finished Goods Inventory and Cost of Goods Sold Finished Goods Beginning Inventory Jan. 1, 2017 plus Work-in-Process Inventory Transferred to Finished Goods Inventory equals Finished Goods Available for Sale less Finished Goods Ending Inventory Dec. 31, 2017 (from Finished Goods Inventory Budget) ( ) Cost of Goods Sold Check Amount $1,198,120 Selling, General, and Administrative (SG&A) Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 1st 2nd 3rd Full Year 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 $141,000 $186,000 Calculation of Cash Payments - for the Cash Budget 1st Qtr. 2nd Qtr. Total SG&A Cost (per above) less Depreciation Included in Total SG&A Cost ( ) Total Cash Payments for SG&A Check Amounts $131,000 3rd Qtr. $166,000 4th Qtr. Total Cash Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 1st 2nd 3rd 4th Beginning Cash Balance plus: Cash Collections (from Sales Budget) less: Payments for PsP (Raw Materials) (from Raw Materials Budget) ( ) Payments for Direct Labor (from Direct Labor Budget) ( ) Payments for Manufacturing Overhead (from Manufacturing OH Budget) ( ) Payments for SG&A (from SG&A Budget) ( ) Payments for Equipment Purchases ( ) Payments for Dividends ( ) Cash Balance before Borrowings, Repayments, and Interest plus Additional Borrowings from the Bank during the quarter 0 0 0 0 less Repayments Made to the Bank during the quarter ( ) 0 0 0 0 less Interest Paid on the Bank Loan at the End of the Quarter ( ) 0 0 0 0 Ending Cash Balance Check Amounts $24,445 $70,779 Complete both schedules before comparing your amounts to the Check Amounts Calculation of the Bank Loan Balance for the Balance Sheet 1st Qtr. 2nd Qtr. Beginning Loan Balance Before Additional Borrowings and Repayments plus Additional Borrowings less Repayments ( ) 3rd Qtr. 4th Qtr. Check Amount $0 Ending Loan Balance After Additional Borrowings and Repayments Forecasted Income Statement for the Profit Planning Year Ending December 31, 2017 MyArmor, Inc. 2017 Dollars Sales (from Sales Budget) less Cost 0.00% of Goods Sold (from Cost of Goods Sold Budget) ( ) 0.00% Gross Profit 0.00% less SG&A Cost (from SG&A Budget) ( ) 0.00% Operating Income less Interest % 0.00% Expense (from Cash Budget) ( ) 0.00% Net Income (Net Loss) Check Amount 0.00% $103,183 Forecasted Balance Sheet for the Profit Planning Year Ending December 31, 2017 MyArmor, Inc. as of December 31, 2017 Dollars % Assets Current Assets Cash (from Cash Budget) 0.00% Accounts Receivable (from Sales Budget) 0.00% Raw Materials Inventory (FIFO) (from Raw Materials Budget) 0.00% Finished Goods Inventory (FIFO) (from Finished Goods Budget) 0.00% Total Current Assets 0.00% Property, Plant, and Equipment, gross (from schedule below) 0.00% less Accumulated 0.00% Depreciation ( ) (from schedule below) Property, Plant, and Equipment, net 0.00% Total Assets Check Amount 0.00% $694,419 Liabilities and Stockholders' Equity Current Liabilities Accounts Payable (from Raw Materials Budget) 0.00% Bank Loan (from Cash Budget) 0.00% Total Current Liabilities 0.00% Stockholders' Equity Common Stock 0.00% Retained Earnings (from schedule below) 0.00% Total Stockholders' Equity 0.00% Total Liabilities and Stockholders' Equity 0.00% Check Amount $694,419 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, 2016 plus Total Payments for Equipment Purchases (from Cash Budget) Property, Plant, and Equipment, gross as of Dec. 31, 2017 Accumulated Depreciation Accumulated Depreciation as of Dec. 31, 2016 ( ) add Total Depreciation (from Manufacturing Overhead Budget) ( ) add Total Depreciation (from SG&A Budget) ( ) Accumulated Depreciation as of Dec. 31, 2017 ( ) Retained Earnings Retained Earnings as of Dec. 31, 2016 plus Net Income (Net Loss) (from Income Statement) less Total Dividends (from Cash Budget) ( ) Retained Earnings as of Dec. 31, 2017 Master Budgets The Master Budget Modeling Project Table of Contents 1. Overview of Your Learning Process 2. Software Preparation - Windows Computers 4. Software Preparation - Mac Computers 5. Working on the Project if You Do Not Have Excel on Your Computer 6. Introduction to Master Budgets 7. Introduction to MyArmor, Inc. 8. Understanding Master Budgets by Completing Your Master Budget Modeling Project 9. Starting Your Project 10. Inputs Tab 12. Sales Budget 15. Production Budget 18. Raw Materials Budget 21. Direct Labor Budget 23. Manufacturing Overhead Budget 25. Finished Goods Ending Inventory Budget 27. Cost of Goods Sold Budget 29. Selling, General, and Administrative (SG&A) Budget 31. Cash Budget 33. Forecasted Income Statement 35. Forecasted Balance Sheet 36. Final Test of Your Completed Model 38. Utilizing Your Budget Model to Evaluate Planning Questions & Opportunities 41. Submitting Your Project - How Your Project Will be Graded Appendix - Calculation Details for All Budget Line Items Master Budgeting 1 of 41 Overview of Your Learning Process You will learn about the Master Budget and the Master Budget process by: Reading a brief overview about the Master Budget. Reading an introduction to MyArmor, Inc., the hypothetical company you will use in your Master Budget Modeling Project. Doing the following for each of the eleven budgets that make up MyArmor's Master Budget: Read a brief introduction to the Budget. \"Wire-Up\" the Budget in your Master Budget Modeling Project. Run a test on the Budget you just \"Wired-Up\". Read a little more about the Budget, and see how that Budget's amounts will be used in the remaining budgets. Utilize your completed Budget Model to evaluate Planning Questions & Opportunities for MyArmor, Inc. STOP Before you continue reading in this document, be sure to perform the Master Budget software preparation process. If you use a Windows computer, complete the process on Page 2. If you use a Mac computer, complete the process on Page 4. If you have any issues during the software preparation process, please call the software support line. The software support phone number is: (513)939-5305 Master Budgeting 2 of 41 Software Preparation - Windows Computers To download the Master Budget software and place the software on your computer's C: drive, do the following: 1. Windows 10........... use Microsoft Edge as your browser Not Windows 10 .... use Internet Explorer as your browser 2. Go to the website: www.SolidFootingAccounting.com 3. Click DOWNLOAD Student Software 4. Enter your First Name - enter your Last Name 5. Select Master Budget 2017 from the drop-down list 6. Click Windows 7. Enter your Software ID Code - your code is in the email you received 8. Click SUBMIT 9. Confirm Software is Master Budget 2017 and Type of Computer is Windows - click Yes 10. Read the information in the Ready to Download? window - click Yes 11. To finish downloading the zip file and extract the software to your computer's C: drive: If you have a problem extracting the zip file, call Software Support (513)939-5305 a. Click Open b. If a Security message box appears, indicate that the download should be allowed to open c. Windows 8 or 10..............Click Extract (top of the screen) - then click Extract all Not Windows 8 or 10 ......Click Extract all files (upper left on the screen) d. When you are prompted to enter a Destination for the extracted files, delete the suggested destination in the extract to box, and enter just C:\\ e. Click Extract to finish the process f. Close all open windows - Close your browser 12. Your Master Budget files are now on your computer in: Computer or This PC C: (drive) Master Budget 1e (folder) Read the information below to complete the software preparation process. 1. Microsoft Excel is required to open the Master Budget Project workbook. If you do not have Excel, see Page 5 for information on how to copy your software to a USB flash drive, and then run the software on a Windows computer that does have Excel. Note: The Starter version of Excel and the RT version of Excel are missing essential Excel components and will not run the Master Budget Project software. 2. Read the information in the box on Page 3. After you read the information for Excel, then go to Page 6 in this document. Software Preparation - Windows Computers Master Budgeting 3 of 41 Information for: Excel 2016 - Excel 2013 - Excel 2010 The first time you open the Master Budget Project workbook, there will be a series of two messages shown just above the workbook on the left side of the screen. The Protected View message will appear first and the Security Warning message will appear second. Do the following to Enable Editing and Enable Macros: Protected View ...................... Enable Editing Click Security Warning .................. Enable Content Click Needing to do the following is rare - If you do not see the \"Security Warning\" message and the buttons in the project do not workthen perform the following steps to change your Macro Security setting: 1. Open your Excel program. 6. Click Macro Settings 7. Click Disable all macros with notification 2. Click File (upper left corner of the screen) 3. Click Options (last or second last choice on the left) 8. Click OK 4. Click Trust Center (last choice on the left in the window) 9. Click OK 5. Click Trust Center Settings 10. Exit out of Excel Workbook Tabs Not Visible If you are not able to see the tabs at the bottom of the Master Budget Project workbook, it is because the workbook needs to be maximized. To maximize the workbook: Hold down the Ctrl key and Press the F10 key Master Budgeting 4 of 41 Software Preparation - Mac Computers To download the Master Budget software and place the software on your Mac's desktop do the following: 1. Use Safari as your browser 2. Go to the website: www.SolidFootingAccounting.com 3. Click DOWNLOAD Student Software 4. Enter your First Name - enter your Last Name 5. Select Master Budget 2017 from the drop-down list 6. Click Mac 7. Enter your Software ID Code - your code is in the email you received 8. Click SUBMIT 9. Confirm Software is Master Budget 2017 and Type of Computer is Mac - click Yes 10. Read the information in the Ready to Download? window - click Yes 11. After the download is complete - close the Downloads window - click Home - Quit Safari 12. Click the Downloads folder in the Dock - drag the Master Budget 1e folder to your Desktop The Master Budget software is now on your Mac computer. Read the information below to complete the software preparation process. 1. Excel for Mac 2016 or Excel for Mac 2011 is required to open the Master Budget Project workbook. If you do not have Excel for Mac 2016 or Excel for Mac 2011, see Page 5 for information on how to copy your software to a USB flash drive, and then run the software on a Mac computer that does have Excel. Note: Excel 2008 is missing essential Excel components and will not run the Master Budget software. 2. Read the information in the box below. After you read the information, then go to Page 6 in this document. Using Excel for Mac 2016 or Excel for Mac 2011 to run the Master Budget Project 1. To open the Master Budget Project workbook - Double click the Master Budget 1e folder - Double click the project workbook you wish to open 2. Enable Macros - if when opening the project workbook, Excel displays a question about macrosalways click Enable Macros Tips: To exit out of Full Screen mode and display the \"Excel File Edit ..\" menu across the top of the screen - press the esc key Workbook Tabs Not Visible - If the tabs are not visible at the bottom of the project workbook, click the green + button in the top left corner of the screen. Saving a workbook - If you receive the message, \"This workbook contains features that will not work or may be removed if you save it in the selected file format. Do you want to continue?\" - always click Continue Master Budgeting 5 of 41 Working on the Project if You Do Not Have Excel on Your Computer You only need to read this page if you do NOT have Excel on your computer. As indicated on Pages 2 and 4, Excel is required to run the project software. If you do NOT have Excel 2010, 2013 or 2016 on your Windows computer - or Excel for Mac 2011 or Excel for Mac 2016 on your Mac computer, then an alternative for running your project software is to: Copy the Master Budget 1e folder from your computer to a USB flash drive. Take the USB flash drive to a computer that has one of the Excel versions listed above. Plug the flash drive into the computer - Run the project software from the flash drive. Copying the Master Budget 1e folder to a USB flash drive: On a Mac computer: Plug the flash drive into a USB port Point to the Master Budget 1e folder on the desktop - Hold down Control key - Click - Click Copy \"Master Budget 1e\" Point to the flash drive on the desktop - Hold down Control key - Click - Click Paste Item Double Click the flash drive - Confirm the Master Budget 1e folder is on the flash drive Close the window - Point to the flash drive - Hold down Control key - Click - Click Eject \" \" On a Windows computer: Plug the flash drive into a USB port Not Windows 8 or 10 .... Select Computer from the desktop or from the Start menu Windows 8 or 10........... Hold down Windows logo key - Tap the S key Type This PC - Press Enter key Double Click C: (drive) - Point to the Master Budget 1e folder - Right Click - Click Copy Click the flash drive - Right Click - Click Paste Point to the flash drive - Right Click - Click Eject Running the Budget Project from a USB flash drive on a Computer that has Excel On a Mac computer Plug the flash drive into a USB port Double Click the flash drive on the desktop - Double Click the Master Budget 1e folder Double Click the project workbook you wish to open After you are finished working on the project workbook - Quit Excel - Close the window - Point to the flash drive - Hold down Control key - Click - Click Eject \" \" On a Windows computer Plug the flash drive into a USB port Not Windows 8 or 10 .... Select Computer from the desktop or from the Start menu Windows 8 or 10........... Hold down Windows logo key - Tap the S key Type This PC - Press Enter key Double Click the flash drive - Double Click the Master Budget 1e folder Double Click the project workbook you wish to open After you are finished working on the project workbook - Exit Excel - Point to the flash drive - Right Click - Click Eject Master Budgeting 6 of 41 Introduction to Master Budgets You are familiar with the financial accounting system that companies use to record transactions, summarize those transactions, and prepare financial statements. The transactions that are recorded in a company's financial accounting system are transactions that have occurred prior to being recorded. The financial statements that are prepared from a financial accounting system are based on historythey are based on transactions that have occurred prior to the date of the financial statements. This is a long way of sayingtraditional financial statements are reporting history to a company's managers. While historical financial statements report important information to a company's managers equally or perhaps even more important to a company's success is a process that enables managers: To develop plans for the future. To test the financial results of those future plans, before the plans are implemented. To evaluate alternative future plans, and alternative implementation strategies. To quantity future plans in a way that enables actual results to be measured against expected results. The process that companies use to enable managers to plan for the future is called Master Budgeting. Some companies call the Master Budgeting process the Profit Planning process. A company's Master Budget, or Profit Plan, is made-up of a series of schedules that enable a company to estimate the expected profit that would result from selling a forecasted number of product units at a forecasted unit price. Think of the Master Budget process as a road map for a journey that the company will make in the future. That road map shows the company how to get from forecasted unit sales (the start of the journey) to forecasted profit (the end of the journey). The schedules (budgets) that make up a manufacturing company's Master Budget are the: Sales Budget Production Budget Raw Materials Budget Direct Labor Budget Manufacturing Overhead Budget Finished Goods Ending Inventory Budget Cost of Goods Sold Budget Selling, General, and Administrative (SG&A) Budget Cash Budget Forecasted Income Statement Forecasted Balance Sheet Just reading about the budgets that make up a Master Budget has little learning value and can be boring. The best way for you to learn about the Master Budget is for you to actually develop the road map that will take a company from forecasted unit sales to forecasted profit. In the sections that follow, you are going to prepare each of the individual budgets that make up the Master Budget for the hypothetical manufacturing company, MyArmor, Inc. Master Budgeting 7 of 41 Introduction to MyArmor, Inc. MyArmor, Inc. is a company that manufactures and sells cell phone covers. MyArmor developed and patented a proprietary material called PlasticSteel Powder (PsP) that is used to make the cell phone covers. PsP is the raw material input that is used in MyArmor's injection molding machine to produce the cell phone covers. Cell phone covers produced with PsP are extremely durable and do a superior job of protecting cell phones. Manufacturing the cell phone covers is a fairly simple process. At the beginning of the day the injection molding machine's material hopper is filled with PsP. The machine injects the PsP into a mold to produce the cell phone cover. During the day, additional PsP is poured into the material hopper as needed. At the end of the day, any remaining PsP is removed from the material hopper, and the machine is cleaned in preparation for the next day's production run. As a result of the above manufacturing process, MyArmor does not have any Work-in-Process Inventory. That is, at the end of each day, they do not have any partially completed cell phone covers. In terms of inventory, MyArmor only has: Raw Materials Inventory (PsP) Finished Goods Inventory (completed cell phone covers) MyArmor works closely with a local chemical company to produce the PlasticSteel Powder used in MyArmor's manufacturing process. The chemical company has signed a confidentiality agreement that covers MyArmor's secret formula for PsP. The payment terms to the chemical company are agreed upon each quarter, and are determined by quantities to be purchased and the cash flow needs of the chemical company. MyArmor distributes and sells their cell phone covers: To wholesale distributors that service specialty retailers. Direct to customers via the company's website. Kiosks in mall locations (this sales channel is new and is currently being tested with 2 kiosk locations). There is some seasonality to MyArmor's unit sales. Unit sales in the 3rd and 4th quarters of the year are higher due to holiday sales. In the 3rd quarter, the wholesale distributors are ramping-up inventory to prepare for holiday sales. In the 4th quarter, sales to wholesale distributors continue to be strong and website sales increase. Due to holiday demand, Average Selling Price per Unit can normally be increased in the 3rd and 4th quarters of the year. Master Budgeting 8 of 41 Understanding Master Budgets by Completing Your Master Budget Modeling Project You will learn about the Master Budget and the budgets that make up the Master Budget by completing the Master Budget Project for MyArmor. The Master Budget Project workbook is an Excel workbook that contains the shell for MyArmor's Master Budget. Your job will be to \"Wire-Up\" each of the budgets in the Master Budget. \"Wiring-Up\" means that you will be entering Excel formulas in all cells of each budget. To be able to enter the correct Excel formulas, you will need to know how each cell in the budget is calculated. Saying the same thing in a different wayto be able to complete your Master Budget Modeling Project, you will need to have a detailed understanding of each budget that makes-up MyArmor's Master Budget. For each of the eleven budgets you will: Read a brief introduction \"Wire-Up\" the Budget in your Master Budget Modeling Project When needed, refer to the Appendix - Calculation Details for All Budget Line Items, which is located at the end of this document Compare the Check Amounts given for the Budget to the amounts calculated by your \"Wired-Up\" Budget Run an additional test on your \"Wired-Up\" Budget by clicking the Test My Model button Read a little more about the Budget, and see how that Budget's amounts will be used in the budgets that follow the Budget just completed The next section will tell you how to begin working on your Master Budget Modeling Project. Master Budgeting 9 of 41 Starting Your Project To start your project, do the following: 1. Turn-in a Copy of the Email You Received Before you begin working on your Master Budget Modeling Project, be sure to turn-in to your instructor a copy of the email you received that contains your Name and Software ID Code. 2. Open the Project Workbook File The Master Budget Modeling Project is in a file entitled \"Master Budget Project 2017\". To open this workbook file, do the following: Open the file Master Budget Project 2017 in the Master Budget 1e folder Enable Macros For information on enabling Macros, refer to: Page 3 if you are working on a Windows computer Page 4 if you are working on a Mac computer 3. Activate the Project Workbook Enter your Software ID Code (Your code is in the email you received.) Enter your First Name Enter your Last Name, and press the Enter key Click the Activate Project button Be sure that your name is correct before clicking Yes to the question \"Is your name correct?\". Once your name is entered into your project, it cannot be changed. Complete the yellow information boxes that are located in the top right of the page. 4. Save Your Activated Project Save your Activated Project by following the directions in the box entitled: SAVING YOUR PROJECT - Immediately after Activation 5. Tab Through the Project to See What It Contains Observe across the bottom of the workbook the 13 tabs that your project contains. The first (left most) tab is the Start tab, and the last (right most) tab is the BalSht tab. If you do not see the 13 tabs at the bottom of the workbook, refer to the heading Workbook Tabs Not Visible on Pages 3 and 4 in this document. Click on each Tab in your project to see what you will be working on. 6. Stopping and then Restarting Work on Your Project You can complete a portion of your project - stop working on it - then continue working on it at a later time. Before you stop working on your project, be sure it has been saved. When you return to your project, be sure the file you open is the file that you saved using your name. Master Budgeting 10 of 41 Inputs Tab To start this section: If it 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 1e folder on your computer. If you are prompted with a question concerning Macros, always click Enable Macros 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, 2017. 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 MyArmor's managers, is the fact that a change to any value in one of the yellow input cells will cascade through all budgets that make up MyArmor's Master Budget model. For example, if Sales in Units in the 1st 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 \"WireUp\" 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 workbook. 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. Inputs Tab Master Budgeting 11 of 41 The Fix How Cells Display button at the top left of the screen will fix any cell display problems that might be caused by copying cells incorrectly while \"Wiring-Up\" your model. Each tab contains one of these buttons, and clicking the button will fix the cells on that tab. You can never hurt anything by clicking this buttonand you will never loose any of your work by clicking this button. You will use the Test My Model button at the top right of the screen after you finish \"Wiring-Up\" each budget and after your entire Master Budget Model is complete. The use of this button will be discussed in more detail after you complete the Sales Budget on the next tab. You will now begin working on the Sales Budget, which is the first budget in MyArmor's Master Budget. Master Budgeting 12 of 41 Sales Budget To start this budget: Click the Inputs tab Click the Set Input Values Back to Original Amounts button Click the Sales tab Introduction The Sales Budget is the first budget in MyArmor's Master Budget. This budget shows/calculates amounts by quarter for: Sales in Units of cell phone covers, the Average Selling Price of the cell phone covers sold, and the calculated Total Dollar Sales of cell phone covers. This budget also calculates Full Year Sales in Units and Full Year Total Dollar Sales. Below the Sales Budget are schedules that calculate Total Cash Collections by quarter, and Accounts Receivable as of December 31, 2017 (the end of the planning year). \"Wiring\" It is now time for you to get started \"Wiring-Up\" MyArmor's first budget. There are many Calculation Hints and Excel Formula Hints to help you get started. This results in many arrows on this tab, and makes the budget look a little busy. The important thing to remember is to focus on just the one cell you are \"Wiring-Up\". Observe how the 1st Quarter Total Dollar Sales are collected. Some Cash is collected in the 1st Quarter, some Cash is collected in the 2nd Quarter, and the Remainder is collected in the 3rd Quarter. Help with getting started \"Wiring\" these Cash collection cells is shown in the hint for cell C17 under the heading Calculation Hints and Excel Formula Hints. Note that the amount for cell C16 Collection of Dec. 31, 2016 Accounts Rec. comes from the Prior Year Balance Sheet, which is at the bottom of the Inputs tab. This is an example of a cell where you might want to use the Appendix - Calculation Details for All Budget Line Items at the end of this document to see where the cell value comes from or how to calculate the cell value. As noted above, for help with \"Wiring-Up\" the cells on the Sales tab see: Calculation Hints and Excel Formula Hints to the right of the budget Appendix pages 1 and 2 in the Appendix - Calculation Details for All Budget Line Items at the end of this document Some helpful tips for this budget, and for all other budgets in your project, are: All white cells must contain Excel formulas. The formulas can either be a reference to another cell in the workbook (e.g. =Inputs!E9) ) or a calculation (e.g. =C8*C9). Never enter a number into a white cell (e.g. 300,000). A cell that has a row title that ends with ( ) must contain an Excel formula that evaluates to a negative number or 0. See the cell D26 with the row title less 4th Quarter Sales Already Collected (per above) ( ) for an example of this. Also see the Hints Box for cell D26. Sales Budget Master Budgeting 13 of 41 Note how cell D27 Accounts Receivable as of December 31, 2017 is calculated. Cell D27 is Total Dollar Sales 4th Quarter less 4th Quarter Sales Already Collected. Since the value in cell D26 is a negative number, the Excel formula you should put in cell D27 is =D25 + D26 Also see the Hints Box for cell D27. If while \"Wiring-Up\" your Sales Budget, any of the cell display formats are inadvertently changed by one of your cell copy operations, click the Fix How Cells Display button. Clicking this button will fix any cell display problems in the Sales Budget and in the two schedules below the Sales Budget. You can never hurt anything by clicking this buttonand you will never loose any of your work by clicking this button. Testing There are 7 Check Amounts given to you for the Sales Budget and the two schedules below the Sales Budget. Before you check the amounts produced by your \"Wiring\