Sales Tracking and Customer Relations Analyses
Aims
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 FairConsultancy".
Learning Objectives
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 and chart form; and
- use the functionality of Microsoft Word to write a brief report of your business observations and recommendations.
Introduction
Ballarat Trade Fair Consultancy (BTFC) specialises in promoting and selling both (i) stand space at trade fairs and (ii) tickets for visitors to trade fairs in Ballarat area. The company currently promotes and sells two different sized stand spaces, to new or returning exhibitors,for a number of Trade Fairs locally and overseas. BTFC alsoprovides a number of additional value-added services to exhibitors for fees, such as organising exhibitor stands, developing marketing material for exhibitors to distribute at fairs, arranging travel and/or accommodation for exhibitors, and hospitality/catering for exhibitors to offer their customers attending the trade fair. BTFC also organise visitor tickets for the trade fairs, which also provide commission income. All these activities are managed through the efforts of a number of sales representatives. The company's founder, Ms Eliza Holt, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.
Ms Holt has recently hired you as her business analyst and poses some operational-level questions about the performance of her business. She is also eager to hear your thoughts and ideas on how to improve the business and requiresyou 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 operationsinthe current year. Thesedetails 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
- Year
- 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 thenpays 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
|
|
CommissionPaid
|
Space Sold
|
Rate (% of commission received)
|
|
New Exhibitor (large)
|
>= 20 sq.m.
|
20
|
|
New Exhibitor (small)
|
< 20 sq.m.
|
10
|
|
Returning Exhibitor (large)
|
>= 20 sq.m.
|
15
|
|
Returning Exhibitor (small)
|
< 20 sq.m.
|
5
|
BTFC also currently pays each Sales Representative a fee for their time spent working on a sale ($10 per hour).
Assessable Tasks
Ms Holt needs to have a summary report of current operations that will include the following information:
Calculations (All Students):
a. total sales, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) income type.
b. total profit, sub-totalled by each (a) Trade Fair, and (b) Sales Representative.
c. total costs, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) cost type.
d. total hours worked, sub-totalled by each (a) Trade Fair, and (b) Sales Representative.
e. percentage of current (a) sales, and (b) profits attributed to each Sales Representative.
Calculations:
f. costs as a percentage of (a) sales, and (b) profits, sub-totalled for each Trade Fair.
g. changes to profitability over the past decade, including current year's figures, sub-totalled by each Trade Fair.
Ms Holt requires you to make observations to help her with the following operational-level questions.
Observations:
h. Who are BTFC's best and worst customers by total sales?
i. What are BTFC's best and worst value-added services by total profit?
j. What are BTFC's best and worst Trade Fairs by total profit?
k. What would happen to profits if the rate of pay to Sales Representatives for hours worked on sales was increased by 50%?
Observations:
l. What are the best Sales Representative's best- and worst-selling services (by total profit)?
m. Who are the worst Sales Representative's best and worst customers (by total profit)?
n. What are the best Trade Fair's best- and worst-selling services (by total profit)?
o. How much additional space is required to be sold at the worst selling Trade Fair to increase the profit for that fair by 100%?
Attachment:- itech data.xlsx