@final aspen As the accountant of Rainbow Ltd, you are required to prepare a cash budget for the months of January, February, and March 2022. You have gathered the following information:
(1) Cash balance at the end of December 2021 is $166,000.
(2) Actual revenue for November and December 2021 and projected revenue for January, February, and March 2022 are as follows:
Month ending
30 November 2021 31 December 2021 31 January 2022 28 February 2022 31 March 2022
$ $ $ $ $
Cash sales 61,700 76,100 62,000 68,900 54,000
Credit sales 125,000 139,000 105,000 140,000 138,000
Total sales 186,700
215,100 167,000 208,900 192,000
(3) Analysis of past records has shown that credit sales are collected over a three-month period, with 30% being collected in the month of the sale, 50% in the next month, and the remainder the following month.
(4) Actual cost of sales and purchases for November and December 2021 and projected cost of sales and purchases for January, February, and March 2022 are as follows:
Month ending
30 November 2021 31 December 2021 31 January 2022 28 February 2022 31 March 2022
$ $ $ $ $
Cost of sales 112,020 107,550 100,200 104,450 96,000
Credit purchases 113,020 111,550 101,200 106,450 98,000
(5) Rainbow Ltd typically pays its inventory suppliers in the month following the purchase.
(6) Projected expenditure during January, February, and March 2022 is as follows:
Operating expenses are budgeted to be $55,000 each month. Operating expenses are usually incurred and paid for in the same month, except that the utilities bill (estimated to be $1,800) for January 2022 will not be paid until February 2022.
A new equipment will be purchased for $108,000, with a $32,400 cash payment in January and the balance to be paid in March. The equipment will be depreciated using straight-line method, over a period of five years. The residual value is estimated to be $13,000.
(7) Rainbow Ltd plans to sell an old machinery on February 2022 for $12,800. Proceeds arising from the sale is expected to be received in the following month.
IMPORTANT:
For payments, please do not include the - (minus) sign in front of the amounts and also do not put the amounts in parentheses.
The - (minus) sign should only be used in front of any amounts that are negative in the last three rows of the cash budget (i.e., Excess/(deficit) receipts over payments, Bank balance at the beginning of month, Bank balance at the end of month). Please do not put the negative amounts in parentheses.
If you think the amount in a cell should be 0, type 0. Please do not leave that cell empty.
For any amounts, please do not include the dollar sign ($) or any thousands separator - i.e., $12,000 should be written as 12000.
REQUIRED:
Complete the schedule of receipts from accounts receivable and cash budget for the 3 months ending 31 March 2022. (11 marks)
Rainbow Ltd
Receipts from Accounts Receivable in Month Ending
Credit Sales 31 January 2022 28 February 2022 31 March 2022
$ $ $ $
30 November 2021 125,000 ? ? ?
31 December 2021 139,000 ? ? ?
31 January 2022 105,000 ? ? ?
28 February 2022 140,000 ? ? ?
31 March 2022 138,000 ? ? ?