A business development opportunity for the business
Management at Head Office of the business are considering a major extension to the Bristol store to be achieved by renting adjoining premises.
The objective is to create additional space for the store in which to develop the Clothing Department. The plan is to introduce in-store brand boutiques such as Calvin Klein, Maine, Principles, Miss Selfridge, Warehouse, Timberland, etc.
The Bristol management have made provisional estimates of a range of key operating variables as follows:-
• Boutique area will be 400 sq m, of which 60 sq m will be storage and office space.
• To re-design part of the store to incorporate the new premises etc. – a total cost of £150,000
• To fit out the new premises – £650 per sq m (to be applied to the full 400 sq m)
• Rent for the new premises will be £200 per quarter per sq m, subject to final negotiation.
• The cost of the Utilities (electricity and water) will be £15,000 per quarter.
• It is planned to open the business boutique for 10 hours per day on average and for 90 days per quarter. As well as access through the store, there will be a separate entrance to the new premises so the boutique is not dependent on the opening hours of the main store.
• It is anticipated that the maximum takings per hour will be £2.50 per sq m
• The costs of sales will be about 55% of the retail price, on average.
• Miscellaneous expenses are expected to amount to about £12,500 per quarter.
In the first year management hope to average 60% of the maximum takings in each quarter, 65% in each quarter in the second year, building up to 70% in each quarter by the third year of trading.
In practice, there is likely to be some seasonal quarterly variation over the year but how much is unknown. Quarter 2 and 4 are known to be the better quarters for the Clothing Department. You may want to use this information in part i) below.
The planned increase in staff dedicated to this area is 3 full time, and 4 part time staff. The part time staff will provide a total of 15 staff-hours a day on average over the whole quarter.
Wages for part-time staff will be about £7.50 per hour. The full time staff will cost about £25,000 per person per year, including all employers’ costs.
The Board of the business would like the development to break even within three years. The company’s cost of capital (discount rate) is 10% nominal per annum, although this figure is under review.
You are required to investigate the data provided and associated factors in order to make a recommendation to the store manager about the whether the development should proceed or not.
a) Use the file BA Cash flow Referred Project 2 1213.xls.
The file contains a framework for calculating the quarterly net cash flow to be expected from the development, together with the net present value as a measure of performance.
• Investigate the structure of the model to ensure that you understand how it works. Some of the data values have already been input.
• In the cells shaded in blue, enter the remaining data values from the project brief above.
• The cells shaded in grey require formulae to be entered. The formulae will either be single entries in column B, or entries in column C which are then auto-filled across the three years.
• Enter the required formulae and auto-fill across the 3 years where appropriate.
Note that all formulae should be based on cell references – no numerical data values should be entered. You will need to use absolute cell references in a lot of cases.
b) Set your model up for printing in Formulae View. Your model should:
i) show all 3 years and show all formulae fully
ii) be landscape view and a maximum of 6 pages
iii) contain your name and ‘Referred Project 2’ as a header
iv) show gridlines and row/column labels
v) the headings in rows 5, 6 and 7 should be repeated at the top of each page
Once set up, save the file as a pdf file.
c) Set your model up for printing in normal view. Your model should:
i) show all 3 years
ii) be landscape view and a maximum of 2 pages
iii) contain your name and ‘Referred Project 2’ as a header
iv) show gridlines
v) the headings in rows 5, 6 and 7 should be repeated at the top of each page
Once set up, save the file as a pdf file.
d) Write a report (word doc) for the manager of the Bristol store with the following sections:
• Introduction/Methodology
Briefly outline the basis of your analysis i.e. what you have done in part a) and explain the meaning of the terms used such as net cash flow, discount factors, present values and net present values and why you have done this i.e. refer to the brief.
• Initial Conclusion
Using the net present value, along with the cash flow, decide whether or not the development is financially worthwhile and explain why/why not.
• Sensitivity Analysis
Identify at least four key factors from the cash flow that you think influence your decision above. Make reasonable changes to the values of these factors and determine how these changes affect your decision above. Determine the value that each key factor would have to take in order to reverse the decision above and comment on its likelihood.
• Market Intelligence/Competition
Gather market intelligence on the designer clothing market and Bristol together with details competition.
• Final Conclusion
Draw all of the material together and make a final recommendation. Include any other relevant information. The recommendation must be supported by clear reasons and stem from the evidence you have reported.
Report (word doc)
i) Write a report for the manager of the Bristol store with the following sections:
• Introduction/Methodology (Learning Outcome 5 and aspects of 1, 2 &3)
Briefly outline the basis of your analysis ie what you have done in part a) and explain the meaning of the terms used such as net cash flow, discount factors, present values and net present values and why you have done this ie refer to the brief.
• Initial Conclusion (Learning Outcome 4)
Using the net present value, along with the cash flow, decide whether or not the development is financially worthwhile and explain why/why not.
• Sensitivity Analysis (Learning Outcomes 1 – 4)
Identify at least four key factors from the cash flow that you think influence your decision above. Make reasonable changes to the values of these factors and determine how these changes affect your decision above. Determine the value that each key factor would have to take in order to reverse the decision above and comment on its likelihood.
• Market Intelligence/Competition (Learning Outcomes 1 – 4)
Gather market intelligence on the designer clothing market and Bristol together with details competition.
• Final Conclusion (Learning Outcomes 4 & 5)
Draw all of the material together and make a final recommendation. Include any other relevant information. The recommendation must be supported by clear reasons and stem from the evidence you have reported.