Please tell me the excel formulas for the picture below in response to the problem 13.6
12.4 Excel Project. REQUIRED a. Create a 12-month cash flow budget in Excel using the following assumptions: Initial sales of $5 million, with forecasted monthly growth of 1% . 40% of each month's sales for cash: 30% collected the following month, 20% . collected 2 months later; 8% collected 3 months later, and 2% never collected Initial cash balance of $350,000 monthly sales growth to range from 0.5% to 1.5% in increments of 0.1%. sales to vary from $4 million to $6 million in increments of $100,000. accuracy. b. Add a "spinner" to your spreadsheet that will enable you to easily change forecasted c. Add a scroll bar to your spreadsheet that will let you modify the amount of initial d. Design appropae data antry apsing contro re spreadsheet 13.6 Excel Project pected cash outflows equal to 77% of the current month's sales. b. Also add a row to calculate the amount of cash that needs to be borrowed in order to maintain a minimum cash balance of $50,000 at the end of each month. c. Add another row to show the cash inflow from borrowing. d. Add another row to show the cumulative amount borrowed. e. Add another row to show the amount of the loan that can be repaid, being sure to maintain a minimum ending balance of $50,000 each month. Add appropriate data validation controls to ensure spreadsheet accuracy. f. Updated Excel Problems Romney and Steinbartxisx Microsoft Excel 0 Trace Precedents Show Formulas Define Name in FormulaTrace Dependents Error CheckingWatch CalculationCalculate Sheet Insert AutoSum Recently Financial Logical Text Date& Lookup & Math More QE Evaluate Formula Window Options Formula Auditing Managerie create from Selection Remove Amows. Time. Reference. & Trig. Functions. oProblem 13.6-12 Month Cash Budget, expanded 1 Problem 13.6-12 Month Cash Budget, expanded 4 Initial sales: 5 Monthly sales growth 6 % monthly cash sales 7 % collected following months 8 collected two months = 9 collected three months 10 % never collected- 11 Initial Cash balance 12 cash outflows as % current month sales ending cash balance each month 50,000 Beginning loan Jaaryebruary March April May July September October November December June August Beginning Cash Balance 350,000 50,000 50,000 50,000 $ 50,000 $ 837,842 $ 1,892,721 $ 2,958,148 $ 4,034,229 5,121,071 6,218,782 7,327,470 5,000,000 5,050,000 5,00,5005,151,505 5,203,020 5,255,050 5,307,601 5,360,677 5,414,284 5,468,4275,523,111 5,578,342 2,000,000 2,020,000 2,040,200 2,060,602 2,081,208 2,102,020 2,123,040 2,144,2712,165,714 2,187,371 2,209,244 2,231,337 0 1,500,000 2,515,000 2,940,150 2,969,552 2,999,2473,029,239 3,059,532 3,090,127 3,121,029 3,152,239 3,183,761 3,850,000 3,888,500 3,927,385 3,966659$ 4,006,325 4,046,389$4,086,853 $ 4,127,721 4,168,999 4,210,689 4,252,795 4,295,323 Tentative Cash Balance (1,500,00o) (318,500) $ 677,815 1084,093 1,094,434 $ 1,892,721 2,958,148 $ 4,034,229 5,121,071 $ 6,218,782 7,327470 $ 8,447,245 Desired End balance 50,00050,000 50,000 $ 50,000 $50,000 50,000 50,000 $ 50,000 50,000 $ 50,000 50,000 50,000 Collections prior sales $ 50,000 $ 50,000 $ 677,815 $ 1,084,093 $ 1,094,434 $892,721 $ 2,958,148 S 4,034,229 $ 5,121,071 $ 6,218,782 $ 7,327,470 $ 8,447,245 Cash after loan Cumulative loan 10000 $ 1,918,500 1918,500 1,290,685 256,592 $ 627,8151,034,093 50,000 50,000 S 837,842 $ 1,892,721 256,592 50,000 50,000 2,958,148 4,034,229 5,121,071 S 6,218,782 $ 7,327A70 $ 8,447,245 Ending cash P 13-6 Sminformulas P 13-7 P 13-7e P14-8P149 P18-5 Problem 9-5 P13-6 Simn Average: 2115532942 Count 192 Sum 32790760
