Cash Budget - for the Profit Planning Year Jan. 1 to Dec. 31, 2023 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. Full Year Total 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 or (Deficiency of Cash) before Borrowings, Repayments, Interest plus Additional Borrowings from the Bank during the quarter less Repayments Made to the Bank during the quarter () Cash Balance before Paying Interest less Interest Paid on the Bank Loan at the End of the Quarter () Ending Cash Balance Quarter in the 2023 Planning Year 1st 2nd 3rd $24,000 305,000 282,000 667,600 (83,200) (150,168) (211,328) (35,280) (64,000) (73,600) (54,760) (62,200) (65,080) (88,000) (116,000) (127,000) 0 0 (10,000) (20,000) (20,000) (20,000) 47,760 60,000 0 0 (22,760) 0 0 37,240 (1,179) 0 0 505,600 (219,880) (47,520) (58,840) (152,000) (15,000) (20,000) (25,000) (80,000) A B $24,793 $24,271 Check Amounts See IMPORTANT box above Fix the Format of All White Cells and Restore the Formulas in the Gray Cells 4th Qtr. Loan Schedule -- Calculation of the Bank Loan Balance for the Interest Calculation and for the Balance Sheet 1st Qtr. 2nd Qtr. 3rd Qtr. Beginning Loan Balance Before Additional Borrowings and Repayments $60,000 plus Additional Borrowings 60,000 less Repayments ( ) (22,760) Ending Loan Balance After Additional Borrowings and Repayments $97,240 Check Amount $18,259 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 $300 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 SG&A Budget. B. Ending Cash Balance on the Cash Budget is an input to the Forecasted Income Statement. C. Ending Cash Balance on the Cash Budget is an input to the Sales Budget. D. None of the above statements are correct. Inputs (Assumptions) for the Profit Planning Year Jan. 1 to Dec. 31, 2023 MyArmor, Inc. Set Input Values Back to Original Amounts As of Dec. 31 2022 Applies to All Qtrs. Fix the Format of All Yellow Cells Quarter in the 2023 Planning Year 2nd 3rd 1st Quarter of 2024 in 2023 1st 4th 3.000 units Production Budget Finished Units in Inventory - as of 12/31/2022 (the prior year) Forecasted Sales in Units - 1st Quarter of 2024 (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 PlasticSteel Powder (PSP) Required per Finished Unit % of Next Qtr.'s "Ounces of Psp Required" to Have in This atr's Ending Inventory Ounces of Psp in Raw Materials Inventory - as of 12/31/2022 (the prior year}| ) Forecasted 'Ounces of Psp Required" in the 1st Qtr of 2024 (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 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 S6 per DLH $50,000 $7.000 Finished Goods Inventory Budget Direct Labor Budget DirectLabor Hours (hrs) per Finished Unit Average Costofa DirectLabor Hour 0.20 hrs $18 per hour $20 per hour $20 per hour $18 per hour Manufacturing Overhead Budget Variable Overhead CostRate per DirectLabor Hour (DLH) Total Fixed Manufacturing Overhead Costper 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 Sheetbelow $4 per unit $5,000 $40,000 $5,000 $40,000 $15,000 $45,000 $15,000 $50,000 Selling. General, and Administrative (SG&A) Budget Variable SG&A Costper Unit Sold Advertising Fixed Cost Salary Fixed Cost Mall Kiosk Rental Fixed Costper Quarter Utilities Fixed Costper Quarter Property Taxes Depreciation $2,000 $1,000 $0 $12,000 $12,000 $12,000 $0 $13,000 $12,000 $14,000 Cash Budaet Equipment Purchases Dividends Annual InterestRate 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. 2022 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 Pavable Bank Loar Common Sock Retained Earnings Total Liabilities & Equity $28.000 $60,000 $160,000 $442,000 $690.000