Paper, Order, or Assignment Requirements
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):
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.
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.
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.
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):
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.
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.
What are FFC’s best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations.
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.
Recommendations (All Students):
Should FFC focus on any particular sales activities in future, and why?
What are your recommendation regarding what other data should be collected to improve decision making for FFC?
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.
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 Martyn 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)
Marking Guide
Tasks
Marks
Calculations (All Students):
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.
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.
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.
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.
10
3
2
3
2
Observations (All students):
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.
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.
What are FFC’s best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations.
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.
10
2
2
2
2
2
Observations (ITECH5005 Students Only):
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.
Chart/graph changes to daily profitability over the three months. Use an appropriate graph/chart to summarise these changes.
4
2
2
Recommendations (All Students):
Should FFC focus on any particular sales activities in future, and why?
What are your recommendation regarding what other data should be collected to improve decision making for FFC?
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.
6
1
1
1
3
Recommendations (ITECH5005 Students Only):
What changes should FFC make regarding any (a) currently employed Sales Representative(s), or (b) Shops, and why?
2
2
Submission Presentation:
Report is well presented and well-written, containing:
Title page
Executive Summary (outlining scope of report, key findings and recommendations)
Table of Contents
Appropriate use of headings within report
Appropriate use of figures (i.e. graphs, summary tables) and reference to calculations and summaries to justify all observations and recommendations
4
0.25
1
0.25
0.5
2
Deductions:
Late submission (deduction – 10% per day)
MS Excel workbook and Word document not named correctly (deduction – 0.5 marks each)
Pivot Tables and Graphs/Charts not used in analyses as required (deduction – up to 5 marks)
Analyses and summaries in workbook do not have headings and explanatory notes (deduction – up to 4 marks)
Total marks ITECH1005 students:
30
Total marksTECH5005 students:
36
Contribution to total marks
30%
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.