Reference no: EM132462978
Cash Budget Question -
Geetha Ltd has given the following information on estimated costs and forecast revenue for two months of November and December 2018 and three months in the year 2019 (January to March):
|
|
2018
|
2019
|
|
Nov
|
Dec
|
Jan
|
Feb
|
Mar
|
|
Sales Qty (units)
|
6,200
|
8,500
|
6,600
|
8,800
|
6,800
|
|
Selling Price/unit ($)
|
30
|
28
|
30
|
28
|
30
|
|
Expenditure:
|
|
|
1) Salaries ($)
|
20,000
|
25,000
|
26,000
|
27,000
|
28,000
|
|
2) Commission
|
10% of Sales
|
|
3) Utilities ($)
|
5,000
|
5,800
|
5,200
|
6,800
|
5,500
|
|
4) Advertisement ($)
|
3,000
|
3,000
|
4,600
|
4,400
|
4,200
|
|
5) Distribution ($)
|
2,000
|
3,500
|
2,900
|
3,600
|
3,000
|
Additional information is given below:
1. The month of December and February are festive seasons.
2. Receipts from Sales:
(a) 30% of the total sales is on Cash basis and the balance 70% is on credit basis.
(b) Credit customers will pay in the following manner:
- 30% in the month of sale (current month)
- 40% in the month following sales (next month)
- 30% will be paid in 2 months after sale (next, next month)
3. Monthly purchases of finished goods from their credit supplier represents 70% of the next month's sales and payments to trade creditors are made two months after purchases.
4. 10% of the purchases of a month will be the opening inventory for following month.
5. All other expenses are paid in the following manner:
- Salaries expenses are paid in the same month of incurrence.
- Bonus was paid in the month of February 2019 equivalent to 150% of December's salary.
- Commission is paid in arrears by two months.
- Utilities and Distribution expenses are paid in arrears by one month.
- Advertisement is paid one month in advance.
6. A down payment of $20,000 will be paid at the end of February 2019 for a new robotic machinery and there-after, a monthly installment of $6,000 for the next 10 months.
7. Mrs. Geetha, the owner of the business, will invest an additional $100,000 cash into the business in the month of February 2019.
8. As at 31 December 2018, the bank account in the company's books showed a credit balance of $20,000.
Required -
1. Prepare the Cash Budget for each of the two months from 1st January to 28th February 2019 using Excel spreadsheet.
2. Prepare the Budgeted Profit and Loss Account for the two months ending 28th Feb 2019 using Excel spreadsheet.
Note: All figures to be rounded-up to the nearest dollar (no decimal point). All workings must be shown clearly.