How do I model vendors? 🏪

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? :mage:Follow the below steps, and/or duplicate this template. This template also has a few different approaches that might be worth looking at too :slight_smile:

Step 1: Create a category called Vendor. This is where you will add the names of each vendor that you engage with.
2021-11-16 16.09.40

Step 2: Create a linked category called Vendor type (if you are forecasting across more than one category of vendors).
2021-11-16 16.12.26

Step 3: Create a linked variable Amount
2021-11-16 16.14.50

Step 4: Create a linked variable Start Date - this is the first month that you engage with each vendor.

2021-11-16 16.15.27

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
2021-11-16 16.18.18

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”).
2021-11-16 16.19.44

:alarm_clock: 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.

  1. Create another linked category called “Frequency”, and populate your options.

  2. 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.

  3. Then you will want to adjust your “Vendor Spend” variable formula as follows.
    2021-11-17 09.53.05

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 date - 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 date - 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.
  1. If desired, you can also choose to summarize the Vendor Spend by Frequency, as follows.
    2021-11-19 17.48.17 (1)