Creating accurate forecasts is essential for effective financial management. In this article, we’ll explore various formulas you can use to build a weekly forecast based on actual performance, categories, and indicators. Let’s dive in! 🚀
1️⃣ Based on Actual Performance
Average of the past 3 months
To calculate the average of the past three months for a specific category, use the following formula:
(value("categorie name", -12) + value("categorie name", -11) + value("categorie name", -10)+value("categorie name", -9) + value("categorie name", -8) + value("categorie name", -7)+value("categorie name", -6) + value("categorie name", -5) + value("categorie name", -4)+value("categorie name", -3) + value("categorie name", -2) + value("categorie name", -1)) / 12Average of the past 6 months
For a broader perspective, you can calculate the average over the past six months:
(value("categorie name", -24) + value("categorie name", -23) + value("categorie name", -22)+value("categorie name", -21) + value("categorie name", -20) + value("categorie name", -19)+value("categorie name", -18) + value("categorie name", -17) + value("categorie name", -16)+value("categorie name", -15) + value("categorie name", -14) + value("categorie name", -13)+value("categorie name", -12) + value("categorie name", -11) + value("categorie name", -10)+value("categorie name", -9) + value("categorie name", -8) + value("categorie name", -7)+value("categorie name", -6) + value("categorie name", -5) + value("categorie name", -4)+value("categorie name", -3) + value("categorie name", -2) + value("categorie name", -1)) / 24Same month in the previous year
To reference the same month from the previous year, simply use:
value("categorie name", -52)You can also apply a multiplier to the last year's value by using * :
Example : value("categorie name", -52)*1.2
2️⃣Based on a category
If you want to base your forecast on a specific category, you can use the following formula:
value("categorie name",0)For example, to calculate 90% of the category “sales” of last week, you would write:
Example : value(sales,-1)*0.9
3️⃣ Based on an indicator
To refer to an indicator, user this formula :
value("indicator name", 0)For instance, if you want to calculate the salaries of four employees (each at $3000) using the manual indicator “number of employees” for the same week, you would use:
Example : value(number of employees, 0)*3000
💡Good to know : For more information regarding custom formulas, we recommend checking out our dedicated articles on Creating a Calculation Formula and Defining a Forecast with a Formula