Sales tracking and customer relations analyses

Assignment Help Other Subject
Reference no: EM13152744

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


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


Exhibitor travel arrangements


Exhibitor accommodation arrangements


Exhibitor marketing material organised


Exhibitor hospitality organised


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.


New Exhibitor (small)

< 20 sq.m.


Returning Exhibitor (large)

>= 20 sq.m.


Returning Exhibitor (small)

< 20 sq.m.



Space Sold

Rate (% of commission received)

New Exhibitor (large)

>= 20 sq.m.


New Exhibitor (small)

< 20 sq.m.


Returning Exhibitor (large)

>= 20 sq.m.


Returning Exhibitor (small)

< 20 sq.m.


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 operations that will include the following information:


a. total sales, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) income type.

b. total profit, sub-totalled by each (a) Trade Fair, and (b) Customer.

c. total costs, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) cost type.

d. total hours worked, sub-totalled by each (a) Trade Fair, and (b) Customer.

e. percentage of current (a) sales, and (b) profits attributed to each Trade Fair.


f. costs as a percentage of (a) sales, and (b) profits, sub-totalled for each Sales Representative.

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.


h. Who are BTFC's best and worst customers by total profit?

i.  What are BTFC's best and worst value-added services by total sales?

j.  Who are BTFC's best and worst Sales Representatives by total profit?

k. What would happen to profits if the commission rate of pay to Sales Representatives was increased by 50%?


l. What are the worst Sales Representative's best- and worst-selling services (by total profit)?

m. Who are the best Sales Representative's best and worst customers (by total profit)?

n. Who are the best Sales Representative's best and worst Trade Fairs (by total profit)?

o. How much additional value-added services are required to be sold at the least profitable Trade Fair (by total profit) to increase the profit by 25%?

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


p. Should BTFC focus on any particular Trade Fair(s) or sales activities now and/or in future, and why?

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

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

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


t. 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 analyses, and not simply report the answers.Create a new worksheet in the assignment workbook called "Calculations" to provide Ms Holt with thesecalculations and summations.

As a paid consultant, your submission to Ms Holt must be professionally presented;all analyses, calculations and summaries in the Excel file musthave headings and be supported with explanatory notes; and the formal business report must be preceded by a 'covering letter' (i.e. incorporate letter as first page of your Word document with your report on following pages).

Download:- Assignment data.xlsx

Reference no: EM13152744

Difference of water between inside and outside the cell

Consider a spherical cell of radius 8 micrometers. The cell is suddenly placed in a solution of pure water. For simplicity, assume that the cell is a 0.15 M NaCl solution and

Self satisfaction-joy-self actualization or accomplishment

A state of self-fulfillment all individuals naturally strive to develop and move toward, according to Abraham Maslow, is called; self satisfaction, joy, self actualization or

Provide a brief critique of the issues

From the first eActivity, select one of the news events regarding NTEU and provide a brief critique of the issues. From the second e-Activity, as Human Resource Director for t

Spreading the american dream

What is Emily S. Rosenberg's main argument "Spreading the American Dream" is as to why the US gets involved in the world. Tell us if you agree or disagree with this argument a

What can be done to defend against attack explain

What are your thoughts on the use of suicide bombing as a terrorist tactic? Explain. Is this an effective tactic for spreading terror? Why or why not? What can be done to defe

Conceptual frameworks of globalization and autonomy

When looking at the conceptual frameworks of globalization and autonomy, discuss the difference between western scientific epistemologies and indigenous epistemologies and how

Do you think they are liberal or conservative

Choose one news anchor that you tend to watch OR believe. Tell us about that person. Why do you trust them? What network do they work for? Do you think they are liberal or con

Environmental analysis-political-economic and demographic

When conducting a Case Study Analysis based on the online educational institute Kaplan University, I need in depth the External Analysis. Within this I need the General enviro


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd