Finchley Executive Cars

Part 1 | Part 2 | Part 3 | Part 4 | Part 5

This is an executive car dealership specialising in Porsche, Ferrari, BMW, Jaguar, Range Rover and Aston Martin.

You are the FInance Director and your task is to produce a model that will show the owner how well the business did over the last month.

Company Information

The table below shows the cars sold last month and the salespeople who sold them.

Part 1

Calculate, using formulae:

• The total number of cars sold
• The total number of cars sold per make
• The total number of cars sold per salesperson per month

Produce a graph displaying the amount of cars sold per month and the name of the salesperson

Part 2

The company operates a bonus scheme for its sales team. If they sell ten (10) cars or more they receive a bonus of £1,000.

Calculate who receives a bonus, using an IF function.

Part 3

Basic salary information is given in the table below:

From the above salary table calculate:

• The salary owed to each salesperson for last month (including their bonus)
• The total salary bill for last month, including bonuses.

Part 4

The business pays £40,000 for each BMW and they currently sell them for £47,000.

What should the sale price be if they wanted to make £70,000 profit from selling BMWs in the last month? (Hint: use Goal Seek)

Part 5

Finchley Executive Cars employ Peter as a part-time administrative assistant. His hours are given in the table below.

Peter is paid £5 per hour

Calculate Peter's weekly wage using these three scenarios:

1. The original scenario, using the above figures
2. Peter working 5 hours every day at £5 per hour
3. Peter working 5 hours every day at £6 per hour