Original subtitles

Offsets are useful tools for scenario analysis.

We can see how in this example when we change the scenario,

it changes the assumptions here,

and when the assumptions are changed,

it changes the forecasted Sinek metrics here.

Let's build one scenario analysis.

I split up the video into various steps.

So, they're building on each concept.

For starters, lets define the four steps to take to create a financial forecast.

Step 1 is to calculate

the operating statistics that include the gross and operating margin, and revenue growth.

This is all based on the historical data,

which you can find in the income statement over here.

So, this is step 1.

The first box here.

Step 2 is to create the scenarios.

I have them here in this box here.

So, this is the scenario that we are going to be creating.

Typically, we have a strong base and weak case.

Some business analysts choose to have two additional scenarios as well,

we're going to stick with three cases.

Then come the assumptions as part of step 3.

These assumptions change in response to the scenario that is chosen over here.

This is where we're going to use offset.

Finally, step 4 may be develop the forecasted scenarios that

change dynamically based on

the scenarios that are chosen because that affects the assumptions,

and the assumptions go inside the forecasted metrics.

So, for step 1,

we need to calculate

our gross and operating margin here for a based on a historical data.

So, we have this original data that we have seen before,

and the gross margin is basically one minus the cost of revenue or cost of goods sold,

those two terms are interchangeable over the total revenue.

So, you plug in this formula in here,

copy and paste it, and it gives us the gross margin.

For operating margin, this is your operating profit over your total revenue.

So, now we have our historical margin ratios here.

I've plugged them in here using our index and match functions.

So, I've referred to this as the named box for gross margin you can see,

and this is named box for operating margin.

So, I just said okay within the gross margin array,

give me an exact match for where the ticker symbol and your- as you can see,

ticker symbol and your are matched and give me

that particular gross margin or operating margin,

and plug it in for me into these cells.

So, that's what I did here,

and then four operating margin,

I reference the array is referring to the operating margin

saying give me that exact ticker symbol and your match and plug in that value.

Revenue growth, you're just taking

your current year's revenue over your previous year's revenue minus one.

What essentially does is it gives you the difference

between the two years of revenue based on

last year's revenue so you can see how much

the revenue growth has occurred in the current year based on last year's revenue.

I'm using HPE as a ticker symbol,

so you can see all the 3-years historical data.

You can see this looks different for the companies with just two years data,

but I delete the financial forecast would actually be for one company,

for your own company so,

you're going to have this as a dynamic array.

Okay. So, we have the historical upgrading statistics here and step 1 is done.