PS2- Excel Assignment 2020-21
As the newly appointed Financial Analyst for Sparkle International Ltd, you will be responsible for providing forecasted financial information to the Finance Director. The company has been trading for many years in the manufacturing and resale of wholesale seasonal items.
However, following the UK’s decision to leave the European Union (Brexit), the company is gearing up to achieve a greater worldwide presence to ensure positive financial performance in the years to come. The company appreciates that entering new markets is not easy and that short-term financial performance may be affected. The company believes it will need to commit appropriate levels of finance to promote and advertise its products in the run up to the relevant festive periods, in order to attract new business and gain market share.
Over the first three months of the forecasting period, the company will incur the following costs (Jan, Feb and Mar 2021):
Jan 2021 Feb 2021 Mar 2021
£ £ £
R&D 800,000 1,100,000 2,150,000
Production Machinery 3,000,000 2,000,000 4,500,000
Warehouse 1,500,000 2,875,000 2,380,000
Advertising (UK) 430,000 475,000 550,000
Advertising (ROW) 500,000 450,000 510,000
The following initial variable costs of production per wholesale quantity unit are attached to the product:
£
Direct Materials 20.0
Direct Wages 10.0
Variable Production Overhead 7.0
Other Financial Information
- The selling price of the new seasonal range is £41.00 per wholesale quantity unit.
- A market research company has completed a study of the target markets and believes that a level of 92,000 units can be sold in January 2021. By looking at sales patterns for previous products, the research company anticipates demand rising by 9% month on month for the first year of trading, with this level of increase then tailing off to just 3% per month in the second year.
- The company is expected to incur fixed production costs in the region of £1,600,000 per annum for the first year. These costs will be paid quarterly. The costs are expected to rise by 30% for the second year.
- Direct Materials and Variable Production Overhead will be paid monthly in arrears.
- Direct Wages will be paid in the month they are incurred and will be subject to an inflationary increase of 6% for Year 2.
- The company has purchased enough machinery to produce up to 200,000 units per month. However, it is believed that sales will rise past that level, therefore in the month that sales rise past 200,000 units, the company will need to purchase and pay for additional machinery costing £8,000,000.
- All sales will be settled at the time, with customers paying for their goods in the month of sale.
- The company has negotiated a £2,500,000 overdraft limit with the bank. This limit can be broken if necessary but with penalties! If the company has a negative cash balance up to the agreed overdraft limit, interest will be incurred at -1.3% per month payable in the following month. If the company breaks the overdraft limit of £2,500,000 then the interest charged increases to -3.6% on the whole overdrawn balance. Positive cash balances attract interest at 1.0% per month, receivable in the following month.
- The company has secured funding via a cash investment through a venture capitalist trust of £16,500,000. The funds are to be made available immediately.
REQUIREMENTS
- Produce a monthly Cash Flow Forecast in Excel for the 24 months commencing Jan 2021 using all of the information given above.
- Utilise Buttons / Macros to navigate around your spreadsheet and incorporate IF statements (to help with the calculation of bank interest and new machinery costs).
- Incorporate COUNTIF and SUMIF functions respectively, to determine for how many months the Closing Bank Balance/Net Cash Flows are negative, and to calculate total value of these negative Net Cash Flows.
- Produce a simple Pivot Table capable of analysing the Company’s StartUp costs
- Provide graphical analysis of the Closing Balances and Net Cash Flows.
- If the cash balance at the end of the period is negative, provide a simple WhatIF analysis using the GoalSeek option to determine what the Selling Price, Direct Costs and Initial Funding might need to be, in order to generate a positive cash balance. If your cash balance is positive, use the same GoalSeek function to arrive at a target positive cash balance set by yourselves.
(….cont)
You are required to follow the skeleton format below for the Cash Flow Forecast:-
Cash Flow Forecast – Sparkle International Ltd
for the 24 months Jan 2021 – Dec 2022 (show each month in parallel to the previous month)
INFLOWS
__
__
__
TOTAL INFLOWS A
OUTFLOWS
__
__
__
TOTAL OUTFLOWS B
NET CASH FLOW A-B
OPENING BANK BAL C
CLOSING BANK BAL (A-B)+C
Performance will be achieved from:
Good spreadsheet layout and formatting (including use of macros and buttons).
Appropriate use of formulae and functions in the spreadsheet.
Appropriate calculations and presentation of information required.
Suitable graphical presentation.
HINTS
Use a separate worksheet for each of the Data, Calculations and Output sections, within the one single workbook, although you can use just one sheet if you prefer. Formulae should only appear in the Calculations and Output sections with raw numbers appearing only in the Data section
When working out certain items to go into the Cashflow forecast, you may find it simpler to do the calculations in stages rather than trying to do the whole calculation using one formula. There is no problem with using a workings/calculations section.
