Sales Tracking and Customer Relations Analyses

Question: Describe the Sales Tracking and Customer Relations Analyses? and to analyse a set of data, and write a brief report, identifying and explaining your insights into the operation of The FedFone Co.? Answer: Executive Summary Businesses evolve around data, now a days, every business process of any organization generates a lot of data. Analysis of those data gives critical insight in the business process, future scopes and strategies etc. FFC or FedFone Co. is such a business in Ballarat area. It sells mobile phones, accessories, mobile phone insurance plans from 3 shops in the Ballarat area. Now the business is planning for expansion. Thus the owner wants to make new business strategies, improve customer relationships. In order to do so, the business needs to analyze the sales data from previous months. Calculations In this section the outcomes of the sales data based on different criteria will be discussed. The total income, expense and profits will be analyzed by different combinations of the shops, staffs, phone models etc. The company needs to pay $14 for postal charge, whenever, there is a phone repair. The phone manufacturing companies reimburse charge but that may be partially or totally. The income and expense from phone repair will also be analyzed. 1. Following pivot table shows the outcome of the analysis of total income based on different shops, staffs and phone modes. The sub totals of these three categories have been also there. 2. Following pivot table shows the outcome of the analysis of total expense of FFC, based on the shops and staffs. The subtotals of each of the two groups have been also there. 1. Following pivot table shows the outcome of analysis of total profit of FFC based on the shops, staffs and the phone models. The sub totals of each group also have been there. 2. Following pivot table shows the outcome of analysis of the repair income and expenses based on phone models, staffs and shops. The sub totals of each category have been also given. Observations In this section the outcomes of the analysis of sales information of FFC will be observed to find different best and worst cases. For example, the best and worst selling phone plans, the best and worst performing sales staff in terms of total sales, changes in total profit if the policy of charging for training service is changed etc. 1. Following chart shows the comparison of totals sales of different phone plans. The values will be observed to find out the best and worst selling phone plans. According to the chart, Optrus plan 1 and Vodrfon plan 2 are the best and worst selling phone plans respectively. 2. The following chart compares the number of repairs and the total time for those repairs, for different phone models sold by FFC. According to the observation of the chart, Apel v17 phone is the worst phone that have the most number of repair cases and the most duration for the same. 1. Following chart shows the comparison of the total sales made by the staffs. Based on the information in the chart, the best and worst staffs are Paul and Ringo respectively. 2. Following chart compares the profit with fixed rate training service to the profit with variable rate hourly charged training service. It is observed from the chart that the profit is more in the latter case. Recommendations: In this section the recommendation for FFC will be outlined. The recommendations are based on the current business process of FFC and the results from all calculation and observations carried out so far. 1. For improving the sales activities in future, FFC should take care of the different shops and staffs. All shops and staffs are not performing same and satisfactory levels. Particularly, FFC should focus on the sales activities in the Suburb area, on the sales staff like Ringo, who are not performing well, and on products other than phone models. 2. For improving the decision making activities, FFC should collect more information. Other than sales records it should focus on collecting customers demographics. These will help in building better customer relationships in future. The data should be collected in proper format and should be cleaned before being used in data analysis. 3. Data validation restricts input to different fields and reduces the scope of erroneous data input, manual data entry errors etc. In the Sales datasheet, column A has dates. But there is no validation on the cells under this column. Thus anything other than a valid date can be entered. Also there may be outdated entry. A validation specifying range of possible inputs and proper format of the data will help in restricting the errors and data entry. In the column B, the valid input will be any mane of the three shops. But, there can be any string without proper validation. The valid input in column M should also be any whole number from 0 to 24. These validations in these columns will help in better record keeping and organizing. References: Albright, S., Winston, W., Zappe, C. (2010). Data Analysis and Decision Making. Cengage Learning. Gross, D., Akaiwa, F., Nordquist, K. (2013). Succeeding in Business with Microsoft Excel 2013: A Problem-Solving Approach. Cengage Learning. Jelen, B. (2010). PowerPivot for the Data Analyst. Pearson . Jelen, B., Alexander, M. (2013). Excel 2013 Pivot Table Data Crunching. Que Publishing. Ralston, B. (2011). PowerPivot for Business Intelligence Using Excel and SharePoint. Apress. Winston, W. L. (2009). Microsoft Excel Data Analysis and Business Modeling. Microsoft Press. Sales Tracking and Customer Relations Analyses Questions: Tasksa. 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?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 f or repair, for the three month period? Use an appropriate table and graph/chart to summarise these observations?g. What are FFCs 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? Answers: Executive summary Ms. Georgie Martyn, was stated a mobile sale and service shop, which is situated near Ballart area. Ms. Georgie Martyns mobile sale and service company name is Fed Fone Co. (FFC). Ms. Georgie Martyn or Fed Fone Co. (FFC) is situated in three different ranges in Ballart area like one shop is located in High Street; second shop is located in near Mall zone and the last or third shop is located in Suburd zone. The Fed Fone Co. (FFC) or Ms. Georgie Martyns shops are open regularly in week or 7 days. Ms. Georgie Martyn is measuring and monitoring each and every sells or service data based using its own dataset or spared sheet. By the using of the Ms. Georgie Martyn or Fed Fone Co. (FFC)s spared sheet or dataset we can easily measured or calculated the total income, expenses, profit of different shops of Fed Fone Co. (FFC) by the help of dataset analysis. The dataset or spared sheet is analysis by the help of the Microsoft excel programmed (Griffin Ebert, 2006). By the help of the dataset outcomes or spared sheet outcomes Ms. Georgie Martyn want to analysis the customers behavior. Ms. Georgie Martyn is also wanted to improve the sale efficiency as well as services of Fed Fone Co. (FFC). Ms. Georgie Martyn is wanted to consist or build a good customers relationship by the help of its company services. For that reason we analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC) recoded dataset or spared sheet and the outcomes are shown in below in form of total profit, incomes and expenses of the Ms. Georgie Martyn s shop or Fed Fone Co. (FFC). Calculation a. By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total income of different branches like High Street zone shop is $211880, shop located in Mall is $175469 and Suburd zone shop is $63686. Therefore, according to the analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC)s total income within the three months (1st January to 30th March) is High Street zone shop income + Mall located shop income + Suburd zone shop income. = $(211880+ 175469 + 63686) = $451035 b. The total expences of the Ms. Georgie Martyn or Fed Fone Co. (FFC) is calculating or measuring by the help of the provided data set or spared sheet. The giving dataset or spared sheet of the business model is analysis by the help of the advance excel. By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total expenses of different branches like High Street zone shop is $158553, shop located in Mall is $114038 and Suburd zone shop is $38982. Therefore, according to the analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC)s total expenses within the three months (1st January to 30th March) is High Street zone shop expenses + Mall located shop expenses + Suburd zone shop expenses. = $(158553+ 114038 + 38982) = $311573 c. The total profit of the Ms. Georgie Martyn or Fed Fone Co. (FFC) is calculating or measuring by the help of the provided data set or spared sheet. The giving dataset or spared sheet of the business model is analysis by the help of the advance excel (Gunasekaran Sandhu, 2010). By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total profit of different branches like High Street zone shop is $53327, shop located in Mall is $61431 and Suburd zone shop is $24704. Therefore, according to the analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC)s total profit within the three months (1st January to 30th March) is High Street zone shop profit + Mall located shop profit + Suburd zone shop profit. = $(53327+ 61431 + 24704) = $139462 The income via repair of the Ms. Georgie Martyn or Fed Fone Co. (FFC) are measured and analysis by the help of the excel analysis. By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total income via repairing the phone or mobile is $710 as well as total expenses of the three shops of Ms. Georgie Martyn or Fed Fone Co. (FFC) is $1036. Observation d. Ms. Georgie Martyn or Fed Fone Co. (FFC) has various types of mobile plans and services for customers requirements. According to the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) is shown in below by the help of table, which is analyzing by the help of advance excel. Plan No Telstrar plan 1 249 Telstrar plan 2 140 Optrus plan 1 442 Optrus plan 2 327 Raldi plan 225 Vodrfon plan 1 17 Vodrfon plan 2 8 According to the above discussion, the graphical representation of the phone plans of Fed Fone Co. (FFC) is shown in below. The total income ($452071) via repairing is calculating by the help of the spared sheet, which is shown in below. Plan Total Income Optrus plan 1 135857 Optrus plan 2 109734 Raldi plan 57108 Telstrar plan 1 92444 Telstrar plan 2 9235 Teltsrar plan 2 40936 Vodrfon plan 1 4550 Vodrfon plan 2 2207 According to the above graphical representation and excel analysis, Optrus plan 1 is the most provided phone plan and Vodrfon plan 2 is least provided phone plan by Fed Fone Co. (FFC). e. According to the provided dataset or spared sheet, Aple v17 phone is worst phone in Fed Fone Co. (FFC). f. The Sale of the Fed Fone Co. (FFC) is directly depends on the service and quality of the phone as well as customers. The shop representatives are the most important part because the shop representatives are communicated with customers and sell their products as per their requirements and solving each and every query. According to the giving spared sheet, the sale no is measured by the help of shop representative, which is shown in below. Name of representative Sale no George 281 John 212 Paul 408 Ringo 221 Stuart 276 According to the above table, the graphical representation of sale no and shop representative is shown in below. According to the above graphical representation and excel analysis, Paul is best shop representative and Ringo is worst shop representative in Fed Fone Co. (FFC) for last three months (1st January to 30th March). g. The sum of new profit in brown and total profit in blue is shown in below by the help of graphical representation. Recommendations h. The total sum of incomes of Fed Fone Co. (FFC) is shown in below. Values High Street Mall Suburb Total Income from Phone Model 194140 161610 47525 403275 Income from Phone Plan 3475 3892 1584 8951 Income from Phone Cover 2838 1810 2997 7645 Income from Spare Battery 3650 4500 3590 11740 Income from Charge Pack 2360 2080 4320 8760 Income from Software Update 315 90 215 620 Income from Training Service 4960 1560 3000 9520 Ms. Georgie Martyns Company or Fed Fone Co. (FFC) is a good organization, where the whole business model is analysis by the help of the critical management strategy. By the help of the critical management strategy Ms. Georgie Martyn is easily analysis the whole business model and improving the weak part (Messner, 2007). The best business strategy of Fed Fone Co. (FFC) is consisting by the help of customers dealing and services. If the shop member are in contact with each and every customers or clients of the company than customers feedback are easily get. If Ms. Georgie Martyns Company or Fed Fone Co. (FFC) is provided some free phone services for every purchasing of new phone and also collecting feedback after some few months, than all the customers are feel good about the services and always come to the shop. References Foulds, L., West, M. (2007). The productivity of large business information system development.IJBIS,2(2), 162. doi:10.1504/ijbis.2007.011617 Griffin, R., Ebert, R. (2006).Business. Upper Saddle River, N.J.: Pearson/Prentice Hall. Gunasekaran, A., Sandhu, M. (2010).Handbook on business information systems. Singapore: World Scientific. Law, W., Perez, K. (2005).Cross-cultural implementation of information system. Hershey, PA: Idea Group Pub. Messner, W. (2007). Justifying information system value: Development of a method for measuring customer advisory system effectiveness.Business Information Review,24(2), 126-134. doi:10.1177/0266382107078865 Pedley, P. (2000). Implementing and project managing a new library management system.Business Information Review,17(1), 43-49. doi:10.1177/0266382004237458 Sarngadharan, M., Minimol, M. (2010).Management information system. Mumbai [India]: Himalaya Pub. House. Yeh, C., Lee, G., Pai, J. (2012). How information system capability affects eà ¢Ã¢â€š ¬Ã‚ business information technology strategy implementation.Business Process Mgmt Journal,18(2), 197-218. doi:10.1108/14637151211225171