Question

Step 1

Read the scenario.

Assume you are thinking about starting a business and would like to forecast your cash needs for the next six months. You expect sales to be approximately $30,000 per month for the first 12 months and your purchases to support sales will be approximately 60% of sales. You anticipate about 20% of your sales will be cash and 80% collected the following month. Your supplier has agreed to extend credit for 70 days at no cost (i.e. you will pay your bills on a two month lag). Your monthly expenses for rent, wages, utilities, and so forth will be approximately $3,500 per month. You have deposited $15,000 in a checking account to begin your operations and would like to maintain a minimum balance of $5,000 in that account.

Step 2

Create a simple monthly cash budget.

Create a spreadsheet file that contains a simple monthly cash budget for the next year for the business described in the above scenario.

Answer 1

Business forecast

Monthly cash budget as per the scenarios mentioned

Particulars	Initial	Jan	Feb	Mar	Apr	May	Jun
Sales		30000	30000	30000	30000	30000	30000
Collections for the sales
Cash @ 20%		6000	6000	6000	6000	6000	6000
Credit @ 80 % for next month			24000	24000	24000	24000	24000
Total (A)		6000	30000	30000	30000	30000	30000
Less payments
Purchases @ 60%		18000	18000	18000	18000	18000	18000
payment				18000	18000	18000	18000
rent and other admin expense		3500	3500	3500	3500	3500	3500
total (B)		24500	24500	24500	24500	24500	24500
cash available/ (required) (a-b)	0	(18,500)	5,500	5,500	5,500	5,500	5,500
Actual cash available	10000	10000	8,500	5,500	5,500	5,500	5,500
Cash to be(borrowed)		(8,500)	8500
Cash to be deposited			500	6000	12000	18000	24000
Cash to be maintained	5000	5000	5000	5000	5000	5000	5000