Business Intelligence for EcoClean Company - Assignment Example

Unit code and name Project: Business Intelligence Report for EcoClean Company Name ID Lecture’s name Date WC 1542 EXECUTIVE SUMMARY This report is made by the Business Intelligence Consultant for the management of EcoClean Co. This report reflects data gathered from 2010-2012 for the company’s sales staff performance and annual performance for their product lines kitchen, cleaning, and laundry items in three retain channels in Sydney, Brisbane, and Melbourne. Excel and Access tools have been used in the critical evaluation and analysis of these data hence providing an explanation of the current situation in performance, and recommends the way forward for the company. The findings show that the sales team from Sydney has performed the highest within the three years while that from Melbourne had the least performers. Laundry Cleaner is EcoClean’s highest cash cow product while Window’s cleaner is the least. EcoClean’s products are environmental friendly and have potential to be well sold in the three states of Australia which have a vibrant hospitality industry and steady economic growth. Therefore, the actions to be taken for performance improvement should focus on staff performance, and marketing strategies. The recommendations provided are as follows: Enhance sales staff training strategies for Melbourne. Increase staff motivation through incentives, team building, and similar strategies to help low performers improve, and high performers to be consistent. Retain or improve strategy for marketing Laundry Liquid, and for all the improving and consistent products in relevant states. Conduct market research by contacting consumers about their perspectives on products such as Window Cleaner appears to perform poorly. Focus on alternative marketing strategies, especially online marketing which has shown to generate the highest sales in the three year with very little investment on marketing costs. Table of Contents EXECUTIVE SUMMARY 2 Table of Contents 3 1.Introduction 4 2.Management Performance Report for 2012 4 2.1Sales Performance by Channel 4 2.2Sales Performance by Product Category 5 2.3 Sales Performance by Individual Products 6 2.4 Significant Findings 7 3.Criteria-Related Report for 2010-2012 7 3.1 Criteria-Related Performance by Product Category 7 3.2 Criteria-Related Performance by Product 8 3.3 Sales versus Marketing Costs for each Channel 9 3.4Trend Analysis 11 4. Ranking Report 12 4.1 Top Five Products for EcoClean Co (Cash cows) 12 4.2 Product Rank by Category 13 4.3 Product Rank at State Level 15 4.4 Status of each Product in each State 17 4.5 Analysis of performance across the company 18 5. Recommendations 18 6. Conclusion 19 Reflection on Data Issues 19 References 20 Appendices 21 Appendix 1: Contribution towards sales by each sales person 21 Appendix 2: Product Category and description 21 Appendix 3: Product ID and retail cost 22 Appendix 4: Trends in business environment 22 Appendix 5: consumer price inflation 2011 23 Business Intelligence Report for ЕсоСlеаn Co 1. Introduction This report is established after analysis of performance data for EcoClean Co. using Excel and Access for critical business intelligence analysis. The purpose of the report is to provide a critical analysis comparing bench marks and real trends on the annual sales and sales staff performance of EcoClean Company for their kitchen, cleaning, and laundry products in the company’s three retail outlets in Sidney, Brisbane, and Melbourne for the years 2010, 2011, and 2012. The report addresses the following: First, the management performance of each channel in the year 2012 based on a 95% benchmark; second, criteria-related performance by channel; and third, ranking for each product for each channel. 2. Management Performance Report for 2012 This section of the report addresses and compares the sales performances in the three channels for the year 2012 when a 95% benchmark was set by the CEO for all sales. 2.1 Sales Performance by Channel The 2012 total sales for Sydney were $ 233,071 or 103% of the targeted sales. The total sales for Brisbane were $216,403 or 98.5% of the targeted sales. Meanwhile, the total sales for Melbourne were $44,545.50 or 80.8 % of the targeted sales (See Appendix 1 for each sales rep contribution to the company). All the sales representatives from Sydney performed above the benchmark leading to highest sales in Sydney. Meanwhile, all sales representatives from Melbourne performed below the benchmark leading to low sales generated by the state. The three states have similar business environment and heritage (Victorian Government Department of Infrastructure, 2000), in addition to similar products being marketed by the company. Therefore Sydney and Brisbane performed well because of the aggressiveness of the sales team as compared to that of Melbourne. It is possible that the Melbourne team a change of management tactics to improve the sales. Graph 1: Sales performance and targets for each sales person. 2.2 Sales Performance by Product Category By product category for each state, Sydney surpassed the benchmark in all the three categories with cleaning products giving $68,886 or 99.83% of the target sales, kitchen products giving $59,924 or 105.13% of targeted sales, and laundry giving $104,261 or 104.26% of targeted sales. Brisbane passed the benchmark with cleaning and laundry products at $59,732 or 96.34% and $$106,671 or 118.52% of the target sales respectively. Kitchen products sales were below the benchmark at $50,000 or 80.65% of the target sales. All of Melbourne’s product category sales were below the benchmark with cleaning at $12,303.50 (76.9%), kitchen at $13,864 (81.55%) and laundry at $18, 378.50 (83.54%) of targeted sales respectively (Appendix 2 product category description). Laundry products have the highest sales while kitchen products have the least and this could be based on the logic that more laundry items are used as compared to kitchen items hence there is more washing done for laundry leading to higher purchase of laundry products. Graph 2 showing sales performance by product category 2.3 Sales Performance by Individual Products Laundry products include Laundry Liquid, Laundry Powder, Fabric Softener and Pre-Was Soaker whose total 2012 sales were $229,310 or 102.1 % of the target sales. Kitchen products include Dish Washing Liquid with Chamomile, Machine Dishwashing Powder with Citrus, and Disinfectant with Lemongrass whose total 2012 sales for the company were $123,788 or 89.11% of the target sales, this being below the benchmark. Cleaning products include Stainless Steel Polish, Multipurpose Cleaner with Grapefruit, Floor Cleaner, Window Cleaner and Furniture Polish whose total 2012 sales for the company were $140, 921.50 or 91.02% of the target sales. (See Appendix 3 for information in product ID, name, and retail cost). 2.4 Significant Findings Sydney is the highest selling state while Melbourne is the lowest selling state for EcoClean Co and reason likely to be based on variations in sale’s team aggressiveness/tactics because products and business environment are similar. (See Appendix 4 for presentation of office space use in the three states). Laundry products generated the highest sales for the company, while kitchen products generated the least. Most kitchen product washers are yet to penetrate the Australian retail market as people are sceptical of using unhealthy products on items that can be used to serve food. EcoClean needs to educate its customers about the products being safe and environmental friendly. 3. Criteria-Related Report for 2010-2012 This section focuses on the performance of actual product lines based on marketing strategies in order to draw out comparisons and trends across the years 2010 to 2012 for the company. 3.1 Criteria-Related Performance by Product Category Cleaning products sales across the three channels were at $68,090 in 2010, $166,735 in 2011, and at $140, 921.5 in 2012. Kitchen products sales were at$51,476 in 2010; $120,428 in 2011, and $123,788 in 2012. Laundry products sales were at $94,697 in 2010, $ $284,502.50 in 2011, and at $229,310.50 in 2012. Laundry products gave the highest total sales in the three years while kitchen products gave the least sales. Sydney Product Category Category 2010 2011 2012 Total Cleaning $36,583.00 $87,618.50 $68,886.00 $193,087 Kitchen $25,563.50 $62,518.00 $59,924.00 $148,005.50 Laundry $53,697.50 $132,714.50 $104,261.00 $290,673 Brisbane Product Category Category 2010 2011 2012 Total Cleaning $25,423.00 $61,631.00 $59,732.00 $146,786 Kitchen $19,270.00 $42,909.00 $50,000.00 $112,179 Laundry $35,490.00 $123,518.00 $106,671.00 $265,679 Melbourne Product Category Category 2010 2011 2012 Total Cleaning $6,092.50 $17,486.00 $12,303.50 $35,882 Kitchen $6,643.00 $15,001.00 $13,864.00 $35,508 Laundry $5,510.50 $23,270.50 $18,378.50 $47,159.50 3.2 Criteria-Related Performance by Product Individual products generated the highest sales in 2011 than any other year as shown in the tables below. Sydney Product Product Name 2010 2011 2012 Dishwashing Liquid with Chamomile $7,182.00 $22,344.00 $15,960.00 Disinfectant with Lemongrass $10,043.50 $22,361.00 $23,877.00 Fabric Softener $11,735.50 $35,685.50 $21,315.50 Floor Cleaner $12,768.00 $22,144.50 $22,144.50 Furniture Polish $3,349.50 $14,514.50 $9,251.00 Laundry Liquid $19,489.50 $36,742.50 $46,327.50 Laundry Powder $15,486.50 $35,586.00 $23,394.50 Machine Dishwashing Powder with Citrus $8,338.00 $17,813.00 $20,087.00 Multipurpose Cleaner with Grapefruit $10,174.50 $23,740.50 $17,556.00 Pre-Wash Soaker $6,986.00 $24,700.50 $13,223.50 Stainless Steel Polish $5,582.50 $16,269.00 $10,846.00 Window Cleaner $4,708.50 $10,950.00 $9,088.50 Brisbane Product Product Name 2010 2011 2012 Dishwashing Liquid with Chamomile $6,384.00 $16,758.00 $14,563.50 Disinfectant with Lemongrass $6,253.50 $14,212.50 $21,413.50 Fabric Softener $7,185.00 $23,950.00 $19,639.00 Floor Cleaner $6,583.50 $12,768.00 $14,563.50 Furniture Polish $5,104.00 $9,889.00 $11,165.00 Laundry Liquid $15,016.50 $38,020.50 $50,800.50 Laundry Powder $10,544.00 $42,835.00 $28,996.00 Machine Dishwashing Powder with Citrus $6,632.50 $11,938.50 $14,023.00 Multipurpose Cleaner with Grapefruit $5,187.00 $15,361.50 $15,361.50 Pre-Wash Soaker $2,744.50 $18,712.50 $7,235.50 Stainless Steel Polish $5,263.50 $14,195.50 $11,962.50 Window Cleaner $3,285.00 $9,417.00 $6,679.50 Melbourne Product Product Name 2010 2011 2012 Dishwashing Liquid with Chamomile $3,990.00 $4,389.00 $4,389.00 Disinfectant with Lemongrass $2,653.00 $6,632.50 $5,116.50 Fabric Softener $239.50 $4,311.00 $2,874.00 Floor Cleaner $2,793.00 $1,396.50 $2,593.50 Furniture Polish $478.50 $3,030.50 $1,595.00 Laundry Liquid $3,195.00 $5,751.00 $8,307.00 Laundry Powder $329.50 $10,214.50 $3,954.00 Machine D/washing Powder with Citrus $3,979.50 $4,358.50 Multipurpose Cleaner with Grapefruit $997.50 $5,386.50 $3,192.00 Pre-Wash Soaker $1,746.50 $2,994.00 $3,243.50 Stainless Steel Polish $1,276.00 $5,263.50 $3,828.00 Window Cleaner $547.50 $2,409.00 $1,095.00 3.3 Sales versus Marketing Costs for each Channel Total marketing costs for the three channels in 2010 were $ 30,000 while total sales were $214,273. Total marketing costs in 2011 were $37,500 while total sales were $566,666. Total marketing costs in 2012 were $ 35,500 while total sales were $494,020. Findings revealed that the year 2011 used the highest marketing costs of the three and generated the highest sales. An ABS survey (See Appendix 5) revealed that consumer price inflation went down for most of the states in 2011 (Lane et al 2012), hence this explains the highest purchases that were made in this year. Online marketing costs versus sales Online sales were consistently higher in comparison to the marketing costs. 2010 2011 2012 Online $ 500.00 $ 236,913.50 $ 1,000.00 $ 727,377.50 $ 600.00 $631,777.50 3.4 Trend Analysis Generally, the year 2011 yielded the most sales for each product category in the three channels. Laundry products were the most high-selling for the company across the 2010-2012 at $603, 511.50 (47.3%) followed by cleaning products at $375,755 (29.5%), and finally kitchen products at $295,692.50 (23.2%). All states had their highest sales in 2011 than in 2010 and 2012. Sydney’s marketing costs at $37,500 was the highest but the state also had the highest sales and provided 50% of the company’s sales in the three years. Brisbane with $35,500 marketing costs provided 41% of the total sales. Melbourne with $30,000 marketing costs provided 9% of the total sales. On the other hand, online sales have been escalating even with the least investment in marketing costs. According to Woodings (2012) the retail sector in Australia is changing, and a strong online presence is essential for survival. Thus, EcoClean needs to focus on customer experience in-store and online. 4. Ranking Report This section focuses on analysis ranking of performance in regards to individual products by category during the three years, and also the performance status of each product for each state. The section reveals the Cash Cows for the company and the low selling products/channels. 4.1 Top Five Products for EcoClean Co (Cash cows) These are the products that have consistently generated the highest sales: 1) Laundry Liquid ($333,762); 2) Laundry Powder ($201,628); 3) Multipurpose Cleaner with Grapefruit (200,675); 4) Floor Cleaner ($191,642.50); and 5) Fabric Softener ($190,973). 4.2 Product Rank by Category Laundry Liquid (LAU 001) was the highest in performance ranking in the laundry category. Table 3 - Product Category - Laundry   Ranking Product ID 2010 2011 2012 LAU001 1 1 1 LAU002 2 2 2 LAU003 4 4 4 LAU004 3 3 3 Multipurpose cleaner with grapefruit (CLN002) was the highest in the cleaning category Ranking by Product Category Table 1 - Product Category - Cleaning   Ranking Product ID 2010 2011 2012 CLN001 3 4 3 CLN002 2 1 1 CLN003 1 2 2 CLN004 5 5 5 CLN005 4 3 4 Machine Dishwashing powder with citrus (KIT 002) was highest in the kitchen category Table 2 - Product Category - Kitchen   Ranking Product ID 2010 2011 2012 KIT001 2 2 2 KIT002 1 1 1 KIT003 3 3 3 4.3 Product Rank at State Level In all the three states, Laundry Liquid ranked as the highest selling product while Window Cleaner was ranked the least selling, in category of a laundry and a cleaning product respectively. Sydney Product Name Overall Rank Laundry Liquid 1 Laundry Powder 2 Pre-Wash Soaker 3 Fabric Softener 4 Machine Dishwashing Powder with Citrus 5 Multipurpose Cleaner with Grapefruit 6 Disinfectant with Lemongrass 7 Dishwashing Liquid with Chamomile 8 Floor Cleaner 9 Stainless Steel Polish 10 Furniture Polish 11 Window Cleaner 12 Brisbane Product Name Overall Rank Laundry Liquid 1 Laundry Powder 2 Pre-Wash Soaker 3 Fabric Softener 4 Machine Dishwashing Powder with Citrus 5 Multipurpose Cleaner with Grapefruit 6 Disinfectant with Lemongrass 7 Floor Cleaner 8 Dishwashing Liquid with Chamomile 9 Stainless Steel Polish 10 Furniture Polish 11 Window Cleaner 12 Melbourne Prodcut Name Overall Rank Laundry Liquid 1 Dishwashing Liquid with Chamomile 2 Pre-Wash Soaker 3 Laundry Powder 4 Fabric Softener 5 Disinfectant with Lemongrass 6 Machine Dishwashing Powder with Citrus 7 Floor Cleaner 8 Multipurpose Cleaner with Grapefruit 9 Furniture Polish 10 Stainless Steel Polish 11 Window Cleaner 12 Product Rank Online Online Product Name Overall Rank Laundry Liquid 1 Laundry Powder 2 Pre-Wash Soaker 3 Fabric Softener 4 Machine Dishwashing Powder with Citrus 5 Dishwashing Liquid with Chamomile 6 Multipurpose Cleaner with Grapefruit 7 Floor Cleaner 8 Disinfectant with Lemongrass 9 Furniture Polish 10 Stainless Steel Polish 11 Window Cleaner 12 4.4 Status of each Product in each State The following graphs show the status of each product in each of the states: Sydney Product Name Ranking Status Laundry Liquid 1 Consistent Laundry Powder 2 Consistent Pre-Wash Soaker 3 Consistent Fabric Softener 4 Improving Machine Dishwashing Powder with Citrus 5 Improving Multipurpose Cleaner with Grapefruit 6 Worsening Disinfectant with Lemongrass 7 Improving Dishwashing Liquid with Chamomile 8 Improving Floor Cleaner 9 Worsening Stainless Steel Polish 10 Consistent Furniture Polish 11 Improving Window Cleaner 12 Worsening Brisbane Product Name Ranking Status Laundry Liquid 1 Consistent Laundry Powder 2 Improving Pre-Wash Soaker 3 Improving Fabric Softener 4 Consistent Machine Dishwashing Powder with Citrus 5 Worsening Multipurpose Cleaner with Grapefruit 6 Worsening Disinfectant with Lemongrass 7 Improving Floor Cleaner 8 Worsening Dishwashing Liquid with Chamomile 9 Improving Stainless Steel Polish 10 Worsening Furniture Polish 11 Improving Window Cleaner 12 Consistent Melbourne Product Name Ranking Status Laundry Liquid 1 Consistent Dishwashing Liquid with Chamomile 2 Worsening Pre-Wash Soaker 3 Worsening Laundry Powder 4 Improving Fabric Softener 5 Improving Disinfectant with Lemongrass 6 Worsening Machine Dishwashing Powder with Citrus 7 Worsening Floor Cleaner 8 Worsening Multipurpose Cleaner with Grapefruit 9 Worsening Furniture Polish 10 Improving Stainless Steel Polish 11 Improving Window Cleaner 12 Worsening 4.5 Analysis of performance across the company Laundry liquid is consistent in all the three state, and so far the company’s strongest product. Window Cleaner is worsening in Sydney and Melbourne but consistent in Brisbane. Sydney has the highest number of consisted and improving products; Brisbane has the highest number of improving products. Brisbane has a consistent and fast growing economy that includes establishment of hotels and other facilities of the hospitality industry hence with proper marketing EcoClean can find more channels to sell its products (Warren, 2012). Melbourne has the highest number of worsening products. Nevertheless, the sales for all the states reduced in 2012. Woodings (2012) predicts a 4.4 percent growth of computer and software over the next five years which may lead a slow growth of department stores by one percent per annum over the next five years. 5. Recommendations Change or enhance sales staff training strategies for Melbourne, the least performing state. Increase staff motivation through incentives, team building, and similar strategies to help low performers improve, and high performers to be consistent. Retain or improve strategy for marketing Laundry Liquid, and for all improving products in relevant states. Conduct market research by contacting consumers about their perspectives on products such as Window Cleaner which had the least sales overall. Invest more on new marketing strategies, especially online marketing which has shown to generate the highest sales in the three year with very little investment on marketing costs. 6. Conclusion EcoClean Company is experiencing a challenging business time where trends show a decline in sales performance in its retail outlets in Sydney, Melbourne, and Brisbane. The findings have shown that the performance depends on the sales managers and the new ways of selling. The company should apply the recommendations given in the report to ensure that sales of all products are consistent or improved. This can be done through targeting the online selling which has shown to use fewer marketing costs but bring higher gross sales. Reflection on Data Issues I have learnt that when creating such a report that requires analysis of a large amount of data, managing of data can become a challenge. I realized it was possible to make repeated or erroneous entries when entering the raw data for analysis. Attention to detail is therefore required when doing data analysis and this process taught me the importance of being very attentive. Inaccurate data would have led to invalid results and that would have affected the overall reliability of the report for EcoClean Company. Nevertheless, I realized how the use of Access and Excel also makes the analysis of data much easier than when one is staring at the huge amount of raw data. I was so encouraged that after a lot of hard work making raw data entries, all I needed to was to put in the right queries in Access or formula command in Excel, and was able to generate data that made sense. This helped my analysis as trends became apparent. It was a very interesting procedure, in that my skills for using both Access and Excel have improved as well as the know-how of how raw data can be displayed in a sensible way with the use of the applications. References Davis, K., Lane, K., and Orsmond, D. (2012). “The recent economic performance”, Reserve Bank of Australia, p.1-8 Victorian Government Department of Infrastructure. (2000). Globalization, competitiveness and metropolitan strategy. Available at www.dpcd.vic.gov.au/__data/assets/pdf_file/.../tech03_globalisation.pdf Warren, B. (2012). “Brisbane hotels primed to meet event visitor growth” Web. Retrieved April 24, 2013 from http://www.ttgmice.com/article/brisbane-hotels-primed-to-meet- event-visitor-growth/ Woodings, T. (2012). Australian retain industry outlook. Forensic Accounting, p.1-8 Appendices Appendix 1: Contribution towards sales by each sales person Appendix 2: Product Category and description Product Info Product ID Product Name Category Cost Retail CLN001 Stainless Steel Polish Cleaning $6.00 $15.95 CLN002 Multipurpose Cleaner with Grapefruit Cleaning $2.50 $19.95 CLN003 Floor Cleaner Cleaning $3.50 $19.95 CLN004 Window Cleaner Cleaning $5.00 $10.95 CLN005 Furniture Polish Cleaning $8.00 $15.95 KIT001 Dishwashing Liquid with Chamomile Kitchen $6.00 $19.95 KIT002 Machine Dishwashing Powder with Citrus Kitchen $6.50 $18.95 KIT003 Disinfectant with Lemongrass Kitchen $6.00 $18.95 LAU001 Laundry Liquid Laundry $10.50 $31.95 LAU002 Laundry Powder Laundry $14.00 $32.95 LAU003 Fabric Softener Laundry $6.30 $23.95 LAU004 Pre-Wash Soaker Laundry $5.50 $24.95 Appendix 3: Product ID and retail cost Product ID Product Name Category Cost Retail CLN001 Stainless Steel Polish Cleaning $ 6.00 $15.95 CLN002 Multipurpose Cleaner with Grapefruit Cleaning $ 2.50 $19.95 CLN003 Floor Cleaner Cleaning $ 3.50 $19.95 CLN004 Window Cleaner Cleaning $ 5.00 $10.95 CLN005 Furniture Polish Cleaning $ 8.00 $15.95 KIT001 Dishwashing Liquid with Chamomile Kitchen $ 6.00 $19.95 KIT002 Machine Dishwashing Powder with Citrus Kitchen $ 6.50 $18.95 KIT003 Disinfectant with Lemongrass Kitchen $ 6.00 $18.95 LAU001 Laundry Liquid Laundry $10.50 $31.95 LAU002 Laundry Powder Laundry $14.00 $32.95 LAU003 Fabric Softener Laundry $ 6.30 $23.95 LAU004 Pre-Wash Soaker Laundry $ 5.50 $24.95 Appendix 4: Trends in business environment Source: Davis, Lane and Orsmond, (2012). Reserve Bank of Australia, p.3 Appendix 5: consumer price inflation 2011 Read More