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.". 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. 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. 1. total income for the three month period, sub-totalled by each (a) Shop, (b) Sales Representative, and (c) Phone.. 2. total expenses for the three month period, sub-totalled by each (a) Shop, and (b) Sales Representative. 3. total profit for the three month period, sub-totalled by each (a) Shop, (b) Sales Representative, and (c) Phone. . 4. total phone repair expenses and income for the three month period, sub-totalled by each (a) Phone, (b) Sales Representative and (c) Shop.. Assessable Tasks I have done these calculations. Observations (All students): Please do the following for me. Thx! a. 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. b. Which are the worst phones sold by FFC, by (a) total number of repairs required and Use an appropriate table and graph/chart to summarise these observations. c. What are FFC's best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations. d. 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. Ms Martyn also requires you to make recommendations concerning the following business-level questions. Recommendations (All Students): e. Should FFC focus on any particular sales activities in future, and why? f. What are your recommendation regarding what other data should be collected to improve decision making for FFC? g. 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 & Z on the spreadsheet to show Ms Martyn how to apply this technique of improving data quality. 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. Marking Guide Tasks Marks Observations (All students): a. 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. b. 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. c. What are FFC's best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations. d. 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 Recommendations (All Students): e. Should FFC focus on any particular sales activities in future, and why? f. What are your recommendation regarding what other data should be collected to improve decision making for FFC? g. 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 Submission Presentation: h. 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)