Overview

This course equips learners with advanced skills in building and analyzing models using Excel to solve real-world business problems. Participants will learn to optimize decision-making processes using Solver, conduct sensitivity analysis to refine model outcomes, and apply advanced integer programming techniques to tackle complex scenarios. The course also covers sophisticated methods for solving assignment and transportation problems and introduces simulation techniques to analyze uncertainty and variability in decision-making. By the end of this course, learners will have the tools to enhance efficiency, improve resource allocation, and drive strategic decisions using Excel’s powerful modeling and analytical capabilities. What sets this course apart is its focus on actionable insights and practical, hands-on applications of advanced techniques, ensuring students are prepared to address challenges across a range of industries. Whether optimizing operations, solving logistical challenges, or preparing for uncertainty, this course provides essential skills for making confident, data-driven decisions. Prerequisite knowledge of basic Excel functions and foundational analytics is recommended.

Syllabus

Modeling in Excel: Elementary to Advanced

In this module, we do a quick review of how to model business problems in Excel and how to solve for them using Solver.

Sensitivity Analysis

We have learned about how to formulate linear programming problems and how to solve them using Excel Solver. In this module, we will learn about sensitivity analysis. So far in all of our models, we assumed that we know every coefficient with certainty. This unfortunately is hardly the case in real life. Sensitivity analysis allows us to quickly check whether our optimal solution changes when certain parameters change. For example, what if you were not able to receive as much supply as predicted? Would your optimal solution change? How much profit would you expect to lose? By the end of this module, you will know how to answer questions like these.

Advanced Integer Programming

In this module, we dive into the powerful technique of integer programming, a branch of optimization that requires decision variables to be whole numbers, making it particularly valuable for real-world business decisions. Unlike linear programming, integer programming is essential when dealing with constraints that require discrete decisions, such as determining the number of products to manufacture, selecting projects within budget limits, or optimizing logistics and workforce scheduling. Using Excel's Solver, you'll learn how to model and solve advanced integer programming problems. This module will equip you with a versatile and widely applicable skill set, helping you approach complex decisions with confidence and precision.

Advanced Transportation Problems

In this module, you will dive into the fascinating world of optimization and decision-making to tackle real-world logistical and operational challenges. Explore how businesses assign tasks efficiently, transport goods cost-effectively, and manage complex networks of supply and demand. You will learn to build mathematical models that represent these problems and master powerful optimization techniques. Discover how to evaluate solutions for feasibility and optimality, and transform mathematical results into strategic insights for decision-making in Excel using Solver. By the end of this module, you’ll have the skills and confidence to address complex business challenges and add significant value to organizational operations.

Simulation

In all of the optimization problems we studied, we assumed that the values of the parameters were known exactly. We tried to study the impact of uncertainty in our parameters through sensitivity analysis. While sensitivity analysis is useful, it cannot capture large deviations or simultaneous changes to many parameters. In this module, we will learn a new modelling tool called simulation. Simulation is a powerful technique used to model and analyze complex systems, especially when uncertainty and variability are key factors. In this module, we will explore how to use Excel to build simulations that aid in decision-making and risk analysis in business contexts. By learning to implement simulations in Excel, you will gain practical skills that can be applied to a variety of business analytics problems, including financial forecasting, inventory management, and project scheduling.