Businesses usually will have a list of recurring vendors that they engage with on a regular basis. Oftentimes, you will not want to forecast at this level of granularity as you scale, however some smaller/earlier businesses might. If you do - an efficient way to forecast these vendor payments over time in Causal, is utilizing categories. How? Follow the below steps, and/or duplicate this template
Step 1: Create a category called Vendor. This is where you will add the names of each vendor that you engage with.
Step 2: Create a linked category called Vendor type (if you are forecasting across more than one category of vendors).
Step 3: Create a linked variable Amount
Step 4: Create a linked variable Start Date - this is the first month that you engage with each vendor.
Step 5: Create a linked variable End Date - if you stop engaging with a vendor, you would capture that last month here, so they are no longer forecast into the future.
Step 6: Add a variable in the spreadsheet, called “Active Vendors”, this is a flag (similar to a headcount flag in staff models), that captures which vendors are currently active (1 or 0), according to the Start and End dates you provided. Formula as follows:
if date >= start date and (date ≤ End Date or is_Empty(End Date)) then 1 else 0
Step 7: Add a variable “Vendor Spend”, with the following formula. This gives you the spend per month, for each vendor, for every month that you engage with them.
Active Vendors * Amount
Step 8: Add a variable “Vendor Spend by Type”, which references Vendor Spend that you just created, but groups Vendor by the “Vendor Type” category. You can also add a formula that summarizes the number of Active Vendors by type (simply reference the Active Vendors variable, but group Vendor by “Vendor Type”).
Note: If all your vendors are paid monthly, then the above process will work fine as is. However, if you also have vendors that you pay quarterly, annually, semi-annually etc - you might like to follow the below steps.
Create another linked category called “Frequency”, and populate your options.
Create a linked variable to that “Frequency” category, called “Frequency (# months)” - and populate the months that each option relates to, e.g. monthly = 1 month, annually = 12 months.
Then you will want to adjust your “Vendor Spend” variable formula as follows.
The above formula uses the modulo function, represented as “%”. Modulo returns the remainder when dividing two numbers. To illustrate how this works, let’s use a worked example, for annual contracts, whose frequency of payment is once every 12 months (from their start date):
- When the current
Start date= 0 (i.e. the current date is the start date) → 0/12 = 0, with no remainder → so the if statement is true, and therefore Causal returns the annual spend for that vendor.
- When the current
Start date= 12, 24, 36 (i.e. any multiple of 12 months has elapsed since the start date) - then these equal whole integers 1, 2, 3 etc, with no remainder, so the if statement is true, and therefore Causal returns the annual spend for that vendor.
- If desired, you can also choose to summarize the Vendor Spend by Frequency, as follows.