please show the excel formula on how to calculate for the (cash balance, beginning) located inside the red square box

Napoleon, Inc. Sales Budget For the 6 mos ending June 16 ov 15 11,250 10.00 $ S 112.500 $ Dec '15 11,600 10.00 116,000 S Feb '16 11,400 10.00 $ 114.000 $ Mar 16 12,000 10.00 $ 120,000 $156.000 S Jan 16 10,000 10.00 $ 100.000 $ Apr 16 15,600 10.00 S May 16 18,000 11.25 202.500 $ 247,500 S June 16 22,000 11.25 6 mos total 89,000 Budged unit sales Selling price per unit $ S 940,000 Total Sales Cash Sales % 30% Credit Sales 70% 60,750 74,250 141,750 202,500 247,500 S 36,000 84,000 120,000 $ 156,000 $ Cash Sales Credit Sales Total Sales 34,800 81,200 116,000 S 30,000 70,000 100.000 $ 114.000 S S 33,750 $ 78,750 $ 34 200 S 48,800 109.200 282,000 658,000 940,000 79.800 173 250 112.500 $ 112500 30%-337500 & 112500 70 %-78750 and so on Current month A/R Collections 30% 1 month prior A/R Colections 2 months prior A/R Collections Uncollectible 50% 16% 4% Napoleon, Inc. Cash Collections Far the 6 mos ending June 16 Feb 16 34.200 23,940 Mar 16 36,000 25,200 39,900 11.200 Apr 16 46.800 32,760 Jan '16 30,000 21.000 May '16 60,750 42,525 54,600 13,440 106,132 S112,300 $134 328 171315 $ June 16 74.250 51,975 $ 70,875 S 17,472 S 214.572 S 6 mos total 282,000 197,400 282,975 80,472 842.847 Current month cash Sales Current month A/R Collections 1 month prior A/R Collections 2 months prior A/R Collections Total cash collections 40,600 12,600 104.200 $ 35,000 42,000 12,992 12,768 Bad Debl Expense 3.150 3,248 2,800 3.192 3,360 4.368 $ 20,118 Desired ending inventory % 40% Napoleon, Inc Purchase Budget For the 6 mos ending June '16 Apr '16 15,600 7.200 22,800 6,240 16.560 Nov 15 11,250 4640 15.890 Dec '15 11,600 4,000 15,600 4,640 10,960 Jan 16 10,000 Feb '16 11,400 4,800 16,200 4,560 11.640 Mar 16 12.000 May 16 18,000 June 16 22,000 7200 29,200 8,800 20,400 6 mos total Budged unit sales Add desired ending inventory 89,000 38,800 127,800 35.600 92.200 4560 14.560 4,000 10.560 6,240 8,800 28,800 7,200 19.600 Total needs 18,240 Less Beginning Inventory Required Purchases 4.800 13.440 4.00 46,560 $ 4.00 S Cost per unit Purchases 4.00 $ 43,840 $ 4.20 $ 4 20 4.41 S 4.41 56.448 $ 69,552 $ 88,436 S 42,240 89,984 391,200 % Paid in Month of Purchase % Paid in Month after Purchase 40% 60% Napoleon, Inc. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For the 6 mos ending June 16 Mar 16 Apr '16 22,579 S 27.936 50,515 S 6 mos total 156,480 207,046 363.526 Jan '16 16.896 $ 26.304 Feb '16 May 16 34.574 June 16 18,624 S 27,821 S 35,986 $ Cash purchases 1 month prior A/P Collections Cash disbursements for merchand se purch. 25.344 33,869 41,73 51,862 61,690 S 76,306 $ 87,847 S 43.200 43,968 S Variable Operating Expenses: Fixed Operating expenses: Salaries and Wages Rent Supplies Expense Other Overhead Other- Depreciation 3,000 Sales Commissions, 9% of Revenue 8,000 Other Variable Cash Expenses, % of Revenue 2.000 7% 6% 24,000 24,000 Napoleon, Inc. Operating Expense Budget For the 6 mos ending June '16 Jan '16 3,000 Feb '16 3,000 $ May 16 Mar '16 Apr 16 3,000 $ 6 mos total 18,000 65,800 48,000 56,400 12,000 144,000 144,000 20,118 June '16 3,000 $ 17,325 8,000 $ Salaries and Wages 3,000 8,400 8,000 7,200 2,000 24,000 24,000 3,000 7,980 8,000 6,840 2,000 24,000 24,000 3,248 79,068 10,920 8,000 9,360 2,000 24,000 24,000 3,192 84,472 14,175 Sales Commissions 7,000 8,000 6,000 2,000 24,000 24,000 3.150 77,150 Rent 8.000 14,850 $ 2,000 $ 24,000 $ 24,000 $ 4.368 $ 97,543 Other Variable Cash Expenses Supplies Expense Other- Overhead 12,150 2,000 24,000 24,000 3,360 90,685 Other - Depreciation Bad Debt Expense Total operating expenses Depreciation and noncash items Bad Debt Expense Cash disbursements for operating expenses 2,800 79,400 508,318 24,000 $ 4,368 $ 69,175 $ 24,000 3,150 50.000 24,000 3,248 51,820 $ 24,000 2,800 52,600 $ 24,000 3,192 57,280 24,000 3,360 63,325$ 144,000 20,118 344,200 $ $ S Equipment payment - January Equipment payment - February Dividends-March 50,000 30,000 12,000 15,000 Minimum Monthly Cash Budget Napoleon, Inc. Cash Budget For the 6 mos ending June 16 Mar '16 15,344 $ 112,300 127,644 Apr 16 15,529 $ 134,328 149,857 Jan '16 $ 15,000 $ Feb '16 May 16 15,887 $ June '16 15,571 $ 214,572 $ 230,143 6 mos total 92,331 842,847 935.178 Cash balance, beginning 15,000 $ Add collections from customers 104,200 119.200 106,132 121,132 171,315 187,202 Total cash available Less disbursements: Cash disbursements for merchandise purch. Cash disbursements for operating expenses Equipment purchases Dividends Total cash disbursements 50,515 52,600 87,847 $ 69,175 $ 43,200 50,000 50,000 43,968 51,820 30,000 61,690 76,306 63,325 363,526 344,200 80,000 12,000 799,726 135.452 57,280 12,000 115,115 12,529 143,200 118.970 139,631 47,571 157,022 73,121 125,788 30,887 (24,000) (4,656) Excess of receipts over disbursements Financing: Borrowing-note Repayments-note Total financing Cash balance, ending 20,000 3,000 62,000 39,000 (15,000) $ (15,000) 58,121 $ (15,000) (15,000) 15,887 $ (32,000) (32,000) 15,571 (62,000) 39,000 15,000 $ 20,000 15,344 $ 3,000 15,529 $ 0 135,452 Napoleon, Inc. Budgeted Income Statement For the 6 mos ending June '16 Mar '16 120,000 S 49.440 70,560 79.400 (8 840) Total 940,000 Cost of goo A800 4)+(7200*-4.2) Jan '16 100,000 $ 40,000 60.000 $ 77.150 (17,150) $ Feb 16 114,000 S 45,600 68,400 $ Apr 16 156,000 S 65,520 90,480 S 84,472 6,008 S May 16 202,500 247,500 $ 77,868 124,632 $ 90,685 33 947 June '16 Sales, net Cost of goods sold: Gross margin Total operating expenses Net Income 97,020 150,480 $ 375448 494000 564,552 77868= 508.318 56.234 (7200 4.2) (10800 4.41) 79,068 97,543 52,937 S (10,668) S