This assignment will require you to prepare the cash budget and determine the ca
ID: 2525874 • Letter: T
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: Historical Sales | July, 2016 $100,000 August, 2016 $125,000 September, 2016 $105,000 October, 2016 $155,000 | November, 2016 $ 60,000 December, 2016 | $ 70,000 Forecasted Sales January, 2017 $110,000 February, 2017 $ 90,000 March, 2017 $120.000 April, 2017 $ 80,000 May, 2017 $ 70,000 | June, 2017 $ 60,000 a. 50% of the Sales are collected immediately. 30% of the Sales will be collected one month after the sale. 10% will be collected two months after the sale. 6% will be collected three months after the sale. The remainder will be collected four months after the sale. Bad debts are insignificant. b. Purchases were $50,000 in July, 2016 and are expected to grow by 2% each month. The purchases will be paid in the same month. c. Wages and salaries of $30,000 will be paid each month. d. Depreciation expenses are $20,000 each month. e. Rent of $15,000 will be paid at the end of each calendar quarter. f. Machinery worth $150,000 will be purchased in March. Prepare the cash budget from January to June. Determine the cash surplus and shortages for each month from January to June. Provide your analysis. O e AS WE para o TANESExplanation / Answer
Cash Budget Question
Cash Budget from January to June
Cash Budget
January
February
March
April
May
June
Cash collections
$95,500
$94,800
$94,800
$94,400
$80,800
$69,800
Cash disbursements:
Purchases
$56,308
$57,434
$58,583
$59,755
$60,950
$62,169
Wages and salaries
$30,000
$30,000
$30,000
$30,000
$30,000
$30,000
Rent
$15,000
$15,000
Machinery
$150,000
Total cash disbursements
$86,308
$87,434
$253,583
$89,755
$90,950
$107,169
Cash surplus/(shortage)
$9,192
$7,366
($158,783)
$4,645
($10,150)
($37,369)
Workings –
Cash collections :
January
February
March
April
May
June
September 2016 sales
$4,200
October 2016 sales
$9,300
6,200
November 2016 sales
$6,000
$3,600
$2,400
December 2016 sales
$21,000
$7,000
$4,200
$2,800
January sales
$55,000
$33,000
$11,000
$6,600
$4,400
February sales
$45,000
$27,000
$9,000
$5,400
$3,600
March Sales
$60,000
$36,000
$12,000
$7,200
April Sales
$40,000
$24,000
$8,000
May Sales
$35,000
$21,000
June Sales
$30,000
Total cash collections
$95,500
$94,800
$94,800
$94,400
$80,800
$69,800
Notes:
The cash collections are computed as follows,
The purchases are computed as follows –
The purchases posted an increase of 2% every month starting from August as follows,
July 2016 purchases = $50,000; August 2016 purchases = 50,000 + 2% = $51,000; September 2016 purchases = 51,000 + 2% = $52,020
October 2016 purchases = 52,020 + 2% = $53,060; November 2016 purchases = 53,060 + 2% = $54,122; December 2016 Purchases = 54,122 + 2% = $55,204
January 2017 purchases = 55,204 + 2% = $56,308; February 2017 purchases = 56,308 + 2% = $57,434; March 2017 Purchases = 57,434 + 2% = $58,583
April 2017 purchases = 58,583 + 2% = $59,755; May 2017 purchases = 59,755 + 2% = $60,950; June 2017 purchases = 60,950 + 2% = $62,169
Analysis of reasons for cash shortage in the months of March, May and June:
The main reason for cash shortage in the month of March is the purchase of equipment at $150,000.
The cost of equipment is high and the company should be better off opting a financing plan rather than making cash payment.
Also, the collections from sales are spread over a 4- month period with varying percentages of collections. However, the purchases are paid entirely in the month of expense. The optimal approach for the company would be to adopt a distributed payment pattern similar to cash collections to lower the burden of payment in a single month.
Also, the rent expense is paid once in quarterly, the same can be divided over the three month period to reduce the cash outflow in the months of March and June.
The company can also negotiate a line of credit with a bank to address the frequent cash shortages.
Cash Budget
January
February
March
April
May
June
Cash collections
$95,500
$94,800
$94,800
$94,400
$80,800
$69,800
Cash disbursements:
Purchases
$56,308
$57,434
$58,583
$59,755
$60,950
$62,169
Wages and salaries
$30,000
$30,000
$30,000
$30,000
$30,000
$30,000
Rent
$15,000
$15,000
Machinery
$150,000
Total cash disbursements
$86,308
$87,434
$253,583
$89,755
$90,950
$107,169
Cash surplus/(shortage)
$9,192
$7,366
($158,783)
$4,645
($10,150)
($37,369)