n fo
REWIR ECI
You areare required to produce an excel workbook that contains the following reports on separate worksheets:
1. FINANCIAL REPORTS (25 marks)
• A detailed cash budget for the first 6 months of trading (analysed into months) which highlights the expected bank/cash balances at the end of each month and at the end of the period. You should also show a total column for the six month period.
• A bar chart illustrating the closing cash position per month
• Detailed working capital budgets for the first 6 months of trading for:
o Trade Receivables
o Trade Payables (including materials and packaging)
o Inventories of Finished Goods (in Quantity)
As with the cash budget, you should show a total column for each of your working capital budgets.
Note - you are not required to produce a detailed income statement and statement of financial position for the first 6 months of the business.
2. PREDICTED PRODUCT COSTS AND PROFIT FORECASTS (20 marks)
• A detailed product cost breakdown, showing variable costs per unit, selling price per unit, contribution per unit and total fixed costs for the 6 month period.
• A table showing the expected profit or loss of the business for 6 months at:
o the expected level of activity
o the level of activity which exceeds your expectations (i.e. a best case)
o the level which is some way below your expectations (your worst case) and the quantity of products that need to be sold in order to break-even
o Margin of safety as a number of units, and as a percentage.
Note: the best case and worst case fluctuations should be between 10-30% either side of the expected level. Within these limits, you pick the percentage fluctuation that you think is most appropriate to your business and justify the choice of percentage in your written summary. The coursework briefing lecture will discuss this point further.
• A break-even chart that shows: total revenues; total costs; total fixed costs; total variable costs; the expected break-even point and margin of safety clearly labelled .
• A pie chart to show, at expected sales levels, the projected split of sales by geographic region.
AFM Study Guide: Term 1
71
A Passion for Fashion
.---------A Passion for Fashion
3. A WRITTEN SUMMARY (750 words maximum), DESCRIBING THE BUSINESS AND IDENTIFYING KEY FIGURES FROM THE ACCOUNTING REPORTS YOU HAVE PRODUCED (25 marks)
The Summary should be prepared in Word using the template provided to you on NOW. Use bullet points /subheadings to separate the different sections of your report. You are required to start with a brief introduction to your product and your market. Then:
Pricing
8. Justify the selling price you have selected with reference to competitors and existing retail prices on similar products. Be precise with your sources. (2 marks)
9. Import the pie chart of potential regional sales to illustrate which regions you should focus your marketing on. (1 mark)
Profitability and risk
10.Evaluate the business risk in terms of the expected profit figures you predict, Explain the percentages you have assumed as best case and worst case scenarios with your reasons for selecting those percentages (eg what kind of risks could impact your sales projections?). (2 marks)
11.Comment on margin of safety, and the sensitivity of profit to changes in costs either variable or fixed. Illustrate this with some sensitivity analysis (=What-if analysis) on 2 key cost figures. (4 marks)
12.Suggest 1 way in which the company could reduce its financial risk / increase its profit numbers from where it currently stands. Illustrate the impact of your suggestion with What-if analysis. (2 marks)
Liquidity
13.Analyse the key aspects of the cash budget prepared for the first six months of trading and identify the nature and amount of additional finance you would recommend that the company should raise, and when they would need it. (2 marks)
14. Illustrate your commentary by including the bar chart indicating monthly cash position. (1 mark)
Working Capital
15.Comment on the levels of working capital the business is operating with. (1 mark)
16. Highlight 3 ways in which your business could improve their working capital position. (3 marks)
17.Use what if analysis to show the potential impact on cash balances of implementing 1 of these recommendations. (2 marks)
Other cash recommendations
18. Discuss at least 2 further ways the business could improve its cash balances (apart from more efficient use of working capital) (2 marks)
Conclusion
Conclusion: Do you believe the business is currently in a position to start trading using its existing financial plan, or do you recommend
implementation of some of your suggestions above before submitting the loan application to the bank? (1 mark)
Good presentation and report style (including introduction), clear use of English. (2 marks)
AFM Study Guide: Term 1
72
In
If es re.
-fl
th
I-1 tt
N c i! I
ion for Fash. A Passion for Fashion
--------0 ---
Ied to you on I EXCEL SKILLS (30 marks)
ions of your
uct and your In determining the mark for the coursework you will be assessed on how you have demonstrated your excel IT skills in producing the above reports. In particular your completed coursework should apply the following skills: petitors and - Creation and linking separate worksheets
E BUSINESS Note that the marker will neither consider nor award marks for anything
RTS YOU written beyond the 750 word limit.
sources. (2 - Completion of a data sheet linked to other worksheets
- Use of VLOOKUP
regions you - Use of appropriate formulae for all calculations
- Formatting of cells for a good standard of presentation
- Use of IF function and Conditional Formatting
- Creation and labelling of a Break-even graph
(ou predict. - Illustration of cash data using bar chart by month and sales split using pie
worst case j chart by geographical location
hat kind of
t
If you set up the spreadsheet correctly, you should find that if you change any of your
es in costs estimates - -what-if- changes - (on the data sheet) the spreadsheet will automatically
(=What-if recalculate all of the profit forecasts and financial reports in your workbook.
ncrease its
I
ct of your First Steps
IllThe first step is to open the Excel file Passion for Fashion.xlsx and save it using a name
months
that incorporates your student number.
rou would
red it. (2 Have a look at the data sheet and compare the information there (in the green cells) with that outlined in this coursework brief so that you understand exactly what it means.
ithly
ith. (1 :apitai
ances
) start
emend
ig the
se of
Next you should look at the Individual Data set on your excel file and use VLOOKUP to collect the additional information that is unique to you and complete the cells highlighted in yellow in the data sheet. You will be marked on your use of VLOOKUP formulae.
Note, if you are unable to use VLOOKUP formulae it is possible to manually type in your allocated data from the dataset, BUT you will lose the marks for use of VLOOKUP formula.
Thirdly, having done your research into your product, add your company name, product, and the figures you have researched: selling price, and material cost per unit (into the orange cells).
Then you are good to go. You can start with any further calculations, and setting up your financial reports and analyses on separate pages as per the requirements.
Open up the Word document Passion for Fashion Report.doc and save it using a name which incorporates your student number.
Use this template to create your business report, which should be submitted at the same time, and to the same dropbox as your Excel file.
Jl
n n
-You know you can do it...
Good Luck!
72 AFM Study Guide: Term 1
73