1. Income statement budgeting and forecasting
In this screencast, we will learn how to add the budget and forecast for each line item to your income statement and create variance measures. Recall that a line item is each item reported on the left side of the income statement, like operating or revenue or marketing.
The income statement mainly reports revenues and expenses. Being able to budget and forecast these items helps improve a companies success.
First let’s take a look at our new budget and forecast tables for 2019-2020.
We can see here that they are very similar to our actuals table.
To better understand our data, let's take a look at our budget table and our forecast table.
Now let’s look at our income statement. We see that we are viewing our data on a yearly basis. Now we are going to the budget table. You can find this in your data pane, by using the measure “budget” under “Model Measures” pane.
Now let’s add the forecast table. Similarly to the Budget table, we navigate to the data pane and drag forecast under budget in the Visualization pane.
The results are the actuals budget and forecast next to each other in our income statement.
Similarly to the balance sheet, the budget and forecast variance are actuals - budget and actuals - forecast.
First we will calculate the Budget Variance similar to our measures for the balance sheet, we use the Logic “if” function.
Let's call this measure “Variance of Actuals vs Budget, #”
We use the Logic “if” function.
Which tells us If actuals are greater than 0, then actuals - budget is reported where positive values > 0.
And If actuals are less than 0, then - (actuals - budget) where positive values < 0.
Your equation should look like this:
Variance of Actuals vs Budget, # =
var result
= if ([Actuals]>0, [Actuals]-[Budget], -([Actuals]-[Budget]))
return
result”
You will notice that you are given the # of Actuals vs. Budget.
We will add both variances next to the budget in our visualization pane.
We will change the names of each column to vs. Budget, # and vs. Budget, % respectively
With the slicer we can view the different information based on year.
Now we have this information and we are going to focus on the gross profit margin for actuals.
We know that `Gross Profit Margin = (Net Revenue - COGS)/(Net Revenue)`
So we will create a measure called "Actuals Gross Profit Margin" using measures that have been created for you.
Your equation should look like this:
= ([Total Net Revenue] - [Total Cost of Sales])/([Total Net Revenue])
Now it is your turn.
2. Let's practice!