Prepare a cash budget from the given data using a spreadsheet. The directors of Kingston & Co.
Question:
Prepare a cash budget from the given data using a spreadsheet.
The directors of Kingston & Co. were concerned about the company’s cash flow. They requested their accountant to prepare a cash budget for the four months ending 30 April 2022
The following sales figures are for the months of November 2021 to June 2022. The figures from January 2022 onward are estimated:
Actual sales | $ |
November 2021 | 60,000 |
December 2021 | 64,000 |
Forecast sales | |
January 2022 | 65,000 |
Feb 2022 | 70,000 |
March 2022 | 72,500 |
April 2022 | 76,250 |
May 2022 | 80,000 |
June 2022 | 78,750 |
Half the sales are normally paid for in the month in which they occur and the customers are rewarded with a 5% cash discount. The remaining sales are paid for net in the month following the sale.
(ii) Goods are sold at a mark-up of 25% on the goods purchased one month before sale. Half of the purchases are paid for in the month of purchase and a 4% prompt settlement discount is received. The remainder is paid in full in the following month.
(iii) Wages of $12000 per month are paid in the month in which they are earned. It is expected that the wages will be increased by 10% from 1 March 2022
(iv) Rent will cost $60000 per annum payable three monthly in advance in January, April, July and December each year.
(v) The directors have arranged a bank loan of $60000 which would be credited to company’s current account in February 2022.
(vi) The half-yearly interest on 200000, 8% debentures of $1 each is due to be paid on 15 January 2022.
(vii) The ordinary dividend of $12000 for the year 2015 will be paid in March 2022.
(viii) The bank balance at 31 December 2021 is $12000.
(please upload the picture of the answer clearly)
Fundamentals Of Financial Management
ISBN: 9780273713630
13th Revised Edition
Authors: James Van Horne, John Wachowicz