- Forecasts demand using Product Life Cycle, linear and exponential techniques
- Calculates NPV, IRR and Payback
- Performs Monte Carlo simulations
- Evaluates Dependency Ranking
- Builds Financial Statements
We continue with the example from the previous article. This time, we will use the Offset function to simulate different scenarios in a business model. The results will be the same, of course, but you can learn how handy this function can be.
Microsoft Excel 2010 has several powerful functions for planning scenarios and forecasting predictive outcomes in financial, statistical and data analysis applications. Examples include the Offset function, used to find a related value to a particular cell from a different part of the spreadsheet; the VLookup and HLookup functions, used to find an exact match without the need to sort the data table being searched; and the Match function, used to find the location of a matching item for use in other functions.
Histograms are the right type of chart to use when you need to display frequencies. For example, your company provides medical services and you need to track how long your customers are waiting at the reception before they are served. You measured waiting times by patient and now you want to present and analyze the data in a concise and consolidated manner. You may create histogram charts in Excel several ways: using the FREQUENCY function, the Data Analysis Tool and using a pivot table. Here is a tutorial to explain how to do this with Excel 2010.
If you have been actively involved in complex business modeling before, you know how iterative (and sometimes frustrating) the whole process can be. In most organizations, the planning process involves people with different responsibilities, backgrounds and egos. Usually, they all want to influence the model in their own way or incorporate new information and expectations as they come along.
In larger organizations this can be overwhelming. If you are the responsible for making sure that all the pieces fit together in an error-free, logical puzzle, you have to know the best practices.
In this post I will point out some good techniques for controlling versions, backing-up and avoid the most common errors in business model development.
One of the most pressing questions in business modeling is how to handle inflation. Should we use nominal values (with inflation) or should we work with real values (without inflation)? Personally, I prefer nominal values, for a series of reasons I will explain. However, you may configure your spreadsheet to use both.
Before the introduction of the Euro, anyone in a big company here in Europe would face the management of the currencies and exchange rates in a business model as one of the greatest challenges in the world. Although things are facilitated today, experience has given us a few valuable lessons we should always keep in mind when handling multiple currencies:
One of the most common pitfalls when working with complex business models in Excel is forgetting to frequently save the file you have been building. To avoid this, a simple solution is to enable AutoRecover for a specific time frame. Learn how...
Have you heard the expression: "garbage-in/garbage-out" before? It defines very well the importance of the input data in business modeling. Along with creating channels for input values and writing consistent formulas, data validation is one of my favourite techniques to get high-quality input data.
It prevents users from entering wrong types of data in the wrong input cells. If your users are still human, you could use this tutorial to validate input data. Read on...
Let's learn how to create a simple business model in Excel for a sales forecast based on monthly advertising expenditure. To do this, we will create a spreadsheet and make three columns for:
- Advertising expenditure, and
- Sales revenue.