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, 2021.The following information is available:

• Sales of facial products in January 2021 is budgeted at R220,000 and is expected to grow with 1% each month.

• Sales of body products are a function of facial product sales at a rate of 30%.
• Sales of vitamins will commence in April 2021 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 months wages.

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

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

• Purchases is 70% of estimated monthly sales.

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

• A delivery van will be purchased for R400,000 on 31 December 2021. A R70,000 deposit will be paid on that date, with the balance due on 31 January 2022.
• Unpaid credit sales at 31 December 2021 is estimated at R100,000 and at 1 January 2021 amounted to R30,000.
• Stock (inventory) on hand on 31 December 2021 will be value at was R60,000 and at 1 January 2021 R40,000.
• The tax rate is 28%. Tax has not been paid yet.
• The book value of other equipment on balance sheet date will be R650,000. Ignore depreciation.
• Purchases to the amount of R5,000 will be delivered to MNO Cosmetics on December 30th 2021, but only paid on 15 January 2022.
• 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 2021 and paid on 31 January 2022.
• Apart from the information herein contained, no other amounts will be unpaid on 1 January 2021 or 31 December 2021.
• The retained earnings balance on 31 December 2020 was R268,000.
• 2020 ratios were as follows:
  • RoE: 10% o D:E: 50% o GP%: 35% o 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 2021 31 December 2021 (each month to be shown separately and a total column included)
2. Compile a budgeted income statement for the year 1 January 2021 31 December 2021 and a budgeted balance sheet as at 31 December 2021.
3. Calculate the corresponding ratios for 2021 and interpret these as compared to the ratios provided for 2020.

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.