I am confused to calculate cash budget please help me.

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: _ 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 Project. Inputs (Assumptions) 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 12,000 units $26 per unit 40% Average Selling Price per Unit Cash Collection Pattern for Sales in the: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 10,000 units $28 per unit 20% 60% 14,000 units $26 per unit Remainder Remainder 80% 16,000 units $28 per unit Remainder 70% Remainder Production Budget Finished Units in Inventory - as of 12/31/16 (the prior year) Forecasted Sales in Units - 1st Quarter of 2018 (next year) % of the Next Qtr.'s Sales in Units to Have in This Qtr.'s Ending Inventory 3,000 units 16,000 units 30% Raw Materials Budget Ounces of PlasticSteel 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/16 (the prior year) Forecasted "Ounces of PsP Required" in the 1st Qtr of 2018 (next year) Cost of PsP per Ounce Cash Payment Pattern for PsP Purchased in the: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 10 oz 20% 30% 30% 20% 15,000 oz 130,000 oz $1.20 per oz 40% $1.10 per oz 30% 50% $1.10 per oz Remainder 30% 80% $1.10 per oz Remainder 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 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 Finished Goods Inventory Budget $8 per DLH $50,000 $9,000 $18 per hour $20 per hour $20 per hour Remainder 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 $5,000 $40,000 $1,000 $1,000 $0 $12,000 $5,000 $40,000 $1,000 $1,000 $12,000 $12,000 $15,000 $45,000 $2,000 $2,000 $0 $13,000 $15,000 $45,000 $2,000 $2,000 $12,000 $14,000 $0 $0 $10,000 $10,000 Cash Budget Equipment Purchases Dividends per Quarter Annual Interest Rate Charged by the Bank on the Bank Loan $15,000 6% Income Statement No Required Input Data Prior Year Balance Sheet as of Dec. 31, 2016 3 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, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year A Sales in Units times Average Selling Price per Unit Total Dollar Sales Check Amount 1st 12,000 units $26 per unit $312,000 2nd 10,000 units $28 per unit $280,000 3rd 14,000 units $26 per unit $364,000 ReSet Inputs Check Amount Calculation of Cash Collections - for the Cash Budget 1st Qtr. 2nd Qtr. D Collection of Dec. 31, 2016 Accounts Rec. $125,000 Collection of 1st Qtr Sales $124,800 $62,400 Collection of 2nd Qtr Sales $168,000 Collection of 3rd Qtr Sales 3rd Qtr. $124,800 $112,000 $291,200 Partial Collection of 4th Qtr Sales Check Amounts $249,800 ReSet Inputs $230,400 ReSet Inputs $528,000 ReSet Inputs Calculation of the December 31, 2017 Accounts Receivable - for the Balance Sheet Total Dollar Sales 4th Quarter (per above) $448,000 F less 4th Quarter Sales Already Collected (per above) ( ) ($313,600) Accounts Receivable as of December 31, 2017 $134,400 G Check Amount 52,000 units $1,404,000 ReSet Inputs 1 Incorrect Check Amount Blank Multiple Choice Question(s) B Total Cash Collections 4th 16,000 units $28 per unit $448,000 Full Year Total ReSet Inputs Multiple Choice Questions 4th Qtr. E $72,800 $313,600 $386,400 ReSet Inputs 1. Which one of the following statements is correct? Answer: Production Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. B 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 A Quarter in the 2017 Planning Year 1st 12,000 3,000 (3,000) 12,000 ReSet Inputs 2nd 10,000 4,200 (3,000) 11,200 ReSet Inputs 3rd 14,000 3,600 (3,000) 14,600 ReSet Inputs 4th 16,000 3,000 (3,000) 16,000 Full Year Total Units 53,800 units ReSet Inputs All Check Amounts OK Blank Multiple Choice Question(s) Multiple Choice Questions 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 5 rather than 20? A. B. C. D. Answer: D 80 90 100 110 2. Which one of the following statements is correct? A. Finished Units to Produce on the Production Budget will be an input to the Cash Budget. B. Total Dollar Sales on the Sales Budget is an input to the Production Budget. C. Finished Units to Produce on the Production Budget will be an input to the Direct Labor Budget. D. None of the above statements are correct. Answer: C 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 4th 11,200 units 14,600 units 16,000 units 10 oz per unit 120,000 oz 10 oz per unit 112,000 oz 10 oz per unit 146,000 oz 10 oz per unit 160,000 oz 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 120,000 oz 22,400 oz (15,000) oz 127,400 oz 112,000 oz 43,800 oz (22,400) oz 133,400 oz 146,000 oz 48,000 oz (43,800) oz 150,200 oz 160,000 oz 26,000 oz (48,000) oz 138,000 oz Total Ounces of PsP to Purchase times Cost of PsP per Ounce Total Cost of PsP Purchases 127,400 oz $1.20 per oz $152,880 133,400 oz $1.10 per oz $146,740 150,200 oz $1.10 per oz $165,220 138,000 oz $1.10 per oz $151,800 Finished Units to Produce (from Production Budget) times Ounces of PlasticSteel Powder (PsP) Required per Finished Unit Ounces of PsP Required to Produce the Finished Units Check Amount ReSet Inputs Check Amount Full Year Total $616,640 ReSet Inputs All Check Amounts OK Blank Multiple Choice Question(s) Calculation of Cash Payments for Raw Materials - for the Cash Budget 1st Qtr. 2nd Qtr. Payment of December 31, 2016 Accounts Payable $28,000 Payment of 1st Qtr PsP Purchases $61,152 $45,864 Payment of 2nd Qtr PsP Purchases $73,370 Payment of 3rd Qtr PsP Purchases 3rd Qtr. $45,864 $44,022 $132,176 Partial Payment of 4th Qtr PsP Purchases Total Cash Payments for PsP Check Amounts $89,152 ReSet Inputs $119,234 ReSet Inputs $222,062 ReSet Inputs Calculation of December 31, 2017 Accounts Payable - for the Balance Sheet 4th Quarter Total Cost of PsP Purchases $151,800 less Partial Payment of 4th Qtr PsP Purchases ( ) ($106,260) Accounts Payable as of December 31, 2017 $45,540 Check Amount ReSet Inputs Calculation of Dec. 31, 2017 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 26,000 oz times 4th Quarter Cost of PsP per Ounce Raw Materials Inventory as of December 31, 2017 Check Amount $1.10 per oz $28,600 ReSet Inputs 4th Qtr. $29,348 $33,044 $106,260 $168,652 ReSet Inputs Direct Labor Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 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 1st 2nd 3rd 4th 12,000 units 0.20 hrs 11,200 units 0.20 hrs 14,600 units 0.20 hrs 16,000 units 0.20 hrs 2,400 hrs 2,240 hrs 2,920 hrs 3,200 hrs 24,000 hrs $18 per hr 2,240 hrs $18 per hr 2,920 hrs $20 per hr 3,200 hrs $20 per hr $43,200 $40,320 $58,400 $64,000 ReSet Inputs Check Amount Full Year Total $205,920 ReSet Inputs All Check Amounts OK Blank Multiple Choice Question(s) Cash Payments for Direct Labor - for the Cash Budget 1st Qtr. 2nd Qtr. Total Cash Payments for Direct Labor * $43,200 $40,320 * MyArmor pays all direct labor cost in the period incurred; thus Total Cash Payments = Total Direct Labor $'s 3rd Qtr. 4th Qtr. $58,400 $64,000 Manufacturing Overhead Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Quarter in the 2017 Planning Year 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 1st 2,400 hrs $8 per DLH $19,200 2nd 2,240 hrs $8 per DLH $17,920 3rd 2,920 hrs $8 per DLH $23,360 4th 3,200 hrs $8 per DLH $25,600 $50,000 $50,000 $50,000 $50,000 $69,200 $67,920 $73,360 $75,600 ReSet Inputs Check Amount Full Year Total 10,760 hrs $286,080 ReSet Inputs All Check Amounts OK Blank Multiple Choice Question(s) Calculation of Cash Payments for Manufacturing Overhead - for the Cash Budget 1st Qtr. 2nd Qtr. Total Manufacturing Overhead Cost (per above) $69,200 $67,920 less Depreciation Included in Manufacturing Overhead ( ) ($9,000) ($9,000) Total Cash Payments for Manufacturing Overhead $60,200 $58,920 Check Amounts ReSet Inputs ReSet Inputs 3rd Qtr. $73,360 ($9,000) $64,360 ReSet Inputs Calculation of the Predetermined Overhead Rate - for the Finished Goods Ending Inventory Budget 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 $286,080 10,760 hrs $27 per DLH ReSet Inputs A 4th Qtr. $75,600 ($9,000) $66,600 ReSet Inputs Full Year Total Finished Goods Inventory Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Calculation of 4th Quarter Production Cost per Finished Unit A Quantity Required per Finished Unit PsP (Raw Materials) Direct Labor Manufacturing Overhead (applied based on Direct Labor Hours) Total Cost per Finished Unit 4th Qtr. Cost = = = $11.00 per unit $4.00 per unit $5.40 per unit 4th Qtr. Production Cost per Finished Unit $20.40 per unit 10 oz 0.20 hrs 0.20 hrs x x x $1.10 per oz $20 per hr $27 per DLH Check Amount Calculation of Finished Goods Inventory (FIFO) as of Dec. 31, 2017 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, 2017 Check Amount 4,800 units $20.40 per unit $97,920 ReSet Inputs ReSet Inputs B Cost of Goods Sold Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. Raw Materials Inventory Raw Materials Inventory as of January 1, 2017 $18,000 plus Total Cost of PsP Purchases for 2017 (from Raw Materials Budget) 616,640 less Raw Materials Inventory as of December 31, 2017 (from Raw Materials Budget) ( ) (28,600) equals Cost of Raw Materials Transferred to Work-in-Process Inventory $606,040 Work-in-Process Inventory (WIP) Work-in-Process Inventory as of January 1, 2017 $0 plus Cost of Raw Materials Transferred to Work-in-Process Inventory 606,040 plus Total Direct Labor $'s for 2017 (from Direct Labor Budget) 205,920 plus Total Manufacturing Overhead Cost for 2017 (from Manufacturing Overhead Budget) 286,080 less Work-in-Process Inventory as of December 31, 2017 ( ) (0) equals Cost of Work-in-Process Transferred to Finished Goods Inventory $1,098,040 Finished Goods Inventory and Cost of Goods Sold Finished Goods Inventory as of January 1, 2017 plus Cost of Work-in-Process Transferred to Finished Goods Inventory $60,000 1,098,040 less Finished Goods Inventory as of Dec. 31, 2017 (from Finished Goods Inventory Budget) ( ) Cost of Goods Sold Check Amount (97,920) $1,060,120 ReSet Inputs 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 4th Full Year Total SG&A Variable Cost Sales in Units (from Sales Budget) times Variable SG&A Cost per Unit Sold Total SG&A Variable Cost 12,000 units $6 per unit $72,000 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 10,000 units $6 per unit $60,000 14,000 units $6 per unit $84,000 16,000 units $6 per unit $96,000 $5,000 40,000 1,000 1,000 0 12,000 $49,000 $5,000 40,000 1,000 1,000 12,000 12,000 $71,000 $15,000 45,000 2,000 2,000 0 13,000 $77,000 $15,000 45,000 2,000 2,000 12,000 14,000 $90,000 $131,000 $131,000 $161,000 $186,000 ReSet Inputs $609,000 ReSet Inputs All Check Amounts OK Blank Multiple Choice Question(s) Calculation of Cash Payments - for the Cash Budget 1st Qtr. 2nd Qtr. Total SG&A Cost (per above) $131,000 $131,000 less Depreciation Included in Total SG&A Cost ( ) ($12,000) ($12,000) Total Cash Payments for SG&A $119,000 $119,000 Check Amounts ReSet Inputs 3rd Qtr. $161,000 ($13,000) $148,000 ReSet Inputs 4th Qtr. $186,000 ($14,000) $172,000 Full Year Total ($510,000) Cash Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2017 MyArmor, Inc. 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. Beginning Cash Balance Quarter in the 2017 Planning Year 1st 2nd 249,800 less Payments for PsP (Raw Materials) (from Raw Materials Budget) ( ) (89,152) 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 ( ) less Payments for Dividends ( ) Excess Cash or (Deficiency of Cash) before Borrowings, Repayments, Interest Full Year Total 4th $24,000 plus Cash Collections (from Sales Budget) less less less less 3rd (43,200) (60,200) (119,000) 0 (15,000) (15,000) 52,752 plus Additional Borrowings from the Bank during the quarter 0 0 0 0 A less Repayments Made to the Bank during the quarter ( ) 0 0 0 0 B 0 0 0 Cash Balance before Paying Interest 25,000 less Interest Paid on the Bank Loan at the End of the Quarter ( ) 0 C Ending Cash Balance ReSet Inputs Check Amounts See IMPORTANT box above ReSet Inputs 3 Incorrect Check Amounts 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 ( ) 0 0 Ending Loan Balance After Additional Borrowings and Repayments Check Amount ReSet Inputs Forecasted Income Statement for the Profit Planning Year Ending December 31, 2017 MyArmor, Inc. 2017 Dollars Sales (from Sales Budget) less Cost of Goods Sold (from Cost of Goods Sold Budget) ( ) Gross Profit less SG&A Cost (from SG&A Budget) ( ) Operating Income less Interest $1,404,000 100.00% (1,060,120) (75.51%) 343,880 24.49% (609,000) (43.38%) 265,120 (18.89%) 0 0.00% Expense (from Cash Budget) ( ) Net Income (Net Loss) Check Amount % (18.89%) ($278,127) 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 Property, Plant, and Equipment, gross (from schedule below) 0.00% 0.00% less Accumulated 0.00% Depreciation ( ) (from schedule below) Property, Plant, and Equipment, net 0.00% Total Assets Check Amount 0.00% ReSet Inputs 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 Check Amount 0.00% ReSet Inputs 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 Equipment Purchases - Full Year Total (from Cash Budget) Property, Plant, and Equipment, gross as of Dec. 31, 2017 Accumulated Depreciation Accumulated Depreciation as of Dec. 31, 2016 ( ) add Depreciation - Full Year Total (from Manufacturing Overhead Budget) ( ) add Depreciation - Full Year Total (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 Dividends - Full Year Total (from Cash Budget) ( ) Retained Earnings as of Dec. 31, 2017