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 “The FedFone Co.”.
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
The FedFone Co. (FFC) sells mobile phones and accessories at three shops in the Ballarat area (i.e. High Street, Mall, Suburb) seven (7) days a week. The company also organises repairs for phones they sold but which have been damaged or become faulty. These activities are co-ordinated by five (5) FFC Sales Representatives (John, Paul, Ringo, George, Stuart). The company’s founder, Ms Georgie Martyn, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.
Ms Martyn 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 Martyn has provided you with last year’s sales information in the “itech1005-5005 2014-27 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 first three months of the year. These details included:
• Date (of sale)
• Shop (where sale completed)
• Staff (i.e. Sales Representative name)
• Phone Model
• Phone Plan
• Phone Cover (accessory sold)
• Spare Battery (accessory sold)
• Charger Pack (accessory sold)
• Repair Details (type of problem with phone returned for repair)
• Repair Date (date received from customer for repair)
• Return Date (date returned to customer after repair)
• Software Update (service fee income)
• Training Time (hours spent training customers in phone use – service fee income)
• Insurance (service fee income)
FFC must buy all the stock (i.e. mobile phones and accessories) they then sell to customers.
Phone/Accessory: Cost Price ($A) Sales Price ($A)
HTS model 1 98 150
HTS model 2 185 200
HTS model 3 199 350
HTS universal phone cover 15 27
HTS spare battery 67 100
Sansumg 300 phone 152 300
Sansumg 250 phone 187 210
Sansumg 300 cover 16 25
Sansumg 250 cover 13 25
Sansumg spare battery 90 100
Apel v17 phone 655 690
Apel cover 22 45
Apel charger pack 45 80
Motorolar razzr phone 199 220
Motorolar newstyle phone 245 270
Motorolar phone cover 16 25
Nokkia phone 1111 99 140
Nokkia phone 2222 129 165
Nokkia phone cover 14 25
Nokkia spare battery 80 110
Universal charger pack 27 40
FFC sell a range of mobile phone plans to customers that come from a number of telecommunications companies. FFC are paid a commission by each telecommunications company for selling the different plans as the table underneath indicates:
Phone Plans: Commission Income Received ($A)
Telstrar plan 1 10
Telstrar plan 2 8
Optrus plan 1 8
Optrus plan 2 6
Raldi plan 3
Vodrfon plan 1 5
Vodrfon plan 2 8
When phones are returned for repair, FFC must also pay $14 postage costs to mail the phone to the authorised service centre – this cost is wholly or partially reimbursed by the phone manufacturer according to a scale of fees:
Phone Company: Repair Postage Reimbursement Amount Received ($A)
HTS 18
Sansumg 8
Apel 5
Motorolar 10
Nokkia 15
FFC also provides additional services for which it charges customers fees for service. The additional ‘flat’ (i.e. one-time) fees charged by FFC to customers for their additional services (i.e. as fee income) are:
Additional Service: Service Fee Income Received ($A)
Phone set-up or software update 5
Phone use training 40
Phone insurance brokerage fee 5
All Sales Representatives are paid fixed wages and no additional commissions are paid for sales or services. Wages are a fixed cost to the business and as such are not required in the analysis/reporting of the sales and expenditure for Ms Martyn.
Assessable Tasks
Ms Martyn needs to have a summary report of operations that will include the following information:
Calculations (All Students):
a. total income for the three month period, sub-totalled by each (a) Shop, (b) Sales Representative, and (c) Phone. Use Pivot Table(s) to summarise these calculations.
b. total expenses for the three month period, sub-totalled by each (a) Shop, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
c. total profit for the three month period, sub-totalled by each (a) Shop, (b) Sales Representative, and (c) Phone. Use Pivot Table(s) to summarise these calculations.
d. total phone repair expenses and income for the three month period, sub-totalled by each (a) Phone, (b) Sales Representative and (c) Shop. Use appropriate Table(s) to summarise these calculations.
Ms Martyn requires you to make observations to help her with the following questions.
Observations (All students):
e. Which phone plans provided the (a) most and (b) least income, for the three month period? Use an appropriate table and graph/chart to summarise these observations.
f. Which are the worst phones sold by FFC, by (a) total number of repairs required and (b) time taken for repair, for the three month period? Use an appropriate table and graph/chart to summarise these observations.
g. What are FFC’s best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations.
h. What would happen to FFC profits if the training service was charged at an hourly rate of $25 per hour instead of the current flat fee? Create a spreadsheet tool that allows Ms Martyn to test profit outcomes for any training fee – create a new worksheet on the assignment workbook for this tool.
Observations (ITECH5005 Students Only):
i. Which was the best day of the week (e.g. Monday-Sunday) for FFC over the three month period for profit? Use an appropriate table and graph/chart to summarise these calculations.
j. Chart/graph changes to daily profitability over the three months. Use an appropriate graph/chart to summarise these changes.
Ms Martyn also requires you to make recommendations concerning the following business-level questions.
Recommendations (All Students):
k. Should FFC focus on any particular sales activities in future, and why?
l. What are your recommendation regarding what other data should be collected to improve decision making for FFC?
m. What are your recommendation regarding record-keeping to improve the quality of data collection and management? Apply appropriate data validation rules to columns A, B & M on the spreadsheet to show Ms Martyn how to apply this technique of improving data quality.
Recommendations (ITECH5005 Students Only):
n. What changes should BTFC make regarding any (a) currently employed Sales Representative(s), or (b) Shops, and why?
Ms Martyn 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 Martyn with these calculations and summations.
Note: As a paid consultant, your submission to Ms Martyn 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). It is imperative that all charts/graphs must have appropriate titles, a key or legend to describe the data series, appropriate scales and labels on all axes, and be of appropriate format so that the information being drawn from the chart/graph is clear.
Some Hints
The business options are linked to a number of questions posed by Ms Martyn. 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, gross profit is a simple calculation of total income less total expenses. There are up to nine forms of income (i.e. phone, plan, accessory sales, service fees, repair postage reimbursements) and three types of expenditure (i.e. phone, accessories, repair postage costs) identified by Ms Martyn for any sale. All amounts are included in the detail above - all must be included in the relevant calculations (e.g. of income, expenses and profits). No account of staff wages or other fixed costs is necessary in calculating gross profit.
IMPORTANT: 1-Jan is a Wednesday in the year of the provided dataset – this information is important for ITECH5005 students in calculating which day of the week has the highest profitability.
There is no single ‘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).
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 or incorrectly formatted data) to avoid errors in calculations. You will therefore need to ‘clean’ the dataset by correcting errors 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-27_YourSurname_YourStudentID.doc (or .docx); and
• itech1005_2014-27_YourSurname_YourStudentID.xls (or .xlsx)
For post-graduate students, each file must be named as follows:
• Itech5005_2014-27_YourSurname_YourStudentID.doc (or .docx); and
• Itech5005_2014-27_YourSurname_YourStudentID.xls (or .xlsx)