Forecasting with Crystal Ball

I´ve told you about Crystal Ball capabilities and how you could forecast dependent variables when you are determining the Net Present Value of the Discounted Cash Flows.

I´ve considered an exercise in an ideal world without taxes and none complexities to simplify the comprehension of the capacities of Crystal Ball.

In this simple exercise considering Forecasts for the Net Present Value I´ve assigned distributions of probabilties for the following variables :

• Gross Margin
• Discount rate

Of course you will never have certainty about the final values for the forecasted variables but at least you will have a wide scope and a probabilistic distribution, in this case of the Net Present Value of the Discounted Cash Flows.

Parameterized Forecasts

To avoid excessive relax I was working a little on Parameterized Forecasts of Integrated financial Statements.

I had to work with this in the earliest times of my career and still I use to make models more complex like this, but in essence the main framework is considered here.

I have considered just the essentials to show how parameterized models works and specially in the construction of the Financial Statements Forecasts considering at least :

• The Balance Sheet
• The Income Statement
• The Cash Flow Statement

I was focused solving an old problem which have to deal with negative final balances when you are forecasting with average collection periods and the initial balances are assumed to be zero.

I think I have solved this problem in a reasonable way considering a discrete formula using the cleared final balance when using the average days or just the Initial balance plus the entries.

In this way we can play this model considering all the initial balances to be zero and if you want to try with an initial Balance Sheet, you can do it as well.

Note this forecasts consider many variables whish can be sensibilized with a Montecarlo Simulation software like Crystal Ball. In this case you can assign probabilities to any variables and so forth the simulator will yield a probabilistic distribution for the forecasts of the dependent variables after 1.000 trials

I am using just the student version and it have capabilities to assign probabilistics distributions on 6 variables and to forecast the distribution of the values for just one dependent variable. It is supposed here that the proffesional version must have expanded capabilities making every analize to be more confident and with a wide scope.

Anyway I will show you the Crystal Ball capabilities in the next post.

In the meanwhile you can do an exersice using the following capacities in Excell Spreadsheets

• You can Build several Scenarios for your customized model
• You can use the Find Goal function to find a desired parameter

As an example I´ve used the Find Goal tool to find the Average Collections Period subject to the final balance in cash account will be zero at december.

I hope you will have some fun with this Parameterized Forecasts of Integrated Financial Statements .