Sales Tracking and Customer Relation Analyses

Questions: 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 companys 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 companys 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 years 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 consultancys 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: 1. Phone. Use Pivot Table(s) to summarise these calculations. Calculationsa. 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.Observationse. 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 a nd (b) time taken for 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. Answers: Calculation a. As per the gave information set of the Fed Fone Co. (FFC's) offering portable or telephone and additionally their extras in its presumed shop in Ballarat range. The Fed Fone Co. (FFC) is arranged in three better places in Ballarat zone like High Street, Mall and Suburb. The three shops of Fed Fone Co. (FFC) are 7 days open, thus they ascertain the aggregate salary, costs and benefit for every extensions of organization. They additionally need to enhance the offers of the organization, inward efficiencies, adequacy of customers or clients and they likewise need to manufacture a decent relationship between every clients or customers of the shop by the assistance of a few different investigations of salary, costs and benefit (Beynon-Davies, Galliers Sauer, 2009). Along these lines, the aggregate salaries of the shop as per its extensions are indicated in beneath. The aggregate pay or total income of Fed Fone Co. (FFC) is 445017 $ Furthermore, the pay or total income of different extensions of Fed Fone Co. (FFC) like High Street is 208837 $ The pay of different limbs of or total income Fed Fone Co. (FFC) like Mall is 174036 $ The pay of different limbs of or total income Fed Fone Co. (FFC) like Suburb is 62144 $ In above all the data are analysis outcomes of the giving data set of Fed Fone Co. (FFC). All the data are providing in table form, which are calculating by the help of, excel advance analysis. b. According to provided data set of the Fed Fone Co. (FFC), the aggregate cost or total expenses of business model or Fed Fone Co. (FFC) are measuring by the help of excel advance analysis. The aggregate cost or total expenses of business model or Fed Fone Co. (FFC) are discus in below. The aggregate cost or total expenses of Fed Fone Co. (FFC) is 316105 $ Also, the cost or total expenses of different extensions of Bolstered Fone Co. (FFC) like High Street is 160204 $ The cost or total expenses of different limbs of Encouraged Fone Co. (FFC) like Mall is 115115 $ The cost or total expenses of different limbs of Encouraged Fone Co. (FFC) like Suburb is 40786 $ c. The benefit or profit is the most important term as well as valuable data for every business model. The benefit or profit of the business model is calculating or measuring by the help of total income of the business model and total expenses of the business model in same period (Moutot Bascoul, 2008). According to the provided data set of Fed Fone Co. (FFC), the profit or benefit is analysis or calculating. The profit values of the business model or Fed Fone Co. (FFC) are shown in below. Thusly, the aggregate benefit/ profit of Fed Fone Co. (FFC) = (downright salary or income aggregate cost or total expenses) = 128912 $ Similarly, the benefit/ profit of different limbs of Fed Fone Co. (FFC) like High Street is 48633 $ The benefit/ profit of different limbs of Fed Fone Co. (FFC) like Mall is 58921 $ The benefit/ profit of different limbs of Fed Fone Co. (FFC) like Suburb is 21358 $ d. According to the provided data set of the Fed Fone Co. (FFC), All out salaries or income via repair = 720 $ Aggregate costs via repair = 14* 50 = 700 $ Therefore, the total benefit or profit via repairing the old handsets or phone is (720 700) $ = 20 $ Observation e. The Fed Fone Co. (FFC) is provided various phone plans for clients or customers of the shop. According to the provided data set of Fed Fone Co. (FFC) are demonstrated in beneath. 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 The basic graphical outline of the phone plans are demonstrated in underneath according to their uses and plan name. Along these lines, the most provided phone plans gave by Fed Fone Co. (FFC) is Optrus plan 1 furthermore minimum arrangement gave by Fed Fone Co. (FFC) is Vodrfon plan 2. f. By the help analysis of the provided data set it is clear that the most exceedingly terrible telephone offering by the Fed Fone Co. (FFC) is Apel v17 telephone. g. In the Fed Fone Co. (FFC) business model, the representatives are communicating with every client or customers of the shop. The sale as well as the profit of the business model is also depends on the customers, for that reason the representative of Fed Fone Co. (FFC) is more important part of its business strategy (Sanchez Freiling, 2005). According to the provided data the sale of the Fed Fone Co. (FFC) by the representative is discussed in below, which is recorded by the help of the last three months sales in Fed Fone Co. (FFC) shop and its other branches. Name of representative Sale no George 281 John 212 Paul 408 Ringo 221 Stuart 276 The basic graphical outline of the sales are demonstrated in underneath, which is recorded according to the representative of sale or who communicate with clients. As indicated by most recent three months information set, the best illustrative is Paul and most exceedingly awful illustrative is John. Recommendations Shoppers once depended on a recognizable businessperson, for example, the proprietor of their neighborhood general storeto help them find simply what they needed. Drawing on what he knew or could rapidly derive about the client, he would place the ideal item and, regularly, recommend extra things the client hadn't even considered. It's a curious situation. Today's occupied shoppers, shelled with data and choices, frequently battle to discover the items or administrations that will best address their needs (Sutton, 2011). The in need of help and frequently inadequately educated floor staff at numerous retailing locales can't start to repeat the individual touch that customers once relied on upon and purchasers are still to a great extent all alone when they shop on the web. This sorry condition of issues is evolving. Advances in data innovation, information social event, and investigation are making it conceivable to convey something likeor maybe far better than the proprietor's recommendation. Utilizing progressively granular information, from point by point demographics and psychographics to shoppers' click streams on the web; organizations are beginning to make profoundly redone offers that control customers to the "right" stock or administrations at the right minute, at the right cost, and in the right channel, these are called "next best offers (Zhang, 2010). Consider Microsoft's prosperity with email offers for its web crawler Bing. Those eà ¢Ã¢â€š ¬Ã¢â‚¬Ëœmails are custom-made to the beneficiary right now they're opened. In 200 milliseconds, a slack impalpable to the beneficiary progressed investigation programming gathers an offer focused around ongoing data about him or her: information including area, age, sex, and online movement both record ed and instantly going before, alongside the latest reactions of different clients. These promotions have lifted change rates by as much as 70%significantly more than comparable yet un-customized advertising. The innovations and techniques for making next best offers are advancing, however organizations that hold up to endeavor them will see their clients deformity to contenders that lead the pack. Microsoft is only one illustration; different organizations, as well, are uncovering the business capability of decently created Next best offer (NBO)s. Anyway in our examination on Next best offer (NBO)methodologies in many retail, programming, monetary administrations, and different organizations, which included meetings with administrators at 15 organizations in the vanguard, we found that if Next best offer (NBO)s are carried out whatsoever, they're frequently done inadequately (Williams, 2008). Most are aimless or poorly focused on pitches to clients who have as of now purchased the offering, for instance. One retail bank found that its Next best offer (NBO)s were more prone to make malevolence than to expand deals. Organizations can seek after horde great objectives utilizing client examination, yet Next best offer (NBO) projects give maybe the best esteem as far as both potential return on initial capital investment and upgraded intensity (Antonov, 2013). In this article we give a system to creating Next best offer (NBO)s. You will be unable to embrace all the steps immediately, yet advance on each one will be fundamental eventually to enhance your offers. To improve the data quality of the data sets by the help of the data management or data administration, data recording as well as data monitoring. These three main parameters are improving the data set as well as the business model. Data administration alludes to the accumulation, stockpiling, transforming/ investigation, dispersal and productive utilization of data in the setting of observing and assessment. Information gathering may happen on a progressing premise, at consistent interims, or as a feature of a one-off assessment. Record-keeping alludes to the efficient recording of data in institutionalized configurations. It is frequently likewise comprehended to mean the stockpiling of such data. Monitoring of the useful data indicated Project Strategy and System Manual, there are two sorts of Checking: Situation checking measures change in a condition or a set of conditions or absence of progress. Checking the circumstances of youngsters and ladies and objectives. It additionally incorporates observing of the more extensive connection, for example, early cautioning checking, or checking of financial patterns and the nation's more extensive strategy, monetary or institutional setting (Foreman, 2008). Performance checking measures advance in attaining to particular results in connection to a usage plan, whether for projects, methods, or exercises. It is center responsibility for compelling work arranging and audit. To improving the sales in Fed Fone Co. (FFC), Ms Martyn are adopted the basic rules of data administration, data monitoring and data records, which are discus in below. They structure the key premise of observing, usage and assessment. They shield against infringement of rights. Process and results of preoccupation and options must be obviously recorded to guarantee straightforwardness, responsibility and subsequent where essential. Systematization and clear documentation of arrangements and methodology are vital to draw clear lessons from projects and encourage scaling-up or replication. Quality information gathering for preoccupation and plan B projects can help to fortify/ enhance the accumulation of solid factual information for the tyke equity framework all in all. Ongoing checking is vital to guarantee the proficient and viable running of an undertaking or system. It guarantees progress towards objectives. It is important to guarantee that a task or system is considered responsible to its recipients and benefactors (counting citizens if financed from open sources). It serves to distinguish issues from an early stage and mediate in an opportune way to intention them which can bring about time and expense reserve funds. If done well it can help absolutely to group assurance and foster an environment of straightforwardness and professionalism. It can assemble open and political backing for a project and answer stakeholders' inquiries. Fed Fone Co. (FFC) is a great plan of action or framework. The offer of the Fed Fone Co. (FFC) is measure by the assistance of the discriminating administration procedure. Nevertheless most arrangements appraisals are, by nature, erroneous. The trap, masters say, is to know in which heading they're wrong, and change that into a picture of how your business is getting along. Fed Fone Co. (FFC) that could mean plotting all activity transversely over divisions to see how your things are matching up to industry benchmarks (Petrescu, 2012). There are a few methodologies to test the business gagging to know whether you're getting a careful read or essentially fiddling in luxurious soothsaying. One of the best misinterpretations about assessing is that there's one arranged of numbers that addresses "reality" for your business. When in doubt, distinctive figures are critical with a particular deciding objective to address the needs of assorted electorates. It's hard to use a singular test that will promise you can track the exact terms, time, and setting of each one arrangement. Rather, you should focus on making a process that can be regulated, rethought, and changed as conditions change. The best system for Fed Fone Co. (FFC) is relies on upon their clients or customers. On the off chance that the Fed Fone Co. (FFC) are given free overhauling to new telephone acquiring than the criticism clients or customers are constantly positive (Thierauf, 2001). The clients or customers are pulled in by the assistance of different plans like free adjusting, calling following 3 or 4 months and get input for telephone or versatile. The deal anticipating or offers of Fed Fone Co. (FFC) are focused around its client's criticism and quality on telephone or portable. References Antonov, A. (2013). New business-oriented global/regional information network.IJBIS,12(3), 321. doi:10.1504/ijbis.2013.052453 Beynon-Davies, P., Galliers, R., Sauer, C. (2009).Business information systems. Basingstoke, Hampshire: Palgrave Macmillan. Clifton, H., Sutcliffe, A., Clifton, H. (1994).Business information systems. New York: Prentice Hall. Customer Relationship Management. (2013).Sales Bus,22(1-2), 46-47. doi:10.1365/s35141-013-0543-3 Davis, C., Haefele, E. (2010). A practical and tested infrastructure design for large business warehouse systems.Business Information Review,27(1), 43-55. doi:10.1177/0266382110361279 Foreman, J. (2008).The performance implications of planning, implementation, and evolution of market-oriented strategy by top management. Moutot, J., Bascoul, G. (2008). Effects Of Sales Force Automation Use On Sales Force Activities And Customer Relationship Management Processes.Journal Of Personal Selling And Sales Management,28(2), 167-184. doi:10.2753/pss0885-3134280205 Petrescu, M. (2012). Cloud computing and business-to-business networks.IJBIS,10(1), 93. doi:10.1504/ijbis.2012.046682 Sanchez, R., Freiling, J. (2005).A focused issue on the marketing process in organizational competence. Amsterdam: Elsevier JAI. Sutton, D. (2011).How to sell your crafts online. New York: St. Martin's Griffin. Thierauf, R. (2001).Effective business intelligence systems. Westport, CT: Quorum Books. Wiele, T., Boselie, P., Hesselink, M. (2001).Empirical evidence for the relation between customer satisfaction and business performance?. Rotterdam: Erasmus Research Institute of Management, Erasmus Universiteit. Williams, G. (2008).Implementing SAP ERP sales distribution. New York: McGraw-Hill. Zhang, J. (2010).ICLEM 2010. [Reston, Va.]: American Society of Civil Engineers.