Budget Report for Gaia Ltd.

You are required to produce a budget report, complete with analysis and commentary, on a given scenario.

The report consists of THREE compulsory parts.

• You MUST submit an Excel spreadsheet with your report to demonstrate your Excel skills.
• Students who fail to submit an Excel spreadsheet, or who only provide a screenshot of Excel within their reports, will not achieve full marks.

Gaia Ltd

Assessment Scenario

Gaia Ltd is an established vehicle manufacturer operating across the UK and Europe. The directors of Gaia Ltd are considering expanding manufacturing of their new Electric Vehicle (EV), named the Gaia Niche, over the next 12 months.

PART A (35% weighting)                                        

The directors of Gaia Ltd have asked you, as a recently employed assistant management accountant, to prepare and review their cash budget. Their bookkeeper started the cash budget but was unable to complete it due to ill health.

PART B (40% weighting)

The Directors of Gaia Ltd are considering expanding manufacturing of their new Electric Vehicle (EV), named the Gaia Niche, over the next 12 months. Gaia Ltd have asked you to calculate and compare ratios for 2019 and 2020 before making a recommendation as to whether to proceed with expansion plans or not.

You are required to:

1. Use your Excel skills to calculate the following ratios for both years.

You should then present your calculated ratios within your report in a simple table format for ease of comparison. (15% weighting)

NOTE:  To achieve full marks you MUST submit an Excel spreadsheet with your report (not a screenshot) to show your workings and demonstrate your Excel skills, e.g. cell referencing and formula.

1. Gross profit margin
2. Operating profit margin
3. Return on capital employed
4. Asset turnover
5. Current ratio
6. Acid test
7. Inventory days
8. Trade receivable days
9. Trade payable days
10. Working capital cycle/operating cycle
11. Gearing
12. Interest cover
    Compare the performance for both years in the 4 key areas of profitability, efficiency, liquidity and gearing using the ratios calculated in part 1 and make a recommendation as to whether Gaia Ltd should proceed with expanding manufacturing of their new EV, Gaia Niche. Marks will be awarded depending on the depth of discussion. (25% weighting)

PART C (25% weighting)

Gaia Ltd has experienced fluctuating consumer demands since the beginning of the Covid-19 pandemic but are cautiously optimistic now that restrictions appear to be easing.

Their latest product is a new Electric Vehicle (EV) called the Gaia Niche and have a supply chain through which they import components and EV batteries from Europe. The following information is available for the year ending 31^st Dec 2022.

1. 1.    Due to fluctuating and unpredictable consumer demands, you have been asked to use Excel to prepare a flexible sales budget for quantities of 25,000 units and 35,000 units. Sales revenue is fully variable. (10% weighting)

NOTE:  To achieve full marks you MUST submit an Excel spreadsheet with your report (not a screenshot) to show your workings and demonstrate your Excel skills, e.g. cell referencing and formula.

1. Identify and discuss specific issues that could impact both the manufacturing and production of the Gaia Niche EV, and consumer demand for the product. Issues should be in context with the given scenario and current economic climate. You should conduct appropriate research using academic sources to generate ideas and discussion. Marks will be awarded depending on the depth of discussion. (15% weighting)