Task 5 - The Financials
Drawing on what you know of the business from the Case Study - Gin Rummy Distillery, and the work you have done on the Business Plan, you now need to establish Financial calculations for the business.
It's now April.
The Jan- Mar quarter of the finances has been completed for you based on actual figures in The Finances spreadsheet. You are to refer to these to make projections for the rest of thecurrent financial year (Apr-Jun)as well asprojections for the following financial year.
Use this Template for The Financials
-
Task 5 The Finances Spreadsheet.xlsx
Download Task 5 The Finances Spreadsheet.xlsx
DO NOT CONVERT TO PDF WHEN UPLOADING PLEASE:
YOU MUST HAVE Microsoft Xcel or Mac Numbers to complete this task.
You are to work in the 2nd tab of the spreadsheet ONLY -the one called'Exp Cash Flow'.
Your task is to aim for aturnover of $2.5M in the next financial year (July-June). This is achievable with15% growth for each quarter.
Lets racap on the plans and expected impacts on Finances these phases may have had:
ForPhase 1, When the gin is introduced to the market, Paul expects:
·
$50,000 in sales for each of the months of April, May and June
.
·
You are to project anincrease of 15% for each quarterof each liquor thereafter. Paul assures you that the current distillery equipment can produce and dispatch 60,000 litres over the next 15 months - aged, bottled and ready to go.Ensure that you do not factor more than 60,000 litres of alcohol.
ForPhase 2, with financial backing and larger scale distilling systems, productivity is set to increase in future years.
As distilling takes time and rum must be aged, the benefits of larger scale distilling system are out of scope at this time.
You don't have to consider the higher productivity from a larger distillery plant.
Your goal is to achieve funding in the very near future on projections for the coming financial year.
With the introduction of 30,000 litres of the gin to market as well as 30,000 litres of rum, you should be able to reach it.
ForPhase 3, and again with the funding sought, the Cellar Doors, gift shop and restaurant will open on the1st October.
Cashincomingwill increase and, with the extra staff and running costs, so will the cashoutgoing.
You should make projections that reflect both.
YOUR TASK: using the template given above:
·
Populate the income from April onwards.
·
Ensure that theTotal Incomingin theExpected Cash Flowworksheet for the 12 months from July to June (the sum of H12:S12) isclose to and at least $2.5M.
·
This is the target turnover for the financial year (July to June). You have 60,000 litres of spirits to sell over the next 15 months.
·
Populate the expenses in theExpected Cash Flowworksheet from April onwards from what you can gather from theCase Study- Gin Rummy Distillery, orfrom your independent research. You can estimate the outgoings based onprovided costs. (tab 1 of the spreadsheet).
·
Ensure that the cash incoming for the financial year (July to June)is greater than the total outgoing(the sum of H35:S35).
As you can see from the completedStart-up and Equipment Capital(TAB 1 in the spreadsheet) in The Finances workbook, the business put $500,000 start up capital into the bank and needs to borrow approximately $6M to implement Phases 2 and 3.
If you can demonstrate that the business willturn over $2.5M in the next financial year, you have an argument for a $6M investment.
(There is no expectation for you to achieve 100% in the calculation of the figures in the spreadsheet, although if the forumla's are working this should occur. 80% accuracy is deemed competent in this task)