Question

This assignment will require you to prepare the cash budget and determine the cash surplus and shortage each month. The management estimates total sales for the period January through June based on actual sales from the immediate past six months. The following assumptions are made:

1. The Sales were $150,000 in January 2018 and then the sales grew by 10% each month for the first five months (February to June). The sales are expected to grow by 5% each month thereafter.

2. 50% of the Sales are collected in the same month. 45% of the sales are collected in the following month and remainder are not collected.

3. The Purchases are 20% of sales and paid in the same month.

4. Wages and Salaries are $10,000 each month and paid in the same month.

5. Depreciation expense is $5,000 each month.

6. An equipment worth $100,000 will be purchased with cash in October.

7. The company’s debt is $50,000 and the company pays coupon payments in June and December of each year. The coupon rate is 10% per year.

8. Rent Expenses will be $5000 and will be paid at the end of each calendar quarter.

Determine the cash surplus and shortages for each month from July to December. Provide your analysis.

Answer 1

Calculation of the cash surplus and shortages for each month from July to December
Months	July	August	September	October	November	December
Cash receipts from sales	$235,537.09	$247,313.94	$259,679.64	$272,663.62	$286,296.80	$300,611.64
Less : Payments
- Paid for Purchases	$50,731.07	$53,267.62	$55,931.00	$58,727.55	$61,663.93	$64,747.12
- Paid for Wages and salaries	$10,000.00	$10,000.00	$10,000.00	$10,000.00	$10,000.00	$10,000.00
- Paid for Equipment				$100,000.00
- Coupon payment on debt						$2,500.00
- Paid for Rent			$5,000.00			$5,000.00
Total Cash Payments	$60,731.07	$63,267.62	$70,931.00	$168,727.55	$71,663.93	$82,247.12
Cash Surplus / (deficit)	$174,806.02	$184,046.32	$188,748.64	$103,936.07	$214,632.88	$218,364.52
Working
Calculation of sales and purchases From January 2018 to December 2018
Month	Sales	Purchases (20% of sales)
Jan-18	$150,000.00	$30,000.00
Feb-18	$165,000.00	$33,000.00
Mar-18	$181,500.00	$36,300.00
Apr-18	$199,650.00	$39,930.00
May-18	$219,615.00	$43,923.00
Jun-18	$241,576.50	$48,315.30
Jul-18	$253,655.33	$50,731.07
Aug-18	$266,338.09	$53,267.62
Sep-18	$279,655.00	$55,931.00
Oct-18	$293,637.75	$58,727.55
Nov-18	$308,319.63	$61,663.93
Dec-18	$323,735.61	$64,747.12
Calculation of collection from sales
	July	August	September	October	November	December
50% of Current Month sales	$126,827.66	$133,169.05	$139,827.50	$146,818.87	$154,159.82	$161,867.81
45% of Previous month sales	$108,709.43	$114,144.90	$119,852.14	$125,844.75	$132,136.99	$138,743.83
Total Cash receipts from sales	$235,537.09	$247,313.94	$259,679.64	$272,663.62	$286,296.80	$300,611.64