Business Information Systems Case Study

Latrobe Valley Product Gallery: Analysing Sales and Customer Relations

Introduction

Latrobe Valley Product Gallery (LVPG) specialises in promoting and selling both (i) stand space at trade fairs and (ii) tickets for visitors to trade fairs in Latrobe Valley, Gippsland 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. LVPG 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. LVPG 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 Rose, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses. Ms Rose 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 Rose has provided you with last year's sales information in the 'ITECH1005_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 fees charged by LVPG to customers (i.e. exhibitors) for their value-added services are…

The data worksheet 'Fairs' contains collected information of the Trade Fairs over the past decade.
These details included:
· Trade Fair
· Year
· space available for LVPG to sell to exhibitors (sq.m.)
· Visitor tickets sold by LVPG
· Total Profit for LVPG
LVPG receives a fee ($8) 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 $150 per square
meter for floor space at all Trade Fairs. LVPG receive commission according to the following table…

Calculations (All Students):
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 Rose requires you to make observations to help her with the following questions. You can also take help from tutors providing assignment help with the above information technology case study.

Executive Summary

Latrobe Valley Product Gallery or LVPG is interested in the analysis of their historic and current sales data for getting suggestions and supporting information for making strategic decisions on overall improvement of their business process. They are mostly focused on improvement of the internal activities of business process, their sales strategy and interested in building stronger customer relationships.

The scope of the analysis process is bounded by the requested calculations, observations and recommendations requirements provided by LVPG. The sales data provided by LVPG will be analyzed based on those requirements. The record keeping and data quality of the sales data provided by LVPG are not of very high quality thus, it restricts the outcomes of analysis in some cases…

Calculations

Following calculations on the sales data provided by LVPG have been done and those have given the following results…

Question b

A pivot table and pivot chart have been calculated based on the total profit from the trade fairs and sales representatives. Subtotals by each trade fair and sales representative have been shown in the table and chart. The pivot table is,

Recommendations

Following recommendations have been made for LVPG based on the above calculations and observations of the sales data of LVPG.

Question l

LVPG should focus on,

a.       Customers who are profitable or the best five customers.

b.      Trade fairs that are profitable of the best five trade fairs.

Profitable VAS sales activities…