Spring 2017 – FINAL questions- DUE 1/5/17
The workbook – Question.xlsin Blackboard contains all of the data you need.
Question 1 – Correlation and XY scatter chart
You own the rights to sell a scientific calculator which has been developed in China and has been recommended by lecturers on University degree courses in the UK. Your rights allow you to be the sole distributor within the M25
You have, to date, developed links with a few shops within your area and also have a small team of salespeople who travel to Universities and colleges selling direct to students at induction and other marketing events
This business has grown steadily over the past five years and you hope to expand within your area but need to borrow some money from the bank to help you to do this
The bank has asked you to prepare some projections of future growth and you would like to use the skills learnt on your Excel course to help with this
A worksheet containing your sales data has been prepared to assist with this analysis
The worksheet has been prepared monthly, and provides details of the amount spent each month on marketing your calculators, the number of stores you sell your calculator through, the number of salespeople you use, the selling price of the calculator and the number of calculators sold
The first thing you need to work out is if there is a link between any of the different variables and the number of calculators you sell each month. If there is a strong correlation between the number of calculators you sold and another variable this could help with forecasting
Instructions:
1. In column I of the spreadsheet calculate the correlation of each variable with the number of calculators sold
2. Using the two sets of data that correlate most closely, prepare an XY scatter chart to show the relationship of these two sets of data
3. Format your XY scatter chart with appropriate titles
4. Add a trend line and show the equation of this on the chart
Question 2– Solver
See the Solver 1 spreadsheet for details
Question 3 – Solver
You have an advertising budget of £45,000 and want to spend this in such a way that you reach the maximum audience possible
You have identified a number of different publications in which you would like to place at least one but no more than five adverts
Only whole adverts may be placed
The publications, audience numbers and costs are as follows:
Audience Cost
Magazine 1 250,000 1,650
Magazine 2 200,000 1,400
Magazine 3 180,000 1,300
Magazine 4 225,000 1,450
Magazine 5 100,000 750
Newspaper 1 300,000 1,900
Newspaper 2 215,000 1,450
Newspaper 3 264,000 1,800
Newspaper 4 198,000 1,150
The total cost per publication is calculated as the cost per advert * the number of adverts placed
The total audience per publication is calculated as the number of adverts placed * the audience per publication
Use Solver to calculate how many adverts should be placed in each publication to reach the maximum audience figures within your budget, and ensuring you place do not advertise more or less times than you are allowed
Question 4 – Solver 3
A cake mix manufacturer has 4 different factories that all need to buy sugar. There are 5 different suppliers of sugar that could be used
The prices per ton for sugar for each supplier and the delivery cost per ton for sending it to each factory are provided in rows 5 to 12 of the Solver 3 worksheet:
Factories require the following amounts of sugar (as shown in Column I of the Solver 3 worksheet):
Factory 1 420 tons
Factory 2 360 tons
Factory 3 400 tons
Factory 4 375 tons
Each supplier has the following amounts of sugar available to sell (as shown in row 22 of the Solver 3 worksheet):
Supplier 1 350 tons
Supplier 2 250 tons
Supplier 3 200 tons
Supplier 4 300 tons
Supplier 5 500 tons
To do:
You would like to buy sugar for each factory in a way that will minimise total costs (sugar costs + delivery costs)
Sugar purchases need to be for the amounts needed by each factory but you cannot buy more from a supplier than the amount it has available to sell
The information inputs needed to solve this problem have already been input into the Solver 3 worksheet
Formulas are required to work out the following:
• Row 21 – total sugar to be purchased from each supplier – this is the sum of the variable cells per supplier
• Column H – total sugar to be purchased by each factory – this is the sum of the variable cells for each factory
• Row 24 – Cost of sugar – this is the sum of the quantity of sugar purchased from each supplier * the cost of sugar for each supplier
• Row 25 – Cost of shipping – this is the sum of the number of tons purchased from each supplier * the cost of shipping for each supplier
• Cells H24 & H25 – the total costs for all suppliers for sugar and shipping
• Cell H 26 – the total cost (sugar + shipping)
You wish to minimize total cost for the sugar, by varying the quantity purchased from each supplier and ensuring that the quantities purchased equal the quantities needed by each factory but are not more than the amounts available for sale by each supplier
Q5 – Pivots
Data is provided in the Pivot Data worksheet
Using this data, prepare the following pivots – each one on a different page:
1 A pivot chart showing total sales income per category of product
2 A pivot table showing total sales income per product per year
3 A pivot table showing sales income per store number
4 A pivot chart showing the quantity (number) of sales per product
Ensure all charts and pivot tables are fully formatted so that they are ready to be used in a written report
Q6 – Investments – NPV & IRR
Your friend has come into some money and has asked you to help with the analysis of 3 alternative investment opportunities:
Option 1
To invest £250,000 (in Year 0). This investment would give zero income for 9 years, but would then give £925,000 in year 10
Option 2
To buy a new business
– Cost £150,000 in Year 0
– In Year 1 income is expected to be £70,000. This is expected to increase by 3% each year from the start of Year 2
– Costs (excluding staff costs) in Year 1 are expected be £20,000. These will increase by 2% each year from the start of Year 2
– Staff costs in Year 1 are £30,000. These are expected to increase by 2.5% each year
The business could be sold for £300,000 at the end of year 10 if needed
Option 3
To invest £200,000 in Option 3. This would repay you at £40,000 per year for 10 years
Required:
1. In the Investment worksheet set out the annual expected cash flows for each of the three investment opportunities
2. Use Excel to calculate the NPV of each ten year investment option, note that the equivalent cost of borrowing in all cases will be 10%
3. Use Excel to calculate the IRR of each project
4. Based on your analysis, comment on which project provides the best investment opportunity and why