Semester 2, 2014
Weight: 30%
Due: 5pm, Friday, Week 11
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 Fair Consultancy”.
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 also receive 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 managed by three BTFC Sales 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 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 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 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
• 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
BTFC also 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 Commission Fees Received from Exhibitors Space Sold Fee Rate Received (% of sale amount)
New Exhibitor (large) = 22 sq.m. 15
New Exhibitor (small) 22 sq.m. 10
Returning Exhibitor any space 6
Floor Space Commission Fees Paid to Sales Representatives Space Sold Fee Rate Paid (% of commission received)
New Exhibitor (large) = 22 sq.m. 25
New Exhibitor (small) 22 sq.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 working with 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 (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 Holt requires you to make observations to help her with the following questions.
Observations (All students):
f. Who are BTFC’s five 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 (ITECH5005 Students Only):
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 recommendations concerning the following business-level questions.
Recommendations (All Students):
l. Should BTFC focus on any particular (a) customer(s), (b) Trade Fairs, and (c) particular sales 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 (ITECH5005 Students Only):
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 these calculations and summations.
Note: As a paid consultant, your submission to Ms Holt must be professionally presented; your analyses, 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).
Some Hints
The business options are linked to a number of questions posed by Ms Holt. Your role is to answer these specific questions and to support your answers with your data analysis as presented in the appropriate tables and charts. Ensure that your recommendations address the company’s objectives, and that they are clearly linked to your observations.
Within this company, profit is a simple calculation of total sales less total expenses. There are seven types of income and three types of expenditure identified by Ms Holt and included in the detail above - all must be included in the relevant calculations (e.g. of income, expenses and profits).
There is no ‘ideal’ or ‘best’ way of doing this project. You’ll be assessed on your insight of the data, on your ability to make observations by analysing (with formulae, pivot tables) and visualising (with charts/graphs) your data, and your ability to link your observations to business issues. You must use Pivot Tables and graphs/charts in your work, otherwise penalties apply.
There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning your workbook, and especially the observations, you may wish to review the tutorials on the related topics (e.g. Excel functions, absolute and relative cell referencing, formatting, chartings and managing large worksheets, as well as on the analysis of Excel data and drawing recommendations). You may find the following formulae useful in this assignment: MONTH, COUNTIFS, IF, SUM, SUMIFS. You will also need to know how to nest formulae within formulae (i.e. nest an IF statement inside another IF statement to provide multiple answers when multiple criteria must be applied).
The Excel workbook contains all the necessary data for your assignment and includes the heading columns for you to make the minimal set of calculations to answer the questions. You may need or want to include other calculations – the provided column labels are therefore only the minimum set required for your analyses.
You need to make sure the records included in the worksheets are not faulty or inaccurate (e.g. missing data) to avoid errors in calculations. You will therefore need to ‘cleanse’ the dataset before you commence your calculations and analyses.
Make sure you review the marking guide, to ensure you complete all required tasks before submission. Make sure you submit both required files and that both are correctly named, otherwise penalties will apply. Make sure you create enough new worksheets on the provided Excel workbook to answer each question. Make sure you add appropriate headings and explanatory notes of your calculations and analyses on each worksheet to ensure that Ms Holt can interpret your work, otherwise penalties will apply.
Assignment Assessment
The questions posed to you in this assignment range from easy, through medium to challenging. However, it is expected that all students will succeed in all the assessment tasks. Some of the tasks are very similar to those covered in tutorials and the associated exercises. Some of the challenging tasks may rely on insight that requires self-directed study (e.g. using help facilities in Excel to explore in depth some of the topics covered in the lab exercises).
This assignment is worth 30% of total marks for this course and will be marked on:
• your demonstrated understanding of the problems;
• completeness of the tasks;
• exploration of the features in Microsoft Office; and
• quality of the submission (Excel workbook & Word document).
Assignment checkpoints: During your lab classes in weeks 7 to 11 you may be asked by your tutors to show evidence of your consistent work on your assignment.
Submission
All students are required to submit two (2) files for this assignment, via Moodle, prior to the due date and time. Your two submission files will be a Microsoft Excel spreadsheet (containing your analyses) and a Microsoft Word document (containing your report).
For undergraduate students, each file must be named as follows:
• itech1005_2014-17_YourSurname_YourStudentID.doc (or .docx); and
• itech1005_2014-17_YourSurname_YourStudentID.xls (or .xlsx)
For post-graduate students, each file must be named as follows:
• Itech5005_2014-17_YourSurname_YourStudentID.doc (or .docx); and
• Itech5005_2014-17_YourSurname_YourStudentID.xls (or .xlsx)