• 2-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
• Add a "spinner" to your spreadsheet that will enable you to easily change forecasted monthly sales growth to range from 0.5% to 1.5% in increments of 0.1% (see the link and video from Journal of Accountancy on spinners).
• Add a scroll bar to your spreadsheet that will let you modify the amount of initial sales to vary from $4 million to $6 million in increments of $100,000.
• row for expected cash outflows equal to 77% of the current month's sales
• 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
• Add another row to show the cash inflow from borrowing
• Add another row to show the cumulative amount borrowed
• 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 moth
• User data entry should be restricted to the cells that contain the initial assumptions. All other cells in the spreadsheet should be locked.