Author: Mike Rakanovic, MMSc
As globalization and technological innovation have flourished, the amount of competition in the global market has drastically increased. Managers are continuously looking for powerful solutions to cut costs, become more efficient, and increase profits.
In response to these competitive conditions, companies are incorporating advanced analytics by investing thousands of dollars in specialized software to help optimize operational performance. The costs of acquiring new software can amount to large expenses (monetary value of the software itself, and time used for training employees) that many smaller businesses cannot afford.
Here are some advanced analytical tools for common business issues that only require Microsoft Excel. You will need an understanding (or one of your employees to have an understanding) of statistical analysis, simulation, and optimization.
Inventory
Problem: Unit sales fluctuate on a monthly basis with no long term trends and you’re unable to make an accurate prediction. Trying to forecast how many units needed in stock to meet sales just results in stockouts (lost sales) or too much in stock (increased inventory holding costs and taking up inventory space for other products you're selling).
Solution: Monte Carlo simulation is a computer simulation of an actual system (in this case, the system is unit sales) based on previous data (i.e., your unit sales during lead time for new inventory ordered). Once a simulation has been done, you’ll be able to determine probabilities of stockouts with certain inventory levels. This will allow you to determine a re-order point that will minimize stockouts. You then can use the Economic Order Quantity (EOQ) formula to find an order quantity at the re-order point to minimize your annual inventory costs (The EOQ formula can be found in textbooks or online). Simulation can be done in Excel with Solver (a free Excel add-in).
Transportation
Problem: Transportation costs are too high and a solution is needed to minimize costs.
Solution: A linear program (LP) uses mathematical programming to give you an optimal (best possible) solution to minimize or maximize an objective (in this case, the objective is to minimize transportation costs). You will need someone who has knowledge in linear programming to create the model and enter it in Excel so that Solver (a free Excel add-in) can solve the LP. The Excel output will give you an optimal solution based on your per unit shipping costs (to minimize overall expenses) or provide a shortest route for travel (to minimize total distance needed to travel from origin to destination).
Advertising
Problem: You put advertisements in the paper, on a billboard, or any type of advertising outlet and you're not sure if they helped increase your sales.
Solution: Paired t-test, which is a statistical analysis tool, can test if there is a statistical significance (mathematical proof) of a difference in your daily sales before and after your ad campaign. It can detect small differences no other tests or the naked-eye can detect. Sure, using the ROI (Return On Investment) can tell you the return, however, you have to wait until your ad campaign is over to tell if it was worth the advertising dollars or not (and risking money you could have saved in advertising by cutting the campaign off early). A t-test only needs a few days of sales performance information to detect whether the ad campaign is making a difference to your revenue. Paired t-tests can be conducted through the data analysis option (also a free add-in).
Product / Service Performance
Problems: Customers complaining about your products or services.
Solution: Statistical Process Control (SPC) is used for detecting changes in your processes/services as early as possible. Control charts are based on probability theory and created from previous data of your operations. These illustrate whether operations are running smoothly and can provide the foundation of a strong quality control system that can detect problems early. Control charts can be created through the charts option in Excel.
Sales Performance
Problem: Making sales, but you want to know exactly what is (and what isn't) causing them to fluctuate.
Solution: Regression analysis is used to determine relationships between variables (i.e., sales and prices). The output that Excel generates will tell you exactly what variables are influencing your sales and by how much approximately, depending on the statistical significance (mathematical proof) of the correlation (relationship). From the output, a predictive model for sales can be created. Regression can be done through the data analysis add-in.
These are only a few of the many applications of advanced analytics in business operations.
Contact Info: mikerakan@gmail.com