How to Model Ecommerce using the Standard Financial Model

Prebuilt for Ecommerce Businesses #

The Standard Financial Model works well for Ecommerce businesses by default, with no edits required, but easy to customize for specific requirements for the conversion funnel, multiple-channels, SKUs, cost of sales, and additional revenue streans. The video above details the acquisition, conversion, repeat purchase, and revenue structure, and how to modify it for your use.

Common Modifications #

• Adding more growth channels. It is common to add more growth channels to detail the acquisition strategy in more detail, and to assume different growth rates by channel. Straightforward to do: simply replicate the existing growth block of calculations on the Revenues sheet, replicate the relevant assumptions on Get Started, or create your own set of calculations using the logic relevant for that channel, and add it into the line used to calculate conversions.
• Expanding the sales funnel. By default the model assumes one conversion point (for example, leads converting to subscribers), but often users will want to model a conversion funnel with multiple conversion steps (for example, leads converting to qualified leads converting to subscribers). The simple way to model that is to use a conversion rate on Get Started that is reflective of the overall conversion rate (just multiply each conversion rate), or if more detail is required, an additional conversion step can be added on Forecast by replicating the structure using the same or simpler methodology for conversions.
• Adding multiple SKUs or products. This can be done with a simple edit to the assumptions on Get Started. Create a table with the different tiers (on Get Started or a new sheet), input the pricing tiers, assume the percentage of subscribers/clients/etc that are on each tier, and calculate the weighted-average using SUMPRODUCT, and input that into the average revenue assumption on Get Started. If more detail is required, then you can add additional revenue builds using the same methodology as the annual and monthly split detailed below.
• Modeling cost of goods sold, shipping, handling, and other product costs. Modeling the associated cost of sales is a default part of expense forecasting on the Forecast sheet. You can add in any cost component, use a forecasting driver or manually input the cost, and the model will handle all the financial impacts automatically. This is covered in the video above.
• Modeling inventory. Prebuilt into the Forecast sheet. Once you've created a cost of goods sold expense, by default the model will assume that inventory is purchased in the same period as the cost of goods is recognized, but you can use the assumptions on Get Started to model lead times (in terms of the number of months of lead time from making an inventory purchase order to being in inventory), minimum order quantities (dollar value of inventory required to purchae at one time), safety stock (a minimum amount of inventory you want to model to hold), and how the inventory is paid for (upfront, or a number of days in arears). This focuses on the cash impact of purchasing the inventory required for the forecasted sales; the inventory calculations to not calculate the number of units of the SKUs, but it could be edited to add that in based on the dollar inventory forecast created in this section.
• Using the Ecommerce Forecasting Tool. The free Ecommerce Forecasting Tool is an alternative way to forecast ecommerce businesses with a simpler build structure, in approximately 10x less lines of code (a pro!) but with less features around growth, conversion, and cohort modeling (a con). It is straightforward to use the tool to replace the prebuilt revenue model in the Standard Model, or to use it to model additional ecommerce revenue components in an easier way than replicating the prebuilt revenue model.
• Using your own ecommerce revenue build. The model can be used for a wide variety of ecommerce, wholesale, and retail commerce businesses, and you can also import and use any model or other template you are using. Details at How to integrate any model with a Foresight base model.

Common Questions #

Common questions and more details at Standard Financial Model. I provide free and paid onboarding support and custom model services, and contact me anytime for questions.