Posted: February 10th, 2015
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:
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:
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:
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):
Ms Martyn requires you to make observations to help her with the following questions.
Observations (All students):
Recommendations (All Students):
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:
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:
Marking Guide
Tasks | Marks |
Calculations (All Students):
|
10
3
2
3
2
|
Observations (All students):
|
10
2
2
2
2 2 |
Observations (ITECH5005 Students Only):
|
4
2
2 |
Recommendations (All Students):
|
6
1 1
1 3 |
Recommendations (ITECH5005 Students Only):
|
2
2 |
Submission Presentation:
|
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% |
Place an order in 3 easy steps. Takes less than 5 mins.