Semester 1, 2014
Due: 5pm, Wednesday, Week 11
Sales Tracking and Customer Relations Analyses
To analyse a set of data (in Microsoft Excel), and write a brief report (in Microsoft Word), identifying and explaining your insights into the operation of “Ballarat Trade Fair Consultancy”.
In the process of this assessment task you will:
* plan, schedule and execute project tasks with a view to improving your personal productivity;
* gain awareness of some typical issues related to the operation of a small-to-medium size business;
* use the functionality of Microsoft Excel 2007/2010 to manipulate data, analyse it and visualise it in tabular ...view middle of the document...
She is also eager to hear your thoughts and ideas on how to improve the business and requires you to make several recommendations on how to improve the company’s performance, especially in relation to the following business objectives:
* improving the sales strategy;
* improving internal efficiencies and effectiveness; and
* building strong lasting relationships with its customers.
Ms Holt has provided you with last year’s sales information in the “itech1005-5005 2014-05 assignment data.xlsx” file. The data needs to be analysed and visualised to help observations related to the business operation and its shortcomings.
The data worksheet “Sales” contains collected information of the consultancy’s operations in the current year. These details included:
* Date (of customer order),
* Trade Fair
* Customer (i.e. exhibiting company)
* space sold (sq.m.)
* Sales Representative
* time spent by Sales Representative to complete sale
* returning Exhibitor?
* Exhibitor stand organised?
* Exhibitor travel organised?
* Exhibitor accommodation organised?
* Exhibitor marketing material organised?
* Exhibitor hospitality organised?
* Visitor tickets sold
The additional fees charged by BTFC to customers (i.e. exhibitors) for their value-added services are:
Value Added Service | Fee ($A) |
Exhibitor stand organised | 1000 |
Exhibitor travel arrangements | 100 |
Exhibitor accommodation arrangements | 100 |
Exhibitor marketing material organised | 1000 |
Exhibitor hospitality organised | 100 |
The data worksheet “Fairs” contains collected information of the Trade Fairs over the past decade. These details included:
* Trade Fair
* space available for BTFC to sell to exhibitors (sq.m.)
* Visitor tickets sold by BTFC
* Total Profit for BTFC
BTFC receives a fee ($5) for each Trade Fair visitor ticket sold through its Sales Representatives. This historical information is summarised in the “Fairs” worksheet as a summation of all Sales Representative ticket sales.
The company receives different rates of ‘commissions’ (i.e. fees) from the various Trade Fairs for selling space to companies exhibiting at the various fairs. The Trade Fair managers charge $120 sq.m. for floor space at all Trade Fairs, which rate BTFC charges the exhibiting companies. BTFC then pays commissions to the Sales Representatives for selling the space to those exhibiting companies. The following tables describe how these commissions are calculated:
Commission Received | Space Sold | Rate (% of sale amount) |
New Exhibitor (large) | >= 20 sq.m. | 10 |
New Exhibitor (small) | < 20 sq.m. | 5 |
Returning Exhibitor (large) | >= 20 sq.m. | 7.5 |
Returning Exhibitor (small) | < 20 sq.m. | 2.5 |
Commission Paid | Space Sold | Rate (% of commission received) |
New Exhibitor (large) | >= 20 sq.m. | 20 |
New Exhibitor (small) | < 20 sq.m. | 10 |