Some new work of mine!
Operational Research Report: Contract Sales & SIC Relationship
Brett Powers, Matthew Ziff, Dan Reynolds
Contents
Anago: The Company 3
Company Profile 3
Analysis: The current state of Anago 4
Work Objectives 4
Explanation of Basic Analysis: 4
Process Analysis: 5
The Contract Information Process 5
Regional Analysis: 6
Sales Percentages by Region: 6
National SIC Code Performance: 7
City Performance by Region: 7
Reliability of City’s Performance: 11
Z Value Analysis 13
Proportions: 14
Moving Forward: Forecasting and Error Analysis 15
Regression analysis: 15
Error, Sources and Analysis: 18
Conclusion: 18
Index: 19
Anago: The Company
Company Profile
Anago Cleaning Systems was founded in 1989 and began franchising in 1991. The name was derived from the Greek-Latin term “anagogue”, meaning to guide or uplift. The company was founded by David Povlitz, who established Anago after owning a conventional cleaning company in Detroit, Michigan.
The company offers Master Franchise Owners the exclusive developmental rights to sell janitorial or Unit Franchises in a defined territory. Simultaneously, the Master sells cleaning contracts, business to business, within the territory and assigns them to those who purchased a Unit Franchise.
Anago Master Offices service all types of commercial properties, such as multi-tenant buildings, schools, banks, insurance companies, car dealerships, and medical facilities.
Since our establishment in 1989, Anago Cleaning Systems has experienced significant growth in the franchise industry, predominantly in the past 10 years. With 73 total Unit Franchises by the late 1990’s, Anago currently has over 30 Master Franchises in the U.S. and over 2100 Unit Franchises. The company recently started international expansion by opening a Master Office in Santiago, Chile.
Entrepreneur Magazine has consistently ranked Anago as one of the fastest growing franchises in the U.S. Inc. Magazine has ranked Anago as one of the top privately held companies in the U.S. Franchise Business Review recognized Anago as one of the top franchises in Franchisee Satisfaction.
Analysis: The current state of Anago
Work Objectives
Anago unit franchisees are used by a wide variety of industries throughout America as their cleaning service of choice. We conducted operations research in the following areas:
Which SIC (Standard Industrial Classification) codes is the most profitable per regional office.
Which SIC codes sell the most per regional office.
Average sale price of contracts in each SIC (Standard Industrial Classification) code and Regional office.
We compared these different industries & Anago regional offices to see where Anago may be more profitable and how they can make improvements in the way they handle Master Franchise Owners.
We will be working with data directly from Anago’s Information System; Telemagic. We will be working with approximately Five years’ worth of data from each region office when doing our analysis. The data will be provided to us in a “.txt” format, which we will then convert into excel files, and subsequently into Minitab® for advance statistical analysis.
Explanation of Basic Analysis:
After the data was collected, the mean and standard deviation were calculated for both sales and losses. The mean and standard deviation for Average Delta Time and Average Amount of Monthly Billing were calculated by city and by SIC code. The mean shows the average amount each city makes and the average amount each SIC code makes, and the standard deviation shows how much that value ranges. For the SIC code mean and standard deviation, only SIC codes that sold over 20 count were considered, as others would not give significant results. The tables for this data are listed in Index 1A-C.
Anago can use this data to see what cities and SIC codes have the highest average monthly billing and compare that to the delta time. Using these values, Anago can see if the cities and SIC code that make the most money take the longest, and possibly try to focus SIC codes to the more efficient money makers. Also using this data, Anago can see how the standard deviation relates to how much money a city or SIC code brings in. If one particular SIC code makes a lot of money but has a very high deviation, it might not be as efficient as one with a lower deviation that makes slightly less.
Process Analysis:
The Contract Information Process
As Anago Franchising sells Master franchises they are granted access to their Citrix based software: Telemagic. This software is a MS-DOS based client database that stores various amounts of information about clients and potential clients.
Information is created when Anago Franchising buys telemarketing information from a lead company. Lead companies sell other companies information such as phone numbers; Standard Industry Code (SIC), names and regions. Anago can buy information in any combination; the company typically buys by region and SICS code. This information is then uploaded into Telemagic.
Telemarketing Departments now have more company information to continue to provide their services. During telemarking operations Telemarkerts will make appointments with prospective clients, update company information. The Master franchisee will send out of their sales representative to the prospective client
When the sales representative sells or gives a bid he takes this information back to the Master Franchisee sales office. The Master Franchisee sales office will update Telemagic throughout the selling, Biding, Customer service and customer loss process. This information is then used by Anago Franchising to create policy and analyze operations.
Regional Analysis:
The first angle that Anago wanted to look at was the geographical layout of their sales. Anago currently has their sales data broken down by city, however there was also interest in a slightly larger resolution look at their sales data. The next logical categorical breakdown of data for Anago was to break down their sales by region. All cities were placed in a geographic region going off of a standard Health and Human Services Map, (See Index 2) where regions eight, seven and five were combined into a region called “North Central.” Using pivot tables and charts in excel, it was then easy to provide information at a variety of different levels about Anago’s sales.
Sales Percentages by Region:
Regional Breakdown
Region Sum of AVG. Billed Percent Sales
North Central $ 78,762.25 21.89%
North East $ 49,126.88 13.65%
Pacific South West $ 41,557.76 11.55%
South Central $ 24,708.20 6.87%
South East $ 165,664.99 46.04%
Grand Total $ 359,820.09
From the analysis of each sales event, the data has shown that the South East region where Anago has formed is still its top selling region. Also the data shows that the South Central region is under developed.
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
North Central 108 $78,762.25
North East 73 $49,126.88
Pacific NW
Pacific South West 61 $41,557.76
South Central 33 $24,708.20
South East 189 $165,664.99
Grand Total 464 $359,820.09
From looking from a straight quantity of sales perspective, it shows a similar pattern across the nation. Those contract sales dollar values are highly similar across the country.
Box Plot Analysis:
From a national stand point we can use the following Box plots to see how each city does in comparison to Anago national.
Cities that will have monthly billing over $770.61 will be in the top 25% in Anago contract sales $. Any city below $609.12 will be in the lower 25%
Cities that will have total contracts sold greater than 107 will be in the top 25% in Anago contract sales quantities. Any city below 24 will be in the lower 25%
This dot plot shows where the cities fall in total contracts sold. From the data we can see that the most cities fall between 75 and 40. We can clearly see from the plot the few outliers and the three tiers in sales skill the <40, 40 - 75 and 100+.
National SIC Code Performance:
Across all regions there are a handful of Standard Industry Codes (SIC) that have sold the most over the past 5 years. 17 - Construction - Special Trade Contractors, 87 - Engineering & Accounting Mgmt. Services, 15 - Bldg. Construction - General Contractors, 83 -Social Services, 73 - Business Services, 50 - Wholesale Trade - Durable Goods, 80 - Health Services. The most profitable sectors are code 15 averaging $1,263 and code 83 averaging $690. (See next page for table)
SIC Code QTY Avg. Monthly Billing Amt.
17 72.00 322.05
87 86.00 422.67
15 104.00 1263.70
83 106.00 689.96
73 107.00 363.73
50 166.00 418.89
80 340.00 655.39
City Performance by Region:
The second analysis performed for Anago was the performance by city within each region. This allows Anago to see what cities in a given region are performing comparatively well, and which are possibly struggling by comparison to others in their geographic area. This is a more useful view for Anago to take then looking only at cities against the national data. It allows higher resolution look at their franchises, and make more pertinent assessments of their progress. For space reasons, only one of the regions is shown below, however the rest of the regions charts are available in Index 3.
North Central Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
North Central 108 $78,762.25
Cinci 34 $34,217.53
Cleav 30 $16,825.15
Columbus 18 $13,995.40
Detroit 8 $2,072.00
East Ohio 18 $11,652.17
Grand Total 108 $78,762.25
Row Labels Count of SIC code Average of Amt of Monthly Billing
80 70 $726.57
50 47 $527.96
87 39 $475.51
83 31 $1,032.74
73 29 $422.41
35 22 $409.98
17 21 $401.13
In this region the typical SIC 80 is the highest seller however SIC 83 holds a much higher billing value.
North East Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
North East 73 $49,126.88
Baltimore 8 $5,739.67
DC 7 $4,595.33
MontC (Montclair, NJ) 4 $3,796.00
NJ 10 $6,416.02
PHILLY 17 $13,260.61
WAP (Western PA) 27 $15,319.25
Grand Total 73 $49,126.88
Row Labels Count of SIC code Average of Amt of Monthly Billing
80 85 $683.60
50 51 $555.51
51 19 $429.21
83 16 $689.69
87 14 $373.36
73 14 $586.00
This region unlike the others has only two major SIC sellers 85 and 51.
Pacific South West Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
Pacific South West 61 $41,557.76
Bay Area 10 $4,347.28
Hawaii 17 $19,153.86
Sacramento 34 $18,056.62
Grand Total 61 $41,557.76
Row Labels Count of Amt of Monthly Billing Average of Amt of Monthly Billing2
15 82 $1,285.38
80 70 $790.13
73 33 $355.15
89 27 $412.96
50 24 $397.92
17 23 $348.91
59 21 $419.19
Unlike the other regions SIC 15 is the highest seller and billings.
South Central Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
South Central 33 $24,708.20
Dallas 11 $3,816.67
Tulsa 22 $20,891.54
Grand Total 33 $24,708.20
Row Labels Count of SIC code Average of Amt of Monthly Billing
50 17 $287.75
83 9 $589.33
80 9 $972.11
73 7 $378.29
17 7 $309.29
34 6 $439.00
87 6 $289.67
This region as atypical best-selling SICs this suggests it’s under developed.
South East Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
South East 189 $165,664.99
Atlanta 12 $11,057.00
Cent NC 17 $8,681.11
Charleston 2 $1,111.00
Charlotte 16 $9,378.40
Daytona 17 $12,755.27
Hampton Roads VA 13 $12,630.43
JAX 30 $19,943.09
ORL 12 $30,052.47
RAL 23 $27,987.95
Tampa 32 $25,545.74
Tract (treasure coast) 15 $6,522.53
Grand Total 189 $165,664.99
Row Labels Count of SIC code Average of Amt of Monthly Billing
80 159 $585.11
50 63 $396.59
87 45 $466.55
73 38 $347.35
83 29 $749.42
86 28 $1,175.88
17 19 $345.18
This region is the oldest and most developed. This region alone provided 46% of the total contract sales.
Reliability of City’s Performance:
With the city’s data we preformed 95% confidence intervals for Average Billing, Monthly Loss, Retention time before loss and speed at which sales are made. Each cities confidence interval shows how much variability is in the data and how good of a predictor the given data is. The following cities are rated as the most reliable in their respective categories. The full list can be found in the Index 4.
Reliability of Monthly Billing
City 95% CI Interval Size
Detroit 179 313.5 134.5
Tract treasure coast 238.4 429.3 190.9
Sacramento 574 784.9 210.9
WAP Western PA 506.1 728.9 222.8
Atlanta 368.9 604.5 235.6
Dallas 212.9 468.2 255.3
Philadelphia 569 881.3 312.3
Reliability of Monthly Loss
City 95% CI Interval Size
Ral 326.7 478.2 151.5
Jax 407.1 576.8 169.7
Sacramento 368.6 549.1 180.5
Detroit 314.1 534.2 220.1
WAP Western PA 407.4 633 225.6
Bay Area 285 515.3 230.3
Central NC 405.6 659 253.4
Reliability of Retention time before Loss (Yrs)
City 95% CI Interval Size
Tulsa 1.031 1.455 0.424
Ral 1.448 1.939 0.491
Jax 1.883 2.394 0.511
DC 0.929 1.446 0.517
WAP Western PA 1.463 2.022 0.559
Philadelphia 1.245 1.806 0.561
Charlotte 0.891 1.48 0.589
Reliability of Speed at which sales are made (Days)
City 95% CI Interval Size
Dallas 6.13 23.74 17.61
WAP Western PA 24.37 42.59 18.22
Sacramento 30.1 49.95 19.85
Bay Area 29.07 51.67 22.6
Atlanta 33.81 61.31 27.5
ORL 18.98 53.22 34.24
Tract treasure coast 21.97 59.33 37.36
Z Value Analysis
According to the franchise charter between Anago and its franchises, each office has to meet a required monthly billing average based on how long they have been open. These are broken down into:
Operational Time Target Sales
0-24 months $1500
25-36 months $2500
37-48 months $4000
49-61 months $5000
Using these requirements, confidence intervals based on the Z value were constructed. Six cities were selected by Anago to be tested. These cities varied in operational time. Once the cities were chosen, their mean monthly billing and standard deviation of monthly billing were calculated. Once the data was processed, the cities were tested based on 20%, 35%, and 50% of the target sales corresponding to their time of operation. These values were chosen because between 20% and 50% of the data for total monthly billing for each of these cities was missing.
City Time of Operation Mean of Billing Standard Deviation of Billing Target Sales 20% of Target Sales P 35% of Target Sales P 50% of Target Sales P
Charleston 9 Months 555.50 390 1500 0.99 0.936 0.76
Charlotte 4 Months 586.15 786 1500 0.999 0.976 0.798
Jax 15 Months 664.77 712 1500 1 0.991 0.744
Atl 36 Months 384.00 171.6 2500 1 1 1
East Ohio 22 Months 647.34 649 1500 1 1 0.984
Detroit 30 Months 259.00 107.6 2500 0.749 1 1
Using the data found in these tests, confidence intervals were performed at 95% confidence.
City N (x-z*σ)/√n (x+z*σ)/√n
Charleston 5 213.65 897.35
Charlotte 31 309.46 862.84
Jax 105 528.58 800.96
Atl 77 345.67 422.33
East Ohio 31 418.88 875.81
Detroit 12 198.12 319.88
The confidence intervals show us what range in monthly billing we can expect from these cities with 95% confidence.
Using these intervals and the other data above, an error test was performed for each city.
City N E
Charleston 647,431.98 341.85
Charlotte 2,629,723.12 276.69
Jax 2,157,868.22 136.19
Atl 125,342.83 38.33
East Ohio 1,792,892.81 228.47
Detroit 49,282.13 60.88
According to the test, the N value used, the amount of samples was not high enough for the tests to be significant. When the E value was found, it showed that many of the data points would contribute to the error.
Anago can use the data found in the Z analysis to see if, according to the table, these franchises will meet their respective requirements. This data also shows that for a more accurate result, a significantly higher number of samples are required.
Proportions:
The data from Anago that was available was focused on sales by city and SIC code. However, another worthwhile question were trying to answer was which industry provides the highest amount of Anago’s business. To figure this out, the data was sorted the national data by SIC code, and found the one with the highest average contract average value across all of Anago’s Operations. The SIC code that filtered out as the highest grossing was Code 80, which is the health services Industry. From some simple analysis, it was found that SIC code 80 accounts for roughly 25% of Anago’s income. Confidence intervals for a proportion were used to test accuracy, which found that the original estimate of 23% was indeed a relatively accurate statistic, and that in actuality the data fell between 18-23% for a 95% confidence interval. The table below outlines that test.
Top Selling SIC Analysis:
Code: Sales % Sales CI: P-Value Error Test (N)
80 340 23.26% 0.188862, 0.228859 0 245.8624
Notes:
(since Sales>N, we can have a high degree of confidence)
Moving Forward: Forecasting and Error Analysis
Regression analysis:
Up until this point, the analysis of Anago’s sales data has focused on helping them get a better handle on their existing information. However, there are also opportunities to use statistical analysis to help provide models for the future of Anago and its franchises. One important example of modeling is regression analysis. Because, as previously discussed, Anago’s data is spread across a wide variety of SIC codes and regions, regression analysis can actually be performed to a very high resolution level to help predict each SIC codes future performance, and therefore help Anago focus its marketing efforts. The regression analysis of Anago’s Mean Time of contract closure and Average contract sales by SIC code is outlined in the table below. For comparison’s sake, the current numbers according to the model can be contrasted with the actual data in the table on the following page.
Regression Results:
SIC Code Regression Equation Mean Delta Time Ῡ
15 Avg Contract Sale = 1068 + 5.82 38.38 $1,291.34
17 Avg Contract Sale_6 = 316 + 0.176 50.93 $324.96
27 Avg Contract Sale_10 = 619 - 0.491 141.95 $549.30
34 Avg Contract Sale_12 = 1041 - 1.85 64.33 $921.98
35 Avg Contract Sale_16 = 506 - 0.416 79.09 $473.10
50 Avg Contract Sale_2 = 424 - 0.0955 99.49 $414.50
51 Avg Contract Sale_9 = 400 + 0.201 78.57 $415.79
55 Avg Contract Sale_18 = 349 + 6.16 68.73 $772.40
59 Avg Contract Sale_14 = 364 - 0.73 21.08 $348.61
60 Avg Contract Sale_17 = 572 + 0.071 97.03 $578.89
65 Avg Contract Sale_8 = 658 - 1.47 48.17 $587.20
73 Avg Contract Sale_3 = 364 + 0.0319 78.79 $366.51
80 Avg Contract Sale_1 = 623 + 0.801 46.87 $660.54
81 Avg Contract Sale_13 = 498 - 0.014 140.38 $496.03
82 Avg Contract Sale_15 = 1079 + 16.9 40.31 $1,760.31
83 Avg Contract Sale_4 = 712 - 0.152 51.92 $704.11
86 Avg Contract Sale_7 = 1190 - 0.05 87.51 $1,185.62
87 Avg Contract Sale_5 = 349 + 0.462 72.02 $382.27
89 Avg Contract Sale_11 = 475 - 2.00 30.81 $413.37
National Contract Sales (y) $12,646.85
Actual Data:
SIC Code Mean Delta Time Y Actual
15 38.38 $ 1,263.70
17 50.93 $ 322.05
27 141.95 $ 563.78
34 64.33 $ 922.54
35 79.09 $ 473.11
50 99.49 $ 418.89
51 78.57 $ 409.54
55 68.73 $ 773.00
59 21.08 $ 393.26
60 97.03 $ 558.50
65 48.17 $ 669.57
73 78.79 $ 363.73
80 46.87 $ 655.39
81 140.38 $ 496.21
82 40.31 $ 1,751.06
83 51.92 $ 689.96
86 87.51 $ 1,167.07
87 72.02 $ 422.67
89 30.81 $ 412.96
National Contract Sales (Y) Actual $ 12,726.98
These two tables are very close in their outputs, leading the viewer to believe that the regression models must be reasonably accurate, but to make sure that they were statistically valid, other tests were also used. The first of these was checking each regression using the ANOVA method. ANOVA provides a systematic way to address variance in a regression model by breaking it down into components. The ANOVA method generates a table like the one shown below, and while one was created and considered for each SIC Code, for the purposes of this report, the most significant SIC code is Code 80, and therefore the ANOVA table for SIC 80 is presented:
Code 80 ANOVA table:
Source DF SS MS F-Comp'd P-Val
Regression 1 11892389 11892389 21.1 0.00
Residual Error 28 15779145 563541
Total 29 27671534
This Table holds a lot of information that can be used for in depth statistical analysis. The most important piece of information that can be taken from an ANOVA table is the P-Value. That becomes a strong indicator of the strength of the relationship.
R-Squared:
The second piece important test that can be used to assess the validity of a regression equation is to find its R^2 value. To the right is a table listing the R^2 values by SIC code for each regression line.
Quality of Fit
SIC Code Quality of Fit
15 R-Sq = 5.3%
17 R-Sq = 1.9%
27 R-Sq = 0.9%
34 R-Sq = 0.1%
35 R-Sq = 0.0%
50 R-Sq = 1.4%
51 R-Sq = 0.2%
55 R-Sq = 0.0%
59 R-Sq = 1.5%
60 R-Sq = 2.3%
65 R-Sq = 2.8%
73 R-Sq = 3.8%
80 R-Sq = 1.0%
81 R-Sq = 0.0%
82 R-Sq = 1.2%
83 R-Sq = 53.1%
86 R-Sq = 1.4%
87 R-Sq = 0.1%
89 R-Sq = 43.0%
A higher R^2 Value indicates a high level of correlation between the data and the regression model. For the case of the R^2analysis, SIC Code 89 is an excellent example because it has an R^2=43.0%. This indicates that the data supports the hypothesis that is effectively made when fitting a linear regression line to data to help extrapolate a trend. Looking back up at the comparison between the data and the regression outputs, it shows only one dollar difference out of 413 dollars for the Average Sales. That’s less than a 10% difference.
These Regression equations could, if used correctly by Anago, be a very powerful tool for making decisions about how what industries to push advertising in in the future. Additionally, the generally low P-values from the ANOVA tables, and the fact that many of these SIC regressions have strong R^2Values mean that, assuming current market factors remain similar-not always a safe assumption-that they can use these models with a fairly high level of confidence.
Error, Sources and Analysis:
While processing the data, a number of procedural errors arose, both in the analysis and in the gathering of the data itself that caused both difficulty in certain analysis, and larger then desired uncertainty in a few measurements. These errors were considered and accounted for in the final product, but they occasionally caused some trouble both with analysis and with the validity of results. The main cause of the error is based in Anago’s data collection system itself.
There are a few reasons that Anago’s collection system, Telemagic, is the main problem that caused error in the calculations. The system is DOS based, which means it is old and not very easy to work with, this causes some irregularities in its usage. Anago also does not have a standardized system for each franchise to report each sale or to store their data. This makes some inconsistencies in the data and makes it difficult to sort through to find what is needed for the tests.
The other reason that Telemagic is the main problem is that not all of the franchises use the Telemagic system in their offices. These cities use various other systems to record their data, but these don’t always mesh well with the Telemagic system. Some cities don’t record their data at all, which renders them unusable for testing. Because of these inconsistencies and problems with data collection and the Telemagic system, 20-50% of the monthly billing data is missing. This may seem like a substantial amount, but it was well accounted for in testing, for example, the Z tests take into account a 20%, 35%, and 50% reduction in the target sales goal to account for missing data.
Conclusion:
The data resulting from the tests concluded in this assessment can help Anago in many ways, as explained in each section. There are many sources of error in the data collection and records process which needs to be dealt with before Anago can hope to continue optimizing their operations. Hopefully Anago will take these results into consideration in their future endeavors and continue to be one of the fastest growing franchises in the United States.
Index:
INDEX 1A: Mean & STD by City Contract Sales
City Mean Average Delta Time Std Dev Average Delta Time Mean Average Amt Monthly Billing Std Dev Average Amt of Monthly Billing
Atlanta 47.56 60.58 486.7 519
Baltimore 46.6 51.3 769 612
Bay Area 40.37 46.69 635 711
Central NC 145.1 285.3 635 624
Charleston 111.1 49.3 556 390
Charlotte 38.2 67.1 496 603
Cincinnati 1815 8159 775 1217.4
Cleveland 127.7 370.1 604.3 985.9
Columbus 125.9 247.1 610.7 771.6
Dallas 14.94 16.53 340.6 239.6
Daytona 86.6 169.8 873 993
DC 40.4 44.7 600 556
Detroit 91.3 210.8 246.3 105.9
East Ohio 68.1 101.7 738 1169
Hampton Roads VA 157.9 456.8 820 1450
Hawaii 154.6 434.2 1021 1403
Jax 43.2 107.4 661 1283
MontC (Montclair, NJ) 82.3 63.9 949 858
NNJ 26.7 64.8 660 737
ORL 36.1 54.24 733 1501
Philadelphia 26.8 155.6 725.5 828.5
Ral 166.7 796.5 675 2178
Sacramento 40.06 94.89 679.5 1026
Tampa 28.7 185 687.1 1061
Tract (treasure coast) 40.65 62.91 333.8 321.4
Tulsa 91.9 122.8 803 1465
WAP (Western PA) 33.48 52.28 617.5 639.6
INDEX 1B: Mean & STD By City Contract Losses
City Mean Average of Delta (Yrs) Std Dev Average of Delta (Yrs) Average of Monthly Billing Lost Std Average of Monthly Billing Lost
Atlanta 2.154 1.746 530.7 489.2
Baltimore 1.295 0.633 808 700
Bay Area 1.794 1.226 400.2 400.8
Central NC 2.211 1.596 532.3 531.3
Charleston 1.324 0.963 447.1 445.2
Charlotte 1.186 0.73 560 1063
Cincinnati 2.306 10.199 816.9 991.8
Cleveland 2.727 1.938 778 1637
Columbus 2.86 1.756 578 671
Dallas 1.616 1.374 504 827.1
Daytona 1.206 0.839 897 862
DC 1.188 1.161 785 987
Detroit 2.336 2.038 424.2 357.7
East Ohio 1.845 1.919 877 1467
Hampton Roads VA 1.828 1.294 741 1323
Hawaii 2.135 1.539 607 711.2
Jax 2.138 1.525 492 506
MontC (Montclair, NJ) None None None None
NNJ 1.185 0.853 540.8 321
ORL 3.117 2.062 919 1844
Philadelphia 1.526 1.476 733.8 1008
Ral 1.694 1.081 402.5 333.7
Sacramento 1.959 1.82 458.8 536.1
Tampa 3.182 2.459 671.7 807.9
Tract (treasure coast) 1.299 1.074 420 584
Tulsa 1.243 1.201 699 1543
WAP (Western PA) 1.742 1.586 520.2 647.6
INDEX 1C: Mean & STD By SIC Codes
Sic Code Mean Delta Time Mean Amt Month Billing Std. Dev Delta Time Std Dev Amt Month Billing
15 38.38 1264 70.35 1756
17 50.9 322 90.8 357.9
27 137.1 552 188.4 556
34 64.3 923 99.2 1805
35 79.1 473 168.2 597
50 99.5 419.4 330.3 345.2
51 76.6 415.8 215.8 288.5
55 73.5 811 106.1 1001
59 21.08 393.3 45.37 376.4
60 97 558.5 169.8 472.1
65 48.2 670 69.9 1022
73 78.1 365.9 322.7 299
80 46.63 658.2 129.15 740.4
81 140.4 496 350.9 517
82 40.3 1751 94.8 2167
83 52.99 703.8 98.1 812.1
86 87.5 1167 151.2 1490
87 72 422.7 136.1 608
89 30.81 413 40.51 416.9
INDEX 2: Regions Map
Source: http://www.hhs.gov/about/images/hhs-region-map-updated011012.jpg
North Central South Central
Cenci Dallas
Cleave East Ohio
Columbus Tulsa
Detroit South East
East Ohio Atlanta
North East Cent NC
Baltimore Charleston
DC Charlotte
MontC (Montclair, NJ) Daytona
NJ Hampton Roads VA
PHILLY JAX
WAP (Western PA) ORL
Pacific South West RAL
Bay Area Tampa
Hawaii Tract (treasure coast)
Sacramento
INDEX 3A: Pacific Northwest
INDEX 3B: Northeast
INDEX 3C: South Central
INDEX 3D: Southeast:
INDEX 3E: Pacific Southwest:
INDEX 4: Reliability Data:
Monthly Billing
City 95% CI Interval Size
Detroit 179 313.5 134.5
Tract treasure coast 238.4 429.3 190.9
Sacramento 574 784.9 210.9
WAP Western PA 506.1 728.9 222.8
Atlanta 368.9 604.5 235.6
Dallas 212.9 468.2 255.3
Phillidelphia 569 881.3 312.3
Cincinatti 608 941.8 333.8
Bay Area 463.7 807.9 344.2
Cleavland 422.9 785.6 362.7
Columbus 414.8 806.7 391.9
Tampa 489 884.9 395.9
Central NC 414 856 442
Charlotte 275 718 443
Jax 413 910 497
Daytona 610 1136 526
NNJ 368 951 583
Tulsa 477 1129 652
DC 202 997 795
Hampton Roads VA 408 1233 825
East Ohio 310 1167 857
Baltamore 331 1207 876
ORL 259 1207 948
Ral 200 1151 951
Hawaii 487 1554 1067
Charleston 164 1277 1113
MontC Montclair NJ -417 2315 2732
Time Loss
City 95% CI Interval Size
Tulsa 1.031 1.455 0.424
Ral 1.448 1.939 0.491
Jax 1.883 2.394 0.511
DC 0.929 1.446 0.517
WAP Western PA 1.463 2.022 0.559
Phillidelphia 1.245 1.806 0.561
Charlotte 0.891 1.48 0.589
Sacramento 1.65 2.265 0.615
Dallas 1.291 1.941 0.65
Charleston 0.97 1.677 0.707
Bay Area 1.438 2.15 0.712
Hampton Roads VA 1.467 2.188 0.721
Hawaii 1.765 2.505 0.74
Central NC 1.83 2.591 0.761
Daytona 0.802 1.611 0.809
Tract treasure coast 0.883 1.715 0.832
Tampa 2.76 3.597 0.837
Cleavland 2.278 3.176 0.898
NNJ 0.612 1.758 1.146
Baltamore 0.71 1.88 1.17
Atlanta 1.564 2.745 1.181
Columbus 2.257 3.463 1.206
Detroit 1.709 2.963 1.254
ORL 2.419 3.814 1.395
East Ohio 0.89 2.799 1.909
Cincinatti 0.4 4.207 3.807
Fastest to Sell
City 95% CI Interval Size
Dallas 6.13 23.74 17.61
WAP Western PA 24.37 42.59 18.22
Sacramento 30.1 49.95 19.85
Bay Area 29.07 51.67 22.6
Atlanta 33.81 61.31 27.5
ORL 18.98 53.22 34.24
Tract treasure coast 21.97 59.33 37.36
Jax 22.5 64 41.5
Charlotte 13.1 63.2 50.1
NNJ 1.1 52.3 51.2
Tulsa 64.6 119.2 54.6
Phillidelphia -2 56 58
Tampa -0.5 63.2 63.7
DC 8.4 72.4 64
Baltamore 9.9 83.3 73.4
East Ohio 30.8 105.4 74.6
Daytona 40.3 133 92.7
MontC Montclair NJ -19.41 83.9 103.31
Columbus 59 192.7 133.7
Cleavland 58.8 196.7 137.9
Charleston 5.7 174.7 169
Central NC 44 246.3 202.3
Hampton Roads VA 26.7 289.1 262.4
Detroit -42.6 225.3 267.9
Ral -7.2 340.6 347.8
Hawaii -20.8 330 350.8
Cincinatti 69 2933 2864
Monthly Loss
City 95% CI Interval Size
Ral 326.7 478.2 151.5
Jax 407.1 576.8 169.7
Sacramento 368.6 549.1 180.5
Detroit 314.1 534.2 220.1
WAP Western PA 407.4 633 225.6
Bay Area 285 515.3 230.3
Central NC 405.6 659 253.4
Tampa 534.7 808.7 274
Atlanta 367.6 693.8 326.2
Charleston 283.8 610.4 326.6
Hawaii 436.2 777.9 341.7
Phillidelphia 550.8 916.8 366
Cincinatti 631.6 1002.1 370.5
Dallas 309.6 698.3 388.7
NNJ 325.2 756.5 431.3
DC 567 1003 436
Tract treasure coast 193 646 453
Columbus 347 808 461
Tulsa 427 972 545
Hampton Roads VA 373 1110 737
Cleavland 399 1157 758
Daytona 481 1313 832
Charlotte 130 989 859
ORL 296 1543 1247
Baltamore 161 1455 1294
East Ohio 170 1584 1414
Brett Powers, Matthew Ziff, Dan Reynolds
Contents
Anago: The Company 3
Company Profile 3
Analysis: The current state of Anago 4
Work Objectives 4
Explanation of Basic Analysis: 4
Process Analysis: 5
The Contract Information Process 5
Regional Analysis: 6
Sales Percentages by Region: 6
National SIC Code Performance: 7
City Performance by Region: 7
Reliability of City’s Performance: 11
Z Value Analysis 13
Proportions: 14
Moving Forward: Forecasting and Error Analysis 15
Regression analysis: 15
Error, Sources and Analysis: 18
Conclusion: 18
Index: 19
Anago: The Company
Company Profile
Anago Cleaning Systems was founded in 1989 and began franchising in 1991. The name was derived from the Greek-Latin term “anagogue”, meaning to guide or uplift. The company was founded by David Povlitz, who established Anago after owning a conventional cleaning company in Detroit, Michigan.
The company offers Master Franchise Owners the exclusive developmental rights to sell janitorial or Unit Franchises in a defined territory. Simultaneously, the Master sells cleaning contracts, business to business, within the territory and assigns them to those who purchased a Unit Franchise.
Anago Master Offices service all types of commercial properties, such as multi-tenant buildings, schools, banks, insurance companies, car dealerships, and medical facilities.
Since our establishment in 1989, Anago Cleaning Systems has experienced significant growth in the franchise industry, predominantly in the past 10 years. With 73 total Unit Franchises by the late 1990’s, Anago currently has over 30 Master Franchises in the U.S. and over 2100 Unit Franchises. The company recently started international expansion by opening a Master Office in Santiago, Chile.
Entrepreneur Magazine has consistently ranked Anago as one of the fastest growing franchises in the U.S. Inc. Magazine has ranked Anago as one of the top privately held companies in the U.S. Franchise Business Review recognized Anago as one of the top franchises in Franchisee Satisfaction.
Analysis: The current state of Anago
Work Objectives
Anago unit franchisees are used by a wide variety of industries throughout America as their cleaning service of choice. We conducted operations research in the following areas:
Which SIC (Standard Industrial Classification) codes is the most profitable per regional office.
Which SIC codes sell the most per regional office.
Average sale price of contracts in each SIC (Standard Industrial Classification) code and Regional office.
We compared these different industries & Anago regional offices to see where Anago may be more profitable and how they can make improvements in the way they handle Master Franchise Owners.
We will be working with data directly from Anago’s Information System; Telemagic. We will be working with approximately Five years’ worth of data from each region office when doing our analysis. The data will be provided to us in a “.txt” format, which we will then convert into excel files, and subsequently into Minitab® for advance statistical analysis.
Explanation of Basic Analysis:
After the data was collected, the mean and standard deviation were calculated for both sales and losses. The mean and standard deviation for Average Delta Time and Average Amount of Monthly Billing were calculated by city and by SIC code. The mean shows the average amount each city makes and the average amount each SIC code makes, and the standard deviation shows how much that value ranges. For the SIC code mean and standard deviation, only SIC codes that sold over 20 count were considered, as others would not give significant results. The tables for this data are listed in Index 1A-C.
Anago can use this data to see what cities and SIC codes have the highest average monthly billing and compare that to the delta time. Using these values, Anago can see if the cities and SIC code that make the most money take the longest, and possibly try to focus SIC codes to the more efficient money makers. Also using this data, Anago can see how the standard deviation relates to how much money a city or SIC code brings in. If one particular SIC code makes a lot of money but has a very high deviation, it might not be as efficient as one with a lower deviation that makes slightly less.
Process Analysis:
The Contract Information Process
As Anago Franchising sells Master franchises they are granted access to their Citrix based software: Telemagic. This software is a MS-DOS based client database that stores various amounts of information about clients and potential clients.
Information is created when Anago Franchising buys telemarketing information from a lead company. Lead companies sell other companies information such as phone numbers; Standard Industry Code (SIC), names and regions. Anago can buy information in any combination; the company typically buys by region and SICS code. This information is then uploaded into Telemagic.
Telemarketing Departments now have more company information to continue to provide their services. During telemarking operations Telemarkerts will make appointments with prospective clients, update company information. The Master franchisee will send out of their sales representative to the prospective client
When the sales representative sells or gives a bid he takes this information back to the Master Franchisee sales office. The Master Franchisee sales office will update Telemagic throughout the selling, Biding, Customer service and customer loss process. This information is then used by Anago Franchising to create policy and analyze operations.
Regional Analysis:
The first angle that Anago wanted to look at was the geographical layout of their sales. Anago currently has their sales data broken down by city, however there was also interest in a slightly larger resolution look at their sales data. The next logical categorical breakdown of data for Anago was to break down their sales by region. All cities were placed in a geographic region going off of a standard Health and Human Services Map, (See Index 2) where regions eight, seven and five were combined into a region called “North Central.” Using pivot tables and charts in excel, it was then easy to provide information at a variety of different levels about Anago’s sales.
Sales Percentages by Region:
Regional Breakdown
Region Sum of AVG. Billed Percent Sales
North Central $ 78,762.25 21.89%
North East $ 49,126.88 13.65%
Pacific South West $ 41,557.76 11.55%
South Central $ 24,708.20 6.87%
South East $ 165,664.99 46.04%
Grand Total $ 359,820.09
From the analysis of each sales event, the data has shown that the South East region where Anago has formed is still its top selling region. Also the data shows that the South Central region is under developed.
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
North Central 108 $78,762.25
North East 73 $49,126.88
Pacific NW
Pacific South West 61 $41,557.76
South Central 33 $24,708.20
South East 189 $165,664.99
Grand Total 464 $359,820.09
From looking from a straight quantity of sales perspective, it shows a similar pattern across the nation. Those contract sales dollar values are highly similar across the country.
Box Plot Analysis:
From a national stand point we can use the following Box plots to see how each city does in comparison to Anago national.
Cities that will have monthly billing over $770.61 will be in the top 25% in Anago contract sales $. Any city below $609.12 will be in the lower 25%
Cities that will have total contracts sold greater than 107 will be in the top 25% in Anago contract sales quantities. Any city below 24 will be in the lower 25%
This dot plot shows where the cities fall in total contracts sold. From the data we can see that the most cities fall between 75 and 40. We can clearly see from the plot the few outliers and the three tiers in sales skill the <40, 40 - 75 and 100+.
National SIC Code Performance:
Across all regions there are a handful of Standard Industry Codes (SIC) that have sold the most over the past 5 years. 17 - Construction - Special Trade Contractors, 87 - Engineering & Accounting Mgmt. Services, 15 - Bldg. Construction - General Contractors, 83 -Social Services, 73 - Business Services, 50 - Wholesale Trade - Durable Goods, 80 - Health Services. The most profitable sectors are code 15 averaging $1,263 and code 83 averaging $690. (See next page for table)
SIC Code QTY Avg. Monthly Billing Amt.
17 72.00 322.05
87 86.00 422.67
15 104.00 1263.70
83 106.00 689.96
73 107.00 363.73
50 166.00 418.89
80 340.00 655.39
City Performance by Region:
The second analysis performed for Anago was the performance by city within each region. This allows Anago to see what cities in a given region are performing comparatively well, and which are possibly struggling by comparison to others in their geographic area. This is a more useful view for Anago to take then looking only at cities against the national data. It allows higher resolution look at their franchises, and make more pertinent assessments of their progress. For space reasons, only one of the regions is shown below, however the rest of the regions charts are available in Index 3.
North Central Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
North Central 108 $78,762.25
Cinci 34 $34,217.53
Cleav 30 $16,825.15
Columbus 18 $13,995.40
Detroit 8 $2,072.00
East Ohio 18 $11,652.17
Grand Total 108 $78,762.25
Row Labels Count of SIC code Average of Amt of Monthly Billing
80 70 $726.57
50 47 $527.96
87 39 $475.51
83 31 $1,032.74
73 29 $422.41
35 22 $409.98
17 21 $401.13
In this region the typical SIC 80 is the highest seller however SIC 83 holds a much higher billing value.
North East Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
North East 73 $49,126.88
Baltimore 8 $5,739.67
DC 7 $4,595.33
MontC (Montclair, NJ) 4 $3,796.00
NJ 10 $6,416.02
PHILLY 17 $13,260.61
WAP (Western PA) 27 $15,319.25
Grand Total 73 $49,126.88
Row Labels Count of SIC code Average of Amt of Monthly Billing
80 85 $683.60
50 51 $555.51
51 19 $429.21
83 16 $689.69
87 14 $373.36
73 14 $586.00
This region unlike the others has only two major SIC sellers 85 and 51.
Pacific South West Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
Pacific South West 61 $41,557.76
Bay Area 10 $4,347.28
Hawaii 17 $19,153.86
Sacramento 34 $18,056.62
Grand Total 61 $41,557.76
Row Labels Count of Amt of Monthly Billing Average of Amt of Monthly Billing2
15 82 $1,285.38
80 70 $790.13
73 33 $355.15
89 27 $412.96
50 24 $397.92
17 23 $348.91
59 21 $419.19
Unlike the other regions SIC 15 is the highest seller and billings.
South Central Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
South Central 33 $24,708.20
Dallas 11 $3,816.67
Tulsa 22 $20,891.54
Grand Total 33 $24,708.20
Row Labels Count of SIC code Average of Amt of Monthly Billing
50 17 $287.75
83 9 $589.33
80 9 $972.11
73 7 $378.29
17 7 $309.29
34 6 $439.00
87 6 $289.67
This region as atypical best-selling SICs this suggests it’s under developed.
South East Region:
Row Labels Count of SIC Codes Sum of AVG Monthly Billing
South East 189 $165,664.99
Atlanta 12 $11,057.00
Cent NC 17 $8,681.11
Charleston 2 $1,111.00
Charlotte 16 $9,378.40
Daytona 17 $12,755.27
Hampton Roads VA 13 $12,630.43
JAX 30 $19,943.09
ORL 12 $30,052.47
RAL 23 $27,987.95
Tampa 32 $25,545.74
Tract (treasure coast) 15 $6,522.53
Grand Total 189 $165,664.99
Row Labels Count of SIC code Average of Amt of Monthly Billing
80 159 $585.11
50 63 $396.59
87 45 $466.55
73 38 $347.35
83 29 $749.42
86 28 $1,175.88
17 19 $345.18
This region is the oldest and most developed. This region alone provided 46% of the total contract sales.
Reliability of City’s Performance:
With the city’s data we preformed 95% confidence intervals for Average Billing, Monthly Loss, Retention time before loss and speed at which sales are made. Each cities confidence interval shows how much variability is in the data and how good of a predictor the given data is. The following cities are rated as the most reliable in their respective categories. The full list can be found in the Index 4.
Reliability of Monthly Billing
City 95% CI Interval Size
Detroit 179 313.5 134.5
Tract treasure coast 238.4 429.3 190.9
Sacramento 574 784.9 210.9
WAP Western PA 506.1 728.9 222.8
Atlanta 368.9 604.5 235.6
Dallas 212.9 468.2 255.3
Philadelphia 569 881.3 312.3
Reliability of Monthly Loss
City 95% CI Interval Size
Ral 326.7 478.2 151.5
Jax 407.1 576.8 169.7
Sacramento 368.6 549.1 180.5
Detroit 314.1 534.2 220.1
WAP Western PA 407.4 633 225.6
Bay Area 285 515.3 230.3
Central NC 405.6 659 253.4
Reliability of Retention time before Loss (Yrs)
City 95% CI Interval Size
Tulsa 1.031 1.455 0.424
Ral 1.448 1.939 0.491
Jax 1.883 2.394 0.511
DC 0.929 1.446 0.517
WAP Western PA 1.463 2.022 0.559
Philadelphia 1.245 1.806 0.561
Charlotte 0.891 1.48 0.589
Reliability of Speed at which sales are made (Days)
City 95% CI Interval Size
Dallas 6.13 23.74 17.61
WAP Western PA 24.37 42.59 18.22
Sacramento 30.1 49.95 19.85
Bay Area 29.07 51.67 22.6
Atlanta 33.81 61.31 27.5
ORL 18.98 53.22 34.24
Tract treasure coast 21.97 59.33 37.36
Z Value Analysis
According to the franchise charter between Anago and its franchises, each office has to meet a required monthly billing average based on how long they have been open. These are broken down into:
Operational Time Target Sales
0-24 months $1500
25-36 months $2500
37-48 months $4000
49-61 months $5000
Using these requirements, confidence intervals based on the Z value were constructed. Six cities were selected by Anago to be tested. These cities varied in operational time. Once the cities were chosen, their mean monthly billing and standard deviation of monthly billing were calculated. Once the data was processed, the cities were tested based on 20%, 35%, and 50% of the target sales corresponding to their time of operation. These values were chosen because between 20% and 50% of the data for total monthly billing for each of these cities was missing.
City Time of Operation Mean of Billing Standard Deviation of Billing Target Sales 20% of Target Sales P 35% of Target Sales P 50% of Target Sales P
Charleston 9 Months 555.50 390 1500 0.99 0.936 0.76
Charlotte 4 Months 586.15 786 1500 0.999 0.976 0.798
Jax 15 Months 664.77 712 1500 1 0.991 0.744
Atl 36 Months 384.00 171.6 2500 1 1 1
East Ohio 22 Months 647.34 649 1500 1 1 0.984
Detroit 30 Months 259.00 107.6 2500 0.749 1 1
Using the data found in these tests, confidence intervals were performed at 95% confidence.
City N (x-z*σ)/√n (x+z*σ)/√n
Charleston 5 213.65 897.35
Charlotte 31 309.46 862.84
Jax 105 528.58 800.96
Atl 77 345.67 422.33
East Ohio 31 418.88 875.81
Detroit 12 198.12 319.88
The confidence intervals show us what range in monthly billing we can expect from these cities with 95% confidence.
Using these intervals and the other data above, an error test was performed for each city.
City N E
Charleston 647,431.98 341.85
Charlotte 2,629,723.12 276.69
Jax 2,157,868.22 136.19
Atl 125,342.83 38.33
East Ohio 1,792,892.81 228.47
Detroit 49,282.13 60.88
According to the test, the N value used, the amount of samples was not high enough for the tests to be significant. When the E value was found, it showed that many of the data points would contribute to the error.
Anago can use the data found in the Z analysis to see if, according to the table, these franchises will meet their respective requirements. This data also shows that for a more accurate result, a significantly higher number of samples are required.
Proportions:
The data from Anago that was available was focused on sales by city and SIC code. However, another worthwhile question were trying to answer was which industry provides the highest amount of Anago’s business. To figure this out, the data was sorted the national data by SIC code, and found the one with the highest average contract average value across all of Anago’s Operations. The SIC code that filtered out as the highest grossing was Code 80, which is the health services Industry. From some simple analysis, it was found that SIC code 80 accounts for roughly 25% of Anago’s income. Confidence intervals for a proportion were used to test accuracy, which found that the original estimate of 23% was indeed a relatively accurate statistic, and that in actuality the data fell between 18-23% for a 95% confidence interval. The table below outlines that test.
Top Selling SIC Analysis:
Code: Sales % Sales CI: P-Value Error Test (N)
80 340 23.26% 0.188862, 0.228859 0 245.8624
Notes:
(since Sales>N, we can have a high degree of confidence)
Moving Forward: Forecasting and Error Analysis
Regression analysis:
Up until this point, the analysis of Anago’s sales data has focused on helping them get a better handle on their existing information. However, there are also opportunities to use statistical analysis to help provide models for the future of Anago and its franchises. One important example of modeling is regression analysis. Because, as previously discussed, Anago’s data is spread across a wide variety of SIC codes and regions, regression analysis can actually be performed to a very high resolution level to help predict each SIC codes future performance, and therefore help Anago focus its marketing efforts. The regression analysis of Anago’s Mean Time of contract closure and Average contract sales by SIC code is outlined in the table below. For comparison’s sake, the current numbers according to the model can be contrasted with the actual data in the table on the following page.
Regression Results:
SIC Code Regression Equation Mean Delta Time Ῡ
15 Avg Contract Sale = 1068 + 5.82 38.38 $1,291.34
17 Avg Contract Sale_6 = 316 + 0.176 50.93 $324.96
27 Avg Contract Sale_10 = 619 - 0.491 141.95 $549.30
34 Avg Contract Sale_12 = 1041 - 1.85 64.33 $921.98
35 Avg Contract Sale_16 = 506 - 0.416 79.09 $473.10
50 Avg Contract Sale_2 = 424 - 0.0955 99.49 $414.50
51 Avg Contract Sale_9 = 400 + 0.201 78.57 $415.79
55 Avg Contract Sale_18 = 349 + 6.16 68.73 $772.40
59 Avg Contract Sale_14 = 364 - 0.73 21.08 $348.61
60 Avg Contract Sale_17 = 572 + 0.071 97.03 $578.89
65 Avg Contract Sale_8 = 658 - 1.47 48.17 $587.20
73 Avg Contract Sale_3 = 364 + 0.0319 78.79 $366.51
80 Avg Contract Sale_1 = 623 + 0.801 46.87 $660.54
81 Avg Contract Sale_13 = 498 - 0.014 140.38 $496.03
82 Avg Contract Sale_15 = 1079 + 16.9 40.31 $1,760.31
83 Avg Contract Sale_4 = 712 - 0.152 51.92 $704.11
86 Avg Contract Sale_7 = 1190 - 0.05 87.51 $1,185.62
87 Avg Contract Sale_5 = 349 + 0.462 72.02 $382.27
89 Avg Contract Sale_11 = 475 - 2.00 30.81 $413.37
National Contract Sales (y) $12,646.85
Actual Data:
SIC Code Mean Delta Time Y Actual
15 38.38 $ 1,263.70
17 50.93 $ 322.05
27 141.95 $ 563.78
34 64.33 $ 922.54
35 79.09 $ 473.11
50 99.49 $ 418.89
51 78.57 $ 409.54
55 68.73 $ 773.00
59 21.08 $ 393.26
60 97.03 $ 558.50
65 48.17 $ 669.57
73 78.79 $ 363.73
80 46.87 $ 655.39
81 140.38 $ 496.21
82 40.31 $ 1,751.06
83 51.92 $ 689.96
86 87.51 $ 1,167.07
87 72.02 $ 422.67
89 30.81 $ 412.96
National Contract Sales (Y) Actual $ 12,726.98
These two tables are very close in their outputs, leading the viewer to believe that the regression models must be reasonably accurate, but to make sure that they were statistically valid, other tests were also used. The first of these was checking each regression using the ANOVA method. ANOVA provides a systematic way to address variance in a regression model by breaking it down into components. The ANOVA method generates a table like the one shown below, and while one was created and considered for each SIC Code, for the purposes of this report, the most significant SIC code is Code 80, and therefore the ANOVA table for SIC 80 is presented:
Code 80 ANOVA table:
Source DF SS MS F-Comp'd P-Val
Regression 1 11892389 11892389 21.1 0.00
Residual Error 28 15779145 563541
Total 29 27671534
This Table holds a lot of information that can be used for in depth statistical analysis. The most important piece of information that can be taken from an ANOVA table is the P-Value. That becomes a strong indicator of the strength of the relationship.
R-Squared:
The second piece important test that can be used to assess the validity of a regression equation is to find its R^2 value. To the right is a table listing the R^2 values by SIC code for each regression line.
Quality of Fit
SIC Code Quality of Fit
15 R-Sq = 5.3%
17 R-Sq = 1.9%
27 R-Sq = 0.9%
34 R-Sq = 0.1%
35 R-Sq = 0.0%
50 R-Sq = 1.4%
51 R-Sq = 0.2%
55 R-Sq = 0.0%
59 R-Sq = 1.5%
60 R-Sq = 2.3%
65 R-Sq = 2.8%
73 R-Sq = 3.8%
80 R-Sq = 1.0%
81 R-Sq = 0.0%
82 R-Sq = 1.2%
83 R-Sq = 53.1%
86 R-Sq = 1.4%
87 R-Sq = 0.1%
89 R-Sq = 43.0%
A higher R^2 Value indicates a high level of correlation between the data and the regression model. For the case of the R^2analysis, SIC Code 89 is an excellent example because it has an R^2=43.0%. This indicates that the data supports the hypothesis that is effectively made when fitting a linear regression line to data to help extrapolate a trend. Looking back up at the comparison between the data and the regression outputs, it shows only one dollar difference out of 413 dollars for the Average Sales. That’s less than a 10% difference.
These Regression equations could, if used correctly by Anago, be a very powerful tool for making decisions about how what industries to push advertising in in the future. Additionally, the generally low P-values from the ANOVA tables, and the fact that many of these SIC regressions have strong R^2Values mean that, assuming current market factors remain similar-not always a safe assumption-that they can use these models with a fairly high level of confidence.
Error, Sources and Analysis:
While processing the data, a number of procedural errors arose, both in the analysis and in the gathering of the data itself that caused both difficulty in certain analysis, and larger then desired uncertainty in a few measurements. These errors were considered and accounted for in the final product, but they occasionally caused some trouble both with analysis and with the validity of results. The main cause of the error is based in Anago’s data collection system itself.
There are a few reasons that Anago’s collection system, Telemagic, is the main problem that caused error in the calculations. The system is DOS based, which means it is old and not very easy to work with, this causes some irregularities in its usage. Anago also does not have a standardized system for each franchise to report each sale or to store their data. This makes some inconsistencies in the data and makes it difficult to sort through to find what is needed for the tests.
The other reason that Telemagic is the main problem is that not all of the franchises use the Telemagic system in their offices. These cities use various other systems to record their data, but these don’t always mesh well with the Telemagic system. Some cities don’t record their data at all, which renders them unusable for testing. Because of these inconsistencies and problems with data collection and the Telemagic system, 20-50% of the monthly billing data is missing. This may seem like a substantial amount, but it was well accounted for in testing, for example, the Z tests take into account a 20%, 35%, and 50% reduction in the target sales goal to account for missing data.
Conclusion:
The data resulting from the tests concluded in this assessment can help Anago in many ways, as explained in each section. There are many sources of error in the data collection and records process which needs to be dealt with before Anago can hope to continue optimizing their operations. Hopefully Anago will take these results into consideration in their future endeavors and continue to be one of the fastest growing franchises in the United States.
Index:
INDEX 1A: Mean & STD by City Contract Sales
City Mean Average Delta Time Std Dev Average Delta Time Mean Average Amt Monthly Billing Std Dev Average Amt of Monthly Billing
Atlanta 47.56 60.58 486.7 519
Baltimore 46.6 51.3 769 612
Bay Area 40.37 46.69 635 711
Central NC 145.1 285.3 635 624
Charleston 111.1 49.3 556 390
Charlotte 38.2 67.1 496 603
Cincinnati 1815 8159 775 1217.4
Cleveland 127.7 370.1 604.3 985.9
Columbus 125.9 247.1 610.7 771.6
Dallas 14.94 16.53 340.6 239.6
Daytona 86.6 169.8 873 993
DC 40.4 44.7 600 556
Detroit 91.3 210.8 246.3 105.9
East Ohio 68.1 101.7 738 1169
Hampton Roads VA 157.9 456.8 820 1450
Hawaii 154.6 434.2 1021 1403
Jax 43.2 107.4 661 1283
MontC (Montclair, NJ) 82.3 63.9 949 858
NNJ 26.7 64.8 660 737
ORL 36.1 54.24 733 1501
Philadelphia 26.8 155.6 725.5 828.5
Ral 166.7 796.5 675 2178
Sacramento 40.06 94.89 679.5 1026
Tampa 28.7 185 687.1 1061
Tract (treasure coast) 40.65 62.91 333.8 321.4
Tulsa 91.9 122.8 803 1465
WAP (Western PA) 33.48 52.28 617.5 639.6
INDEX 1B: Mean & STD By City Contract Losses
City Mean Average of Delta (Yrs) Std Dev Average of Delta (Yrs) Average of Monthly Billing Lost Std Average of Monthly Billing Lost
Atlanta 2.154 1.746 530.7 489.2
Baltimore 1.295 0.633 808 700
Bay Area 1.794 1.226 400.2 400.8
Central NC 2.211 1.596 532.3 531.3
Charleston 1.324 0.963 447.1 445.2
Charlotte 1.186 0.73 560 1063
Cincinnati 2.306 10.199 816.9 991.8
Cleveland 2.727 1.938 778 1637
Columbus 2.86 1.756 578 671
Dallas 1.616 1.374 504 827.1
Daytona 1.206 0.839 897 862
DC 1.188 1.161 785 987
Detroit 2.336 2.038 424.2 357.7
East Ohio 1.845 1.919 877 1467
Hampton Roads VA 1.828 1.294 741 1323
Hawaii 2.135 1.539 607 711.2
Jax 2.138 1.525 492 506
MontC (Montclair, NJ) None None None None
NNJ 1.185 0.853 540.8 321
ORL 3.117 2.062 919 1844
Philadelphia 1.526 1.476 733.8 1008
Ral 1.694 1.081 402.5 333.7
Sacramento 1.959 1.82 458.8 536.1
Tampa 3.182 2.459 671.7 807.9
Tract (treasure coast) 1.299 1.074 420 584
Tulsa 1.243 1.201 699 1543
WAP (Western PA) 1.742 1.586 520.2 647.6
INDEX 1C: Mean & STD By SIC Codes
Sic Code Mean Delta Time Mean Amt Month Billing Std. Dev Delta Time Std Dev Amt Month Billing
15 38.38 1264 70.35 1756
17 50.9 322 90.8 357.9
27 137.1 552 188.4 556
34 64.3 923 99.2 1805
35 79.1 473 168.2 597
50 99.5 419.4 330.3 345.2
51 76.6 415.8 215.8 288.5
55 73.5 811 106.1 1001
59 21.08 393.3 45.37 376.4
60 97 558.5 169.8 472.1
65 48.2 670 69.9 1022
73 78.1 365.9 322.7 299
80 46.63 658.2 129.15 740.4
81 140.4 496 350.9 517
82 40.3 1751 94.8 2167
83 52.99 703.8 98.1 812.1
86 87.5 1167 151.2 1490
87 72 422.7 136.1 608
89 30.81 413 40.51 416.9
INDEX 2: Regions Map
Source: http://www.hhs.gov/about/images/hhs-region-map-updated011012.jpg
North Central South Central
Cenci Dallas
Cleave East Ohio
Columbus Tulsa
Detroit South East
East Ohio Atlanta
North East Cent NC
Baltimore Charleston
DC Charlotte
MontC (Montclair, NJ) Daytona
NJ Hampton Roads VA
PHILLY JAX
WAP (Western PA) ORL
Pacific South West RAL
Bay Area Tampa
Hawaii Tract (treasure coast)
Sacramento
INDEX 3A: Pacific Northwest
INDEX 3B: Northeast
INDEX 3C: South Central
INDEX 3D: Southeast:
INDEX 3E: Pacific Southwest:
INDEX 4: Reliability Data:
Monthly Billing
City 95% CI Interval Size
Detroit 179 313.5 134.5
Tract treasure coast 238.4 429.3 190.9
Sacramento 574 784.9 210.9
WAP Western PA 506.1 728.9 222.8
Atlanta 368.9 604.5 235.6
Dallas 212.9 468.2 255.3
Phillidelphia 569 881.3 312.3
Cincinatti 608 941.8 333.8
Bay Area 463.7 807.9 344.2
Cleavland 422.9 785.6 362.7
Columbus 414.8 806.7 391.9
Tampa 489 884.9 395.9
Central NC 414 856 442
Charlotte 275 718 443
Jax 413 910 497
Daytona 610 1136 526
NNJ 368 951 583
Tulsa 477 1129 652
DC 202 997 795
Hampton Roads VA 408 1233 825
East Ohio 310 1167 857
Baltamore 331 1207 876
ORL 259 1207 948
Ral 200 1151 951
Hawaii 487 1554 1067
Charleston 164 1277 1113
MontC Montclair NJ -417 2315 2732
Time Loss
City 95% CI Interval Size
Tulsa 1.031 1.455 0.424
Ral 1.448 1.939 0.491
Jax 1.883 2.394 0.511
DC 0.929 1.446 0.517
WAP Western PA 1.463 2.022 0.559
Phillidelphia 1.245 1.806 0.561
Charlotte 0.891 1.48 0.589
Sacramento 1.65 2.265 0.615
Dallas 1.291 1.941 0.65
Charleston 0.97 1.677 0.707
Bay Area 1.438 2.15 0.712
Hampton Roads VA 1.467 2.188 0.721
Hawaii 1.765 2.505 0.74
Central NC 1.83 2.591 0.761
Daytona 0.802 1.611 0.809
Tract treasure coast 0.883 1.715 0.832
Tampa 2.76 3.597 0.837
Cleavland 2.278 3.176 0.898
NNJ 0.612 1.758 1.146
Baltamore 0.71 1.88 1.17
Atlanta 1.564 2.745 1.181
Columbus 2.257 3.463 1.206
Detroit 1.709 2.963 1.254
ORL 2.419 3.814 1.395
East Ohio 0.89 2.799 1.909
Cincinatti 0.4 4.207 3.807
Fastest to Sell
City 95% CI Interval Size
Dallas 6.13 23.74 17.61
WAP Western PA 24.37 42.59 18.22
Sacramento 30.1 49.95 19.85
Bay Area 29.07 51.67 22.6
Atlanta 33.81 61.31 27.5
ORL 18.98 53.22 34.24
Tract treasure coast 21.97 59.33 37.36
Jax 22.5 64 41.5
Charlotte 13.1 63.2 50.1
NNJ 1.1 52.3 51.2
Tulsa 64.6 119.2 54.6
Phillidelphia -2 56 58
Tampa -0.5 63.2 63.7
DC 8.4 72.4 64
Baltamore 9.9 83.3 73.4
East Ohio 30.8 105.4 74.6
Daytona 40.3 133 92.7
MontC Montclair NJ -19.41 83.9 103.31
Columbus 59 192.7 133.7
Cleavland 58.8 196.7 137.9
Charleston 5.7 174.7 169
Central NC 44 246.3 202.3
Hampton Roads VA 26.7 289.1 262.4
Detroit -42.6 225.3 267.9
Ral -7.2 340.6 347.8
Hawaii -20.8 330 350.8
Cincinatti 69 2933 2864
Monthly Loss
City 95% CI Interval Size
Ral 326.7 478.2 151.5
Jax 407.1 576.8 169.7
Sacramento 368.6 549.1 180.5
Detroit 314.1 534.2 220.1
WAP Western PA 407.4 633 225.6
Bay Area 285 515.3 230.3
Central NC 405.6 659 253.4
Tampa 534.7 808.7 274
Atlanta 367.6 693.8 326.2
Charleston 283.8 610.4 326.6
Hawaii 436.2 777.9 341.7
Phillidelphia 550.8 916.8 366
Cincinatti 631.6 1002.1 370.5
Dallas 309.6 698.3 388.7
NNJ 325.2 756.5 431.3
DC 567 1003 436
Tract treasure coast 193 646 453
Columbus 347 808 461
Tulsa 427 972 545
Hampton Roads VA 373 1110 737
Cleavland 399 1157 758
Daytona 481 1313 832
Charlotte 130 989 859
ORL 296 1543 1247
Baltamore 161 1455 1294
East Ohio 170 1584 1414