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