Reference no: EM13290426

Sales Tracking and Customer Relations Analyses

Aims

To analyse a set of data, 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 organises space for exhibitirs (i.e. customers) to display their products or services, for a number of Trade Fairs held locally and overseas. BTFC distinguish between new or returning exhibitors for calculation of their commission fee income rates.BTFC also organises visitor tickets for the trade fairs, which also provide commission income. BTFC alsoreceive income by providing a number of additional value-added services to exhibitors, such as by 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. These activities are managedbythreeBTFCSales Representatives (Suki, James, Padmila). 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 hired you as a 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-17 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
• Exhibitor stand fee income ($A)
• Exhibitor stand expenses ($A)
• Exhibitor travel organised?
• Exhibitor accommodation organised?
• Exhibitor marketing material organised?
• Exhibitor hospitality organised?
• Visitor tickets sold

BTFC organise stands for the exhibitors to display their products or services at the trade fairs - these stands cost BTFC money to organise and this expense if offset by BTFC charging the exhibitor a (variable) fee to organise the stand.

The additional fees charged by BTFC to customers (i.e. exhibitors) for their value-added services (i.e. as fee income) are:

Value Added Service (VAS):	VAS Fee Income Received ($A)
Exhibitor travel arrangements	100
Exhibitor accommodation arrangements	100
Exhibitor marketing material organised (large space:>=22 sq.m.)	1200
Exhibitor marketing material organised (small space: < 22sq.m.)	650
Exhibitor hospitality organised	100

The data worksheet "Fairs" on the Excel workbook contains collected information of the Trade Fairs over the past decade. Note: The list of Trade Fairs on this spreadsheet is complete and their names are all correct. These details collected for the previous decade's work include:

• Trade Fair name
• Year
• space available for BTFC to sell to exhibitors (sq.m.)
• space sold by BTFC to exhibitors (sq.m.) - only for 2014 year data
• Visitor tickets sold by BTFC
• Total Profit for BTFC

BTFCalso receives income via a fee ($6) 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.

BTFC receives 'commission fees'(i.e. as income) from the various Trade Fairs for selling space to companies exhibiting their products or services at those various fairs. The Trade Fairs managers charge $250 sq.m. for floor space at all Trade Fairs, which BTFC charges their customers (i.e. exhibiting companies) and then receives a variable percentage of these floor space fees as income. BTFC also pays commissions to the Sales Representatives for selling the space to those exhibiting companies (i.e. fees as expense). The following tables describe how these commissions are calculated:

Floor Space CommissionFees Received from Exhibitors	Space Sold	Fee Rate Received (% of sale amount)
New Exhibitor (large)	>= 22sq.m.	15
New Exhibitor (small)	< 22sq.m.	10
Returning Exhibitor	any space	6

Floor Space CommissionFees Paid to Sales Representatives	Space Sold	Fee Rate Paid (% of commission received)
New Exhibitor (large)	>= 22sq.m.	25
New Exhibitor (small)	< 22sq.m.	15
Returning Exhibitor	any space	12.5

Important: the fees received as income are based on the amount of floor space sold to customers/exhibitors. The fees paid to Sales Representatives as expenses are based on the amount of commission income received from floor space sold.

BTFC also currently pays each Sales Representative a wage(i.e. an expense) for their time spent workingwith a customer on a sale ($15 per hour).

Assessable Tasks

Ms Holt needs to have a summary report of operations that will include the following information:

Calculations:

a. total sales, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) income type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

b. 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.

c. total expenses, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) expense type.Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

d. 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.

e.  changes to profitability over the past decade, including 2014 figures, sub-totalled by each Trade Fair.Use an appropriate graph/chart to summarise these changes.

Ms Holt requires you to make observations to help her with the following questions.

Observations:

f. Who are BTFC'sfive best and five worst customers by total sales? Use an appropriate table and graph/chart to summarise these observations.

g. What are BTFC's best and worst value-added services by total profit? Use an appropriate table and graph/chart to summarise these observations.

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

i. What would happen to profits if the rate of pay to Sales Representatives for hours worked on sales was increased by 40%? Use an appropriate table and graph/chart to summarise these calculations.

Observations:

j. What are the best Sales Representative's best- and worst-selling services (by total profit)?Use an appropriate table and graph/chart to summarise these calculations.

k. What would happen to profits if the amount of space required to be sold to change the scale of commission paid was changed from 22 sq.m. to (a) 25 sq.m. and (b) 20 sq.m.Use an appropriate table and graph/chart to summarise these calculations.

Ms Holt also requires you to make recommendationsconcerning the following business-level questions.

Recommendations:

l.  Should BTFC focus on any particular (a) customer(s), (b) Trade Fairs, and (c) particularsales activities in future, and why?

m. What are your recommendations for resolving any data redundancy issues observed in the workbook?

n. What are your recommendation regarding what other data should be collected to improve decision making for BTFC?

o. What are your recommendation regarding record-keeping to improve the quality of data collection and management?

Recommendations:

p. What changes should BTFC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why?

Ms Holt has asked you to provide her with the details of how you have accomplished these tasks. Therefore, you must include the functions and the formulae you've used in your calculations and analyses, and not simply report the answers.Create a new worksheet in the assignment workbook for each question, to provide Ms Holt with thesecalculations and summations.

Note: As a paid consultant, your submission to Ms Holt must be professionally presented;youranalyses, calculations and summaries in the Excel file must have headings and be supported with explanatory notes; and the formal business report must be professionally written and presented.All recommendations in your report must be clearly justified (e.g. include charts/graphs or refer to specific analyses/summaries from the Excel spreadsheet).

Attachment:- Assignment 2014-05 2014-17 data.xlsx