Analytics by the Gallon: Maximizing Revenue with Excel Solver Part 1

With summer on its way, thoughts of traveling and taking vacations are on the forefront of every American mind. As families enjoy their summers together either on the road or in the air, transportation vendors around the country can expect an influx of revenue. But how do the gas companies ensure customers purchase gas from them as they travel rather than from their competitors? It's all in the price per gallon.

As a gas company, or any company/organization looking to maximize its profit, Excel Solver can be a powerful tool to quickly visualize the relationship between business variables and constraints and their impacts on potential profit and sales. When integrated with other applications and environments, solutions found using Excel can be transformed into web applications, PowerPoint presentations, and other visualization tools for a variety of marketing, sales, and business purposes. An illustration of using Excel Solver to determine ways to maximize profits of gas sales is detailed below in Figures 1-4.

According to the U.S. Energy Information Administration [1], the average cost of regular gas along the East Coast in late June of 2014 was $3.67/gallon. By contrast, the average price per gallon in Washington, D.C. for the same time was $3.95/gallon [3]. Suppose you are a gas station owner in Washington, D.C., a common tourist destination. At what price should you sell your product in order to increase profit by 5% from 2013, where the average price per gallon in June was approximately $3.74/gallon and yearly profit was $2 million, while remaining competitive with local competitors?

Figure 1: Average Retail Price for Regular Gasoline

Figure 2: Average Retail Price Chart (24 months)

After you have input the target cell, changing cells, and constraints, what does Solver do? Any specification of the changing cells that satisfies the model's constraints is known as a feasible solution. Solver searches over all feasible solutions and finds the solution which maximizes or minimizes the target variable against the optimization parameters.] For instance, in our gas profitability example, any price per gallon that satisfies the following constraints would be a feasible solution:

Price per gallon is less than the local average.

Quantity of gallons sold is greater than or equal to the quantity sold in 2013.

Figure 3: Excel Solver

For this example, the Solver Parameters dialog box should point to the Target Cell F13 for Scenario 1 and to the Variable Cells F14 and F15 for Scenario 1 with Constraints "Avg Price/Gal" less than or equal to $3.95/gallon average (F14 <= 3.95)="" and="" "gallons="" to="" sell"="" greater="" than="" or="" equal="" to="" 534,759,="" the="" amount="" of="" gallons="" of="" gas="" sold="" in="" 2013="" (f15="">=534759). Additionally, the formula for "Profit 2014" for each scenario is calculated as Avg Price/Gal * Gallons to sell or F14*F15 for Scenario 1. Since we are attempting to determine at which price to sell our gas in order to achieve a 5% profit increase from 2013, or a goal of $2,100,000, the option to set the Target Cell to a Value of $2,100,000 should be selected. Note that since the mathematical representation for profit (x*y, where x = price and y=quantity) is nonlinear, the GRG Nonlinear Solving Method was used for this example.

Figure 4: Example - Using Excel Solver

After the model has been set up according to the business problem, clicking Solve will run Excel Solver and produce a series of reports which summarize the solutions found. In the example above, the solution to Scenario 1 as displayed states that in order to achieve a 2014 Profit goal of $2,100,000 while maintaining the quantity of gallons sold from 2013 and staying below the local average price, our gas must be sold at a price of $3.92/gallon to remain competitive. Similarly, Scenario 2 illustrated above suggests that to achieve a 25% increase in profit, we must meet the average price of $3.95/gallon and increase our sales.

Testing the Model

Prior to committing to the fidelity of any model, the results of the model should be tested. For the example above, a simple test might include some basic number crunching to gain an idea of expected gas prices in order to reach a goal of $2,100,000 for 2014.

For instance, with the knowledge that in 2013 gas was sold for $3.74 /gallon and a profit of $2,000,000 was achieved, you can calculate the quantity of gallons sold in 2013 as profit/price, or approximately 534,759 gallons. With this information and the knowledge of the relationship between profit, price and quantity of sales (profit = price * quantity), the 2014 price of gas can be calculated as 2014 profit goal / 2013 quantity, or approximately $3.93/gallon which matches the result of the model.

Though this example is somewhat simplistic, the analytical power behind Excel Solver greatly exceeds the capabilities displayed here. Whether small or large, simple or complex, linear or nonlinear, Excel Solver can serve as a quick and easy to use analytical tool when your problem and its constraints are well known.

In addition to Excel Solver, a variety of commercial optimizers exist that can be utilized to solve a variety of complex problems, both linear and non-linear. Gurobi supports interfaces for a variety of programming and modeling languages such as C, MATLAB, R, Java, and Python, to name a few. More details on Gurobi can be found at the link below.

On the other hand, a readily available open source solution with similar brute power and capabilities as Gurobi and Excel Solver is OpenSolver, a VBA linear and integer optimizer for Excel which extends the capabilities of Excel Solver with a more powerful Linear Programming solver. More details on OpenSolver can be found at the link below.