How to use Excel Solver

Excel Solver is a heavy-duty tool. Let me show you when to use Excel Solver, and how to use Excel Solver. Once you are considering to use Excel Solver in your spreadsheet, you have probably already tried other What-if analysis tools like scenario manager, goal seek, and data table, all of which I have covered in previous videos. Those “low intensity tools” work fine in a lot of fairly simple cases, but sometimes you need heavier machinery like Solver, for example if you are trying to model various constraints that potentially conflict with each other.

⏱️TIMESTAMPS⏱️
00:00 When to use Excel Solver
02:00 Enabling the Solver add-in
02:33 Solver parameters
03:07 Adding constraints in Solver
05:25 Running Excel Solver
07:13 Excel Solver answer report
07:54 Binding and non-binding constraints
08:45 IF functions in Excel Solver
11:07 Scenario analysis vs Solver

All related videos on Excel what-if analysis are gathered in this playlist:    • Sensitivity analysis in Excel  

In this #Excel spreadsheet, we have some input variables here at the top, a profit and loss forecast right below it, and related profitability ratios at the bottom. Most of the cells in the profit and loss forecast as well as the profitability ratios section are linked to the input variables through formulas. This is very important for Excel Solver to work: your input variables must directly or indirectly feed into the target cell.

What’s currently in column B is the base case scenario for a new product introduction: $1 million in expected revenues, $200,000 in expected operating margin. This base case was discussed with senior management. They were supportive of the product itself, but guess what: instead of $200K they want half a million dollars of operating margin for this NPI, and a contribution margin in the range of 45% to 55% of revenue. Let’s see if the NPI team can make that happen. The NPI team has gathered ideas and input from marketing, manufacturing and sourcing that might just make this project work. Let’s use Excel Solver to apply the constraints, and see if a solution comes out!

If we want to use Excel Solver, we first need to verify that it is activated! If you go to the Data tab in Excel, and don’t see Excel #Solver on the far right, then you need to enable the Excel Solver add-in. Go to File – Options – Add-ins – Manage (at the bottom of the screen) – Excel Add-ins – Go. Tick the box next to the Solver Add-in, click OK, and Excel Solver appears on the far right of our Data tab.

Click on Solver, and let’s review the “Solver Parameters” screen. Some people call Excel Solver an advanced version of goal seek, which I think is underestimating the power of the tool. Excel Solver can help you find a solution to a problem subject to multiple constraints.

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, classroom sessions, webinars, and business simulations. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through    / @thefinancestoryteller