[Back to DAO Page of Fame] Ryan Rayborn Company: Coca-ColaCategory: Regression and Pivot Tables The forecasting system Ryan Rayborn constructed to predict sales of Coke products in Target stores outperformed their legacy system and is currently being subjected to side-by-side testing prior to roll out. Ryan first used Pivot Tables to analyze what in his words were “very ugly” data on the sales of Coke products at Target stores and determined at what level it was appropriate to try to forecast sales. Next he turned to regression to identify those variables that were important predictors, which then became inputs into the forecasting model. To accommodate the company’s requirement to create a matrix of volume forecasts, Ryan conducted yet another round of pivot table analysis. The final forecasting model or “system” Ryan created is now being tested in parallel with the company’s legacy system. As Ryan put it, “May the best model win. I’m hoping it’s mine.” Ryan’s efforts drew the following praise from his manager: “Ryan did an excellent job on the Volume Forecasting project. He thoroughly researched the best practices employed by [the company] …and tested a prototype of a working model that may prove useful in helping us to better predict sales. Additionally, Ryan offered solid recommendations on ways that we can improve our existing processes, such as using a zero cost Excel add-in or training in statistical methods.” Benjamin Potter Company: GeicoCategory: Regression Ben Potter used regression analysis to make inferences about the extent to which lower prices led to higher online sales for Geico relative to Progressive. The challenge given to Ben in his summer internship with Progressive was to figure out why Geico’s online conversion rates (defined as the percentage of people requesting a price quote who subsequently purchased a policy) were higher than Progressive’s. Was it due to the fact that Geico’s prices were generally lower? Was the conversion of a Geico prospect driven by the absolute Geico price or the Geico price relative to Progressive’s price? And, most importantly, how should Progressive respond? Using simple data charts, Ben first established that Geico’s prices were generally lower and that, as expected, conversion rates for both companies declined with the price quoted. In addition, for almost all price quotes, the Progressive conversion rate was lower than Geico’s. Using regression, Ben next looked at prospects who received quotes from both companies and established that relative (rather than absolute) price was the better predictor of conversion. Using this model, Ben was able to predict the gains associated with a change in pricing. Ben’s analysis led to proposed changes in pricing with an estimated impact of $10 million in incremental profit! Josh Burke Company: CampbellCategory: Pivot Tables To quickly and accurately quantify the benefits to Campbell Soup of implementing his suggestion to classify correctly that portion of trade liabilities that are non-current, Josh Burke applied a pivot table to data on the prior year’s 60,000 separate trade promotions. During his internship, Josh was given a project to streamline the trade expense/accrual process for Campbell USA. The trade expense is for trade promotions performed by grocers (Campbell customers) on behalf of Campbell. Campbell reimburses the grocers for these promotions, and Josh observed that the company was classifying 100% of this liability balance in the current portion of the balance sheet. Josh knew this was hurting the company in the eyes of credit agencies who use the breakout between current and non-current liabilities when assessing a company’s credit rating. After assembling data on 600,000 separate trade promotions, Josh used pivot tables to quickly determine that 4% of these promotions were paid over a year after the actual trade event and thus qualified as non-current liabilities. Josh used the 4% number to quantify the dollar amount of liabilities that could be reclassified as non-current as part of his argument to change how these liabilities would be classified in the future. Josh’s work and insights received the following praise from Carolyn Smart, Brand Finance Manager: "I was very impressed with Josh's approach to this project and his use of tools to analyze the data and quantify the impact of his findings. Josh's recommendations not only improved the efficiency and consistency of our trade accrual process, but they also improved the accuracy of our balance sheet account classifications. We have already implemented several of his recommendations and truly value the insights that he brought to one of our largest expenses.” Kevin Graney Company: Natural Resource PartnersCategory: Time Series Natural Resource Partners (one of the country’s largest owners of US coal properties) used to conduct simple best case/best guess/worst case analyses of projects until Kevin Graney came along and showed them how to use Crystal Ball to simulate a complete time series of future coal prices. The new, more sophisticated, approach allows NRP to better quantify the value of projects –especially those involving contingent contracts. One of the first things Kevin noticed when he started his summer internship with National Resources Partners (NYSE:NRP) was their use of “base case/worst case/best case” scenarios when evaluating opportunities to purchase mineral rights (mainly coal) for properties through the United States. The major sources of uncertainty for most of NRP’s investments were the future prices of coal and production levels (as a percentage of predicted production), and the three chosen scenarios often reflected a single price for coal throughout the entire future of a project. This assumption left them with models that were unable to distinguish among proposals involving future contingencies. In addition to introducing the firm to Crystal Ball™, Kevin incorporated a relatively simple times series model of coal prices (with extensions to account for variation in price by type of coal) into their project evaluation simulation model. The more sophisticated models not only gave NRP a more complete picture of risk, they also allowed for the evaluation of project terms involving contingent contracts. Kevin’s work received the following praise from Kevin J. Craig, VP-Business Development: “The model Kevin put together using crystal ball to account for uncertainties in the coal business was useful and informative for Natural Resource Partners. Using historic data to create a future forecast for the price of coal and production volumes will help us to evaluate acquisitions and other projects going forward.” Sihartha Sinha Company: MerkleCategory: Optimization To decide how best to process 150+ million pieces of mail each day, Sid Sinha built Merkle a Solver optimization model. Merkle is a leading database marketing agency with over 1,000 employees and about $250 million in annual revenues. One of the services they provide clients is the processing of incoming mail, and Sid took on the challenge of figuring out how to best allocate the incoming mail for 150 clients to three forms of processing: processing using OPEX machines, Agissar machines, and manually. Each of the two kinds of machines had limited capacity, and the task was how to allocate mail to the two kinds of machines so as to minimize the use of manual processing while meeting the contractual throughput requirements of each customer. Sid quickly recognized this as an opportunity to use Solver, but his first attempt at modeling resulted in a problem too big for Solver to handle. The modeling challenge was the number of integer decision variables reflecting the reality that within any shift a single machine (station) could not be used to sort mail from multiple customers. After several rounds of modeling using creative simplifications and reformulations, Sid was eventually able to create a Solver model “small” enough to be solved. When tested on historical data, the model solutions saved, on average, 20 hours of manual labor per week. Roberto Brillembourg Company: Kraft FoodsCategory: Regression Roberto Brillembourg used regression analysis to help Kraft Foods measure the effectiveness of its TASSIMO coffee continuity program in increasing consumption of TASSIMO coffee. Roberto Brillembourg worked for Kraft foods this summer on TASSIMO --a single-serve coffee maker that uses coffee pods or T-Discs. T-Discs can can be purchased in retail stores or online either one-at-a-time or through a continuity program. The brand team's hypothesis was that the continuity program is the most valuable channel and hence worthy of marketing investment. Specifically the team thinks that single serve coffee is an expandable consumption category where the T-Discs on hand causes increased consumption. Pushing the continuity program would therefore lead to increased consumption. What we know is that, continuity users drink a lot of coffee. The brand team thinks that this is because the continuity program affects consumption. Roberto thinks it might be that heavy users just tend to join continuity more often. To examine this question, Roberto found a tracking study which monitored weekly coffee consumption for thousands of households over a two year period using a microchip placed in their Tassimo coffee machine. Half of the observations were "on continuity" while the other half was "off continuity". Initially Roberto observed that overall average consumption “on” was higher than the average of consumption “off” continuity. For individual consumers who where sometimes in and sometimes out, their average consumption was usually higher when in the continuity program. Because of the strong seasonal trends, however, these averages could be misleading. For example temperature (blue) clearly has an inverse relationship to consumption as show below: Roberto used regression analysis to control for the effects of seasonality and temperature to get a better measure of the lift provided solely from the continuity program. The continuity variable had a high T stat with a positive coefficient. Hence, the regression demonstrated that when people join continuity they tend to consume more T-Discs per week than they normally would. Kraft marketing used this information and moved forward with the incentive program. The regression results helped determine how much money to spend on incenting consumers to enter this continuity channel. Shankar Raja Company: BCGCategory: Pivot Tables During his summer internship with BCG, Shankar Raja segmented the US healthcare market using Pivot Tables and developed a comprehensive, market based, bottom-up revenue forecast to inform the post-merger, strategic planning of a client firm. Shankar worked on a pre-merger integration planning assignment for clients in the healthcare technology industry. Shankar’s objective was to develop a comprehensive, market based, bottom-up revenue forecast for the combined entity, to inform post-merger strategic planning. Shankar made extensive use of Pivot Tables to segment over 200K practices in the US healthcare industry by specialty and practice size. The market potential represented by each market segment was translated to a revenue opportunity for the client using a methodical 5-step process: - The addressable market opportunity was determined by applying penetration and adoption rates by segment - Products in the client’s portfolio were positioned against market segments that they best served - The number of deals to be won was calculated from the addressable market by estimating reasonable sales force coverage and win rates by product and segment - The total order intake was estimated using an average deal size per product - Revenue recognition rules were applied to translate order intake into an annual revenue figure Logic similar to what is outlined above was used to determine the revenue opportunity represented by cross-sell and up-sell deals. Shankar’s analysis is being used by the client CFO’s office to validate Wall St. expectations from the merger and develop revenue targets for individual business units. The tools and modeling skills developed in FY DA & DAO courses definitely paid off for Shankar! Anand Rao Company: BainCategory: Pivot Tables During his internship with Bain, Anand Rao used pivot tables to create first-ever organizational charts based on the reporting relationships and cost center assignments contained in a client firm’s 2500-record employee database. Bain’s Client, a Telecom company’s Business Unit, did not have an accurate picture of their organization and needed to figure out ‘who is where’ to facilitate Organization re-design. The original employee-level data was in excel format tagged with cost centre information. Anand used iterative ‘vlookup’ functions to assign hierarchical levels to each employee. Several pivot tables were then used to organize the data to provide different views. The final output were views of organization by reporting structure, by Line of Business and Unit, an overview of unit and location and overview by employee type. The client used this information and recommendations to facilitate successful re-organization as per strategic Christopher Richins Company: Bain & CompanyCategory: Regression To help a client firm price its transportation offering, Christopher Richins used regression analysis during his internship with Bain & Company to build a model forecasting refrigerated trucking rates for shipping produce across the U.S. When Christopher Richins heard what his work stream would be during his internship with Bain & Company, he knew that taking DAO had been a good choice. Christopher was asked to develop a pricing strategy for a new business based on a model that would predict refrigerated produce truck rates from the Western U.S. to the Eastern U.S. To begin, Christopher quickly found a source of historical weekly refrigerated truck rates and U.S. average diesel prices for the last 4 years and got to the task of building a regression model. He was able to quickly run a regression of truck rates with respect to diesel prices. The results were surprising – fuel price alone resulted in an adjusted R^2 of only 44%, much lower than he anticipated. After analyzing the residuals, he found that there was a cyclical pattern in the results. It was clear that in addition to the influence of fuel prices, there was a seasonal component to truck rates. Considering the nature of the product being shipped (fresh produce), it came as no surprise that there would be a seasonal component to the price. In order to capture the seasonal component of the truck rates, he generated a dummy variable for each of the 52 weeks of the year, and reran the regression with diesel price in the mix. The results of the improved regression were surprising. The R^2 of the model increased to over 94%, and the residuals looked normal. This had significant implications for the pricing model Christopher was developing. From the low point in March, he found that truck rates in June could be as much as $2000 dollars higher just because of the seasonal nature of demand (an increase of 50%). As a result of his regression analysis, the pricing model for this new business includes inputs for the current diesel price, and the week of the year to accurately model produce truck rates from the Western U.S. to the Eastern U.S. This model will enable the client to accurately price their substitute offering and maximize the economic value of their service. Further, because of the excellent data analysis skills he had acquired in DAO, Christopher was able to complete the pricing task in 40% of the time predicted for the project. Will Teichman Company: TargetCategory: Regression In the process of creating Target’s first inventory of water consumption across all facilities, Will Teichman used regression analysis to identify the primary drivers of water usage for the purposes of prioritizing future water-saving initiatives. Target Corporation consistently ranks as one of the most responsible corporations in America – giving away 5% of profits and striving to operate its stores in a manner that is environmentally responsible. As the company’s first MBA intern in the area of sustainability, Will brought an analytical eye to the challenge of measuring and understanding the company’s water footprint. The goal of the project was to prepare the company to address an emerging environmental issue by using available data to identify opportunities to improve water efficiency. Will worked closely with functional experts from across the organization – including store design, operations, and sourcing – to collect data on water consumption and other key store characteristics. He constructed a database that served as a centralized repository for this data - containing monthly water utility data, and bringing together information on store size, location, surrounding retail context, operating activities, and other key characteristics. The database served as a virtual pivot table – eliminating duplicate information and providing the capability to aggregate and analyze the data by store, state, region, etc. Will extracted a dataset that allowed him to regress annual store water consumption on a number of store characteristics. This analysis revealed 8 store characteristics that correlate with significantly higher or lower water use. Using the insights gained from Will’s project, the company is exploring a number of water use reduction projects that will shrink the company’s water footprint and reduce operating expenses. Anand Veeraraghavan Company: Category: Optimization In order to select the best options for growth across five business units of an Indonesian energy company, Anand Veeraraghavan used a stochastic optimization model (built using Crystal Ball’s Optquest) that accounted for 25 key uncertainties and linkages across the business units. Anand worked on a growth case for an Indonesian energy company over the summer. The company had five business units—each with multiple growth options. The goal was to select the growth options that would maximize the NPV of combined business. Selecting the growth options presented a unique challenge - the growth options were interlinked across business units. For example, pursuing an aggressive expansion strategy in the geothermal business would create goodwill with the government (since developing geothermal is a key mandate of the government). This goodwill could potentially help the company win a contract extension with an oil & gas field. The reverse was true as well. The solution was a two-step process. The first step was the define linkages across all business units. Two linkages were defined - handshakes (representing goodwill) and petrotechs (skilled resources that were hard to find in the industry). For each growth option, the number of petrotechs and handshakes required and/or produced was defined. This served as a basis to link the various growth options. The second step was to maximize the NPV by optimizing the linkages. Since the NPV from each growth option was a distribution, Crystal Ball's OptQuest was chosen instead of Excel Solver to find the optimal solution. Sarah Glass Company: Merck/Schering-PloughCategory: Pivot Tables and Regression Sarah Glass used Pivot Tables at Merck to consolidate patient-level prescription data for the $2B cholesterol drug ZETIA into 30+ sources of business as the basis for her regression-driven, bottom-up sales forecast. Sarah Glass helped Merck Pharmaceuticals understand the future prospects for ZETIA, a cholesterol drug that works in the digestive tract to lower LDL cholesterol. The firm anticipated the entry of a variety of new competing drugs focused on HDL and triglycerides, and they wanted to understand how the changing marketplace would affect future ZETIA sales and what they might do about it. Sarah had plenty of data to work with. Almost too much data! Merck/Schering-Plough had detailed, but anonymous, information about every prescription written for ZETIA over the last 24 months. Although way too big for Excel to handle, Sarah used her familiarity with Pivot tables to supervise the ensuing summary analyses of ZETIA use by patient groupings of various kinds. Sarah next used the data to calibrate a regression model designed to forecast sales of ZETIA as a function of the growth expected in various usage categories under various competitive scenarios. Unfortunately, most of Sarah's insights and recommendations must remain confidential. We do know that her analysis is now being incorporated into Merck/Schering-Plough’s profit plan forecast and that the Merck/Schering-Plough brand team is currently in the process of conducting a more granular analysis based on the insights from Sarah’s summer report. Alex Holsenbeck Company: EmbarqCategory: Regression Alex Holsenbeck used regression to forecast stock-option exercise rates as a key component of his Crystal Ball model to forecast cash proceeds, taxes, and share dilution resulting from EMBARQ’s outstanding options. When Alex Holsenbeck was given the challenge to figure out how many options would be exercised by Embarq insiders in the future he began to panic. But then he realized that he probably had learned everything he needed in DAO. After dusting off the cobwebs, Alex used regression as the back bone for a Crystal Ball model that helped Embarq forecast the financial impact of future insider options exercising. The first step was to perform a regression based on historic exercise rates vs. specific stock price reference points. Validating the regression output with academic research on lag effects, Holsenbeck found that 10-day lagged reference points were the most robust. Exercise rates from the historical regression data were then used as key inputs to the simulation model. The combination of exercise rates and stock price paths that were simulated resulted in a range of future option exercising. Alex survived the thorough review from the Treasury group, and soon the model was used throughout the finance department. The tax group and cash flow statement guru were particularly heavy users, and Alex knew that all the regression practice in class had paid off. Bobby Jordan Company: PRTM Category: Pivot Tables Bobby Jordan used pivot-table generated daily performance metrics to both guide and validate performance improvements his consulting team implemented for a distribution center shipping more than $120M annually. As part of an organization-wide operational transformation project for a $16B California utility company, PRTM asked Bobby Jordan to lead the receiving process improvement efforts at the utility’s primary distribution center serving northern California. The distribution center employed a legacy warehouse management system (WMS) that was extremely limited in its ability to generate useful reports for managing the receiving process, though valuable raw data could be extracted. Bobby developed a daily process of importing extracted data into Excel from WMS, using the V-lookup function to segment the data into different major groups of receipt categories, and summarizing the data in a pivot table. The pivot table was then used to track daily cycle time averages, standard deviations, and volumes for the different major receipt categories. This enabled visual charting to track the team’s progress and provide daily feedback for guiding decisions regarding the improvement effort. At the end of 12 weeks, by using the pivot table manipulated data for guidance in implementing “lean” methods, the team managed to reduce weekly average receiving cycle time from 62.77 hours to 36.63 hours, improve receipts per labor-hour by 24%, and deliver the work stream to the client six-weeks ahead of the project schedule. This landed Bobby the following praise from the Director leading the transformation project: "Bob, you've done a great job getting your team to today's decision. I could not imagine a summer associate accomplishing more than you have this summer." Director PRTM Where Innovation Operates The statistical significance of the reduction in average cycle time was tested using an unpaired t-test implemented in Excel. The test resulted in a calculated T-stat of 7.01 and a P-value less than 0.00, indicating that it is safe to reject the null hypothesis that the two means are equal. Kevin Kosefeski Company: DanaherCategory: Optimization Kevin Kosefeski used Solver to identify the optimum production schedule for a Danaher factory in China making hand-held digital test strip readers. Working as an operations intern for the Hach Company, a Danaher subsidiary in Loveland, Colorado, Kevin Kosefeski built an optimization model in Excel to determine the weekly production schedule of hand-held digital test strip readers used in the pool and spa market. The test strip readers were manufactured for Hach at a Danaher factory in Shanghai, China. The excel model covered a future one-year period and considered forecasted weekly demand, inventory holding cost, maximum unit production capacity, six week sea-shipping time, Chinese holidays, and excess capacity desired during the peak of seasonal demand. The Solver goal was minimization of inventory holding cost. In addition to building the model, Kevin trained the company’s low-cost-region manager on the use of Solver and provided her with the ability to update the production plan as the forecast changed. The Hach Vice-President of Operations remarked, “Kevin was given very little direction on his project. I framed up the problem and told him to go find the right people to work with. This was a complicated cross-country, multi-site, multi-continent problem, and Kevin plowed right ahead and provided Hach with a useful product for the future.” Gautam Sukumar Company: DellCategory: Pivot Tables Because Gautam Sukumar used pivot tables to summarize the profitability of over 500K orders filled by Dell, the company now has an accurate understanding of configuration profitability.” Gautam Sukumar used Pivot tables and regression extensively to accurately highlight the benefit of configuration profitability rather than individual commodity profitability. Historically, Dell performed profitability analysis on individual components such as Hard Drive, Memory, Processor, etc and used the results of this analysis to set pricing for its computers. Gautam approached the problem differently. By analyzing profitability of a combination of individual commodities that make up a configuration, Gautam was able to identify several insights to help Dell improve its pricing model. One of the key highlights of the analysis was the identification of irregular discounting worth more than $35MM. Saul Yeaton Company: TargetCategory: Inference Saul Yeaton used data analysis to convince TARGET that the 75% higher cosmetic sales in stores with in-store beauty advisors was due, for the most part, to how the advisors were assigned to stores. Saul Yeaton used data analysis to forecast baseline sales at Target Corporation for a newly introduced cosmetics brand. Using this forecast he was able to determine that in-store beauty advisors, who had been non-randomly assigned to higher volume stores, were not entirely responsible for the nearly 75% increase in sales in their stores. Further, he helped target Corporation measure the effectiveness of staffing by comparing beauty advisor weekly hours worked against weekly sales. These findings helped shape beauty advisor staffing and contract negotiation plans for a specific line of cosmetics which is forecasted to have $75MM in annual sales by 2008. As a result of the analysis Target Corporation was able to better understand the impact of each beauty advisor and more effectively plan staff investments in new stores. Christopher Langbein Company: ConocoPhillipsCategory: Regression Chris Langbein used regression to forecast monthly working capital needs for ConocoPhillips. Chris Langbein used regression analysis to forecast monthly working capital needs at ConocoPhillips. With a standard deviation of monthly working capital net balance running close to a billion dollars, the ability to reduce this uncertainty using Langbein's models will lead to substantial savings through the reduction of the amount of cash carried to cover the monthly fluctuations. Langbein immediately recognized that this month's working capital amount should be the starting basis for predicting next month. With this insight, he set about looking for (and finding) predictors of change in working capital. "I was asked to build a model that could predict key cash flows. In fact, my bosses told me that they weren’t sure that it could be done, but thought it would be a good test for a summer internship. Little did they know what they got themselves into, for I had the entire DAO toolbox at my disposal." "The model successfully predicted the future month’s working capital to within a standard deviation much smaller than the billion dollars of natural monthly standard deviation." Michael Lorence Company: L-3 Vertex AerospaceCategory: Regression Mike Lorence showed L-3 Vertex Aerospace how to use logistic regression and decision analysis to decide which projects to pursue. In the words of Bobby Floyd, Major General (retired) U. S. Air Force, Vice President for Business Development at L-3 Vertex Aerospace: "Mike Lorence worked as a summer intern for L-3 Communications Vertex Aerospace in Madison, Mississippi. L-3 Vertex had $ 1.3B in revenue in 2005. One of the toughest decisions we make is determining what opportunities we pursue. I challenged Mike to develop a methodology that would help us make a better decision; a decision based on data and less on 'it feels right'! Mike mined data, including both quantitative and qualitative data, based upon our bidding history. Then using a logistic regression model, he built an algorithm that predicts the probability of winning a specific opportunity. He then used Crystal Ball to derive a risk adjusted Net Present Value (NPV) for opportunities using that probability of winning. Due to his efforts, we now have a vehicle that "quantifies" our process. The L-3 Vertex Leadership Team was impressed and very appreciative of Mike’s contribution to our organization." Douglas Polen Company: Titan Virginia Ready-MixCategory: Regression Douglas Polen used regression analysis to determine the true source of profitability for Titan Virginia Ready-Mix’s business (the delivery of concrete). Capitalizing on that finding will easily increase profitability by half a million dollars a year (for a company with annual profits of approximately $20 million.) Polen discovered that the speed at which Titan could unload its concrete (Yards per Man Hour) varied by customer and was the most important determinant of customer profitability. Changing to a pricing system based, in part, on unloading time will help the company better serve its more profitable customers. Brian McCahill Company: DanaherCategory: Optimization Brian McCahill built an optimization Excel model the Danaher Corporation used to create a manufacturing strategy for a new $100MM OEM program. The model helped the firm select from among four facilities around the world the one best suited for the production of motors for an industrial equipment manufacturer. The model considered cost and investment stemming from: supply chain and customer delivery logistics, inventory, import taxes, equipment and tooling, labor, and overhead. In the words of Dave Banyard ’04: "Brian was given a broad problem and no standard work to guide him. He was able to quickly assess the key drivers of the problem and build a strong analysis to support his recommendations. He created a flexible model to build a manufacturing strategy for the Industrial equipment manufacturer project and quantified risks where applicable. The model allows for future use on other projects with laser insight on location and supply chain structure." Matthew Kirby Company: Home DepotCategory: Pivot Tables Matt Kirby used a Pivot Table to help Home Depot save millions of dollars. Home Depot asked Matt Kirby to help decide which product classes should be reduced to make room for the proposed 3X increase in space devoted to holiday decorations in 2006. The company originally intended to implement the same reductions across all stores. Matt quickly realized the value in tailoring the reduction decisions across regions. Using Pivot tables and Pivot charts, he was able to summarize the regional differences in category sales leading up to the holiday season. Based on these summaries, Kirby helped Home Depot developed product assortment plans tailored to each region that will be implemented in October-December of 2006. Rick Ramsey Company: GilbarcoCategory: Time Series Rick Ramsey built a time series forecasting model to help Gilbarco forecast monthly sales of its main product, gas dispensers. Gilbarco sells about 900 dispensers per month through their core distributor channel. The predictions from the Ramsey-built model will be used to identify monthly material needs, reduce lead times, and increase inventory turns. At $9,000 per dispenser, the savings should be substantial. The model was a Winter's smoothing model which accounts for cycle, trend, and seasonality. Ramsey build the model from scratch using Excel. He used Excel's Solver tool to optimize the three smoothing parameters.