Ballarat Event Management Company (BEMC) specialises in promoting and selling both (i) stand space at trade fairs and (ii) tickets for visitors to trade fairs in Ballarat region. The company
currently promotes and sells two different sized stand spaces (small and large), to new or returning exhibitors, for a number of Trade Fairs locally and overseas. BEMC also provides 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. BEMC 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 Diamantina White, like all small business owners, is always
interested in finding ways to increase revenues and decrease expenses.

Ms White 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 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 White has provided you with the half-yearly sales information of this year (2016) in the “BEMC_sales_first_half_2016.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 BEMC’s operations in the first six months of 2016. These details included:

• Date (of customer order),
• Trade Fair

• Customer (i.e. exhibiting company)
• Area of space rented (sq. m.)

• Sales Representative
• Time spent by Sales Representative to complete sale (minutes)
• Returning Exhibitor?

• Number of visitor tickets sold
• Exhibitor stand organised?

• Exhibitor travel organised?
• Exhibitor accommodation organised?
• Exhibitor marketing material organised?

• Exhibitor hospitality organised?
The fees charged by BEMC to customers (i.e. exhibitors) and their cost for organizing value-added services are as follows:

Value Added Service Fee charged Cost

(AUD) (AUD)

Exhibitor stand organised 1500 1150
Exhibitor travel arrangements 1000 850
Exhibitor accommodation arrangements 800 650
Exhibitor marketing material organised 500 250
Exhibitor hospitality organised 600 500
Table 1: Fees charged and cost of value added services

The company receives different rates of ‘commissions’ (i.e. fees) from the various Trade Fairs for renting space to companies exhibiting at the various fairs. The Trade Fair managers charge AUD 80
per square meter (sq. m.) for floor space at all Trade Fairs. BEMC receive commission according to the following table.

Commission Received New Exhibitor Area of Space Rate (% of total rent)

New Exhibitor (large) Yes >= 20 sq. m. 25
New Exhibitor (small) Yes < 20 sq. m. 30
Returning Exhibitor (large) No >= 20 sq. m. 20
Returning Exhibitor (small) No < 20 sq. m. 25
Table 2: Rates of commission on space rent

Note that exhibiting company pays the space rent directly to the trade fair manager and BEMC receives their commission from the trade fair manager.

BEMC receives a fee of AUD 1.50 for each Trade Fair visitor ticket sold through its Sales Representatives.

Each representative receives AUD 60 for each hour of their service. BEMC pays commissions to the Sales Representatives for selling the space to those exhibiting companies. The following table
describes how these commissions are calculated:

Commission Paid New Area of Rate (% of commission

Exhibitor Space received by BEMC)

New Exhibitor (large) Yes >= 20 sq.m. 30
New Exhibitor (small) Yes < 20 sq.m. 37
Returning Exhibitor (large) No >= 20 sq.m. 27
Returning Exhibitor (small) No < 20 sq.m. 30
Table 3: Rates of representative commission (% of the commission received by BEMC according to Table 2)

Task 1: Calculations

General calculations (All Students):

I. Perform the following calculations using formula:
a. Service fee for each value added services

b. Cost for organising each value added services

c. Total revenue from ticket sales

d. Commission received from space rent

e. Sales representative’s wage

f. Sales representative’s commission

g. Total income

h. Total expenses

i. Total profit
II. Total profit, sub-totalled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
III. Total hours worked, sub-totalled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
Task 2: Observations
Ms White requires you to make observations to help her with the following questions.

General Observations (All students):

I. Who are BEMC’s five best and five worst customers by total profit? Use an appropriate table and graph/chart to summarise these observations.

II. What are BEMC’s best and worst Trade Fairs by total profit? Use an appropriate table and graph/chart to summarise these observations.

III. Who are BEMC’s best and worst sales representatives by total income? Use an appropriate table and graph/chart to summarise these observations.

Task 3: Recommendations

Ms White also requires you to make recommendations concerning the following business-level questions.

General recommendations (All Students):

I. Should BEMC focus on any particular (a) customer(s) and (b) Trade Fair(s), in future, and why?

II. What are your recommendation regarding what other data should be collected to improve decision making for BEMC?
Ms White has asked you to provide her with the details of how you have accomplished these tasks.