The MNO Company is a cosmetic retailer and the CEO has asked your assistance in compilation and analysis of the budgeted financial statements for the financial year ending on December 31, 2022.The following information is available:

• Cash receipts in the month of January for facial products sales is budgeted at R220,000 and is expected to grow with 1% each month.

• Cash receipts of body products are a function of facial product receipts at a rate of 30%.
• Cash sales of vitamins will commence in April 2022 at a budgeted R50,000 per month for three months, and thereafter is expected to double due to a promotion campaign with a once off cost of R30,000.

• The bank balance on 1 January is R48,000.

• Wages in January will amount to R65,000. A planned retrenchment will lead to a saving on the wage bill of R7,000 per month from July onwards. In December, all employees receive a thirteenth cheque equal to one month's wages.

• Rent to be paid is R30,000 per month. The rental lease is up for renewal on 1 November and a 5% increase is expected.

• Water and electricity due and payable in January is R3,050. Apart from a special once-off electricity levy of R1,000 in May, no price hike is expected.

• Cash purchases is 70% of estimated monthly sales receipts.

• Repairs and maintenance incurred and payable in January is R5,000 and is expected to decline by 5% per month.

• A delivery van will be purchased for R400,000 on 31 December 2022. A R70,000 deposit will be paid on that date, with the balance due on 31 January 2023.
• Unpaid credit sales at 31 December 2022 is estimated at R100,000 and at 31December 2021 amounted to R30,000. The unpaid credit sales at 31 December 2021 will all be received during January 2022 and is included in the January cash receipts mentioned above.
• Stock (inventory) on hand on 31 December 2022 will be value at was R60,000 and at 1 January 2022 R40,000.
• The tax rate is 28%. Income tax has not been paid yet. Ignore VAT.
• The book value of other equipment on balance sheet date will be R650,000. Ignore depreciation.
• Credit purchases to the amount of R5,000 will be delivered to MNO Cosmetics on December 30th 2022, but only paid on 15 January 2023. These purchases are not included in the normal cash purchases above.
• The share capital will remain unchanged at R500,000 for the period and consist of 500,000 R1 ordinary shares.
• A dividend of R20,000 will be declared on 31 December 2022 and paid on 31 January 2023.
• Apart from the information herein contained, no other amounts will be unpaid on 1 January 2022 or 31 December 2022.
• The retained earnings balance on 31 December 2021 was to be R268,000.
• 2021 ratio's were as follows:
  • RoE: 10%
  • D:E: 50%
  • GP%: 35%
  • NP%: 5%
  • EPS: 20 cents
  • DPS: 3 cents
  • Dividend cover: 5
  • Current ratio: 1,5
  • Quick ratio: 1

Required:

1. Compile a budgeted cash flow statement for 12 months for the year 1 January 2022 - 31 December 2022 (each month to be shown separately and a total column included)
2. Compile a budgeted income statement for the year 1 January 2022 - 31 December 2022.
3. Compile a budgeted balance sheet as at 31 December 2022.
4. Calculate the corresponding ratio's for 2022 and interpret these as compared to the ratio's provided for 2021.

Your answer must be completed on an excel document and submitted in excel format. All formulas and calculations should be included in the cells- no marks will be given for answers unsupported by calculations.