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 mont

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

Acemyhomework
Calculate your paper price
Pages (550 words)
Approximate price: -
Acemyhomework

Quality Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Acemyhomework

Qualified Writers

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

Acemyhomework

Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account.

Acemyhomework

Prompt Delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. We will always strive to deliver on time.

Acemyhomework

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text.

Acemyhomework

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Homework Market

Place your order

Fill in the order form and provide all details of your assignment.

Homework Market

Proceed with the payment

Choose the payment system that suits you most.

Homework Market

Receive the final file

Once your paper is ready, we will email it to you.

Acemyhomework Writing Services

No need to work on essay at night. Sleep tight, we will cover your back. We offer all kinds of essay writing services.

Acemyhomework Acemyhomework

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Acemyhomework Acemyhomework

Admissions

Admission Essays

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Acemyhomework Acemyhomework

Editing

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Acemyhomework Acemyhomework

Revision

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied.