ExcelFrog
Clear Excel Tutorials
You boss just gave you a problem, and he expects you to model it in Microsoft Excel to find the right answer. What should you do? Where should you start? That's exactly what we will cover in this short tutorial.
Excel is not just about cells and functions, it's also about structure and architecture. Just follow me to unlock the ultimate power of problem solving modelling in Excel :-)
To do this, we first need a problem to work on. Let's use one that I actually had a few months ago.
I have a website selling an ebook, and I was considering doing some ads on a social media app. But before starting to spend money on this, I wanted to know if it might be worth it. So I need to figure out the profits of the campaign.
How would you go about solving this problem? Let's work on this together, in Excel!
The first thing to do is to define our inputs (also called variables). The inputs are the data that we have or can guess, that we will use later to do our computation.
In general, the inputs should be plain numbers, not formulas. For our problem we have 5 inputs:
Then it's up to you to decide the values for each of these inputs. Some are easy to decide like "budget", because you normally know what your budget actually is. Others are much more complicated, like the "cost per visit" that depends on this social media app. For a starting point, let's use these numbers:
So how can we go from this to get our profits?
With all these inputs, we can just use a big formula in a single cell to compute our profits, right?
So we need to do profit = net revenue - costs, and the net revenue is something like visitors * conversion * book price - fees, and the number of visitors is...
Wait, this is already too complicated! Instead of computing directly the result in a single formula, we should do an intermediary step. Here are some computations we should do with our inputs:
visitors = budget / cost per visitbuyers = visitors * will buyrevenue = buyers * book pricenet revenues = revenue * (1 - fees)As you can see this is basic math. Most of the time you won't need to use complex formulas or functions to compute things.
Now with the inputs and the intermediary results, we can finally get to the profits.
The formula is simple: profit = net revenue - costs. Which gives us:
So with our current inputs, we will make $88 in profit. Not bad!
But as we mentioned earlier, some of our inputs might be wrong. Maybe the cost per visit is a lot higher, or the conversion rate is actually lower. We are never sure, the best we can do is to test different scenarios.
It's always worth to spend some time testing different combination of inputs to see how the output is changing.
The coolest thing is that you just need to change the input cells, and no need to modify anything else. The computation works automatically and you can even hide the intermediary results to focus just on the profit figure. Pretty amazing, huh? Personally, I love spending time tweaking the inputs to see how they impact everything.
Dividing a problem into 3 parts (inputs, intermediary, and outputs) makes things a lot easier to follow and understand.
Now you should have a better understanding of problem solving modelling, but that's just the beginning. Once you have the model in place you can do some powerful analysis with a really cool Excel feature: the "what if" analysis. You can learn more about this in the goal seek tutorial.
The structure of your model can be far more complex if you want to do some forecasting with tons of inputs and iterative computations. In this case, try to split inputs, intermediary, and outputs into different tabs. Also use cell styles wisely to better organize your Excel model.
OK, enough spoilers here, we'll take a look at it together in the next tutorial!