Excel question Probelm 12.4 Create a 12-month cash flow budget in excel using th
ID: 2452896 • Letter: E
Question
Excel question
Probelm 12.4 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
Excel Question
a) Expand the cash flow budget you created in Problem12.4 to include a row for expected cash outflows equal to 77% of the current months'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 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 f) Add appropriate data validation controls to ensure spreadsheet accuracy
Explanation / Answer
Statement showing Cash budget Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sales 5,000,000.00 5,050,000.00 5,100,500.00 5,151,505.00 5,203,020.05 5,255,050.25 5,307,600.75 5,360,676.76 5,414,283.53 5,468,426.36 5,523,110.63 5,578,341.73 Collected in Same Month @40% (a) 2,000,000.00 2,020,000.00 2,040,200.00 2,060,602.00 2,081,208.02 2,102,020.10 2,123,040.30 2,144,270.70 2,165,713.41 2,187,370.55 2,209,244.25 2,231,336.69 Collected in next Month @30% (b) 1,500,000.00 1,515,000.00 1,530,150.00 1,545,451.50 1,560,906.02 1,576,515.08 1,592,280.23 1,608,203.03 1,624,285.06 1,640,527.91 1,656,933.19 Collected in next to next Month @20% © 1,000,000.00 1,010,000.00 1,020,100.00 1,030,301.00 1,040,604.01 1,051,010.05 1,061,520.15 1,072,135.35 1,082,856.71 1,093,685.27 Collected in 3 Month @8% (d) 400,000.00 404,000.00 408,040.00 412,120.40 416,241.60 420,404.02 424,608.06 428,854.14 433,142.68 Cash OutFlows @77% of CM Sales (e) 3,850,000.00 3,888,500.00 3,927,385.00 3,966,658.85 4,006,325.44 4,046,388.69 4,086,852.58 4,127,721.11 4,168,998.32 4,210,688.30 4,252,795.18 4,295,323.13 Net Cash Flows = a+b+c+d-e (1,850,000.00) (368,500.00) 627,815.00 1,034,093.15 1,044,434.08 1,054,878.42 1,065,427.21 1,076,081.48 1,086,842.29 1,097,710.72 1,108,687.82 1,119,774.70 Opening Cash Balance 350,000.00 50,000.00 50,000.00 50,000.00 50,000.00 837,842.23 1,892,720.65 2,958,147.86 4,034,229.34 5,121,071.63 6,218,782.35 7,327,470.17 Closing Cash Balance before Borrowing (1,500,000.00) (318,500.00) 677,815.00 1,084,093.15 1,094,434.08 1,892,720.65 2,958,147.86 4,034,229.34 5,121,071.63 6,218,782.35 7,327,470.17 8,447,244.87 Borrowings to maintain min Cash balance 1,550,000.00 368,500.00 - - - - - - - - - - Repayment of Borrowed Loan 627,815.00 1,034,093.15 256,591.85 - - - - - - - Closing Cash Balance after borrowing 50,000.00 50,000.00 50,000.00 50,000.00 837,842.23 1,892,720.65 2,958,147.86 4,034,229.34 5,121,071.63 6,218,782.35 7,327,470.17 8,447,244.87 Total Borrowing 1,550,000.00 1,918,500.00 1,290,685.00 256,591.85 - - - - - - - -
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.