Can you use ChatGPT for basic financial modeling? Yes, and here's an example. Should you? Probably not—anyone can quickly do the modeling you see below easily in a spreadsheet. But, interestingly, taking the conversational journey with an LLM can yield interesting insights, as I found out near the end.
ChatGPT's impressive ability to get creative surely generalizes to other use cases. I'd love to .
Good models, bad models
Prompt:
Pretend you are a product manager at a software-as-a-subscription startup. Contracts renew annually. You have 10 customers, each paying $100,000 per year. Your net margin is 20%. Churn is about 10%, meaning 1 customer is likely to not renew each cycle.
As the person responsible for the product roadmap, you have a choice between features that will grow the number of customers 2 per year, increase margin 10% per year, or decrease churn completely. Given a three year time horizon, and a goal of maximizing revenue, what will you do?
ChatGPT responds with its usual verbose preface, then some bullet point data:
As the product manager with a goal of maximizing revenue over a three-year time horizon, I would evaluate each option based on its potential impact on revenue. Let's analyze each choice:
1. Growing the number of customers by 2 per year:
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: (10 + 2) customers * $100,000 * 0.2 = $240,000 net profit
- Year 3: (12 + 2) customers * $100,000 * 0.2 = $280,000 net profit
2. Increasing margin by 10% per year:
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: 10 customers * $100,000 * 0.22 = $220,000 net profit
- Year 3: 10 customers * $100,000 * 0.24 = $240,000 net profit
3. Decreasing churn completely:
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: 9 customers * $100,000 * 0.2 = $180,000 net profit
- Year 3: 8 customers * $100,000 * 0.2 = $160,000 net profit
Based on the calculations above, it is clear that the most effective option for maximizing revenue over the three-year period would be to focus on growing the number of customers by 2 per year. This strategy generates the highest net profit by increasing the customer base and subsequently multiplying the profit from each customer.
Two out of three isn't bad? It didn't seem to understand what I meant on the churn question, so I clarified and asked for a recalculation, which it did correctly. Over my conversation, I had it recalculate often, in part to trick it into forgetting the correct numbers for one option when it recalculated the others. It never failed.
Side note: this seems like a great way to speed up your own reasoning on the way to building initial spreadsheet formulas.
My scenario wasn't yet great for a side-by-side comparison of a scaling play vs. margin. So…
Now assume that we will grow the customer count by 2 each year even without new features, and the new features will now grow the customer count by 4. Please recalculate.
Certainly! Let's recalculate the potential net profit for each option, considering the updated scenario:
1. Growing the number of customers by 2 per year (without new features):
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: (10 + 2) customers * $100,000 * 0.2 = $240,000 net profit
- Year 3: (12 + 2) customers * $100,000 * 0.2 = $280,000 net profit
2. Growing the number of customers by 4 per year (with new features):
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: (10 + 4) customers * $100,000 * 0.2 = $320,000 net profit
- Year 3: (14 + 4) customers * $100,000 * 0.2 = $440,000 net profit
3. Increasing margin by 10% per year:
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: 10 customers * $100,000 * 0.22 = $220,000 net profit
- Year 3: 10 customers * $100,000 * 0.24 = $240,000 net profit
Considering the updated scenario, it becomes evident that growing the number of customers by 4 per year, with the introduction of new features, continues to yield the highest net profit over the three-year period. However, the option of increasing the margin gradually still provides a substantial increase in net profit. It's important to balance the growth in customer base with profitability, as both factors contribute to overall revenue.
Hmm… still only a two out of three. The problem is that Option 3 doesn't include the baseline growth needed to compare it to Option 2.
Note that ChatGPT tweaked the options—the baseline is now at the top and while it isn't quite the same as “reduce churn to zero”, it's a good basis for comparison. Option 2 above is our scale play and Option 3 is our margin play. Each requires “new features”.
Our back-and-forth went on a couple more rounds. I dropped the baseline (so now Option 1 is scale and 2 is margin). I also set explicit margin targets and tweaked the customer growth numbers. Note that my prompt is pretty explicit here:
I believe you misunderstood me. Here are the options I'd like you to project: grow the number of customers by 4 per year without changing margin (still 20%); increase margin from 20% to 50% to 65% to 82%.
However, the answer to this was still missing the baseline growth of 2 customers per year. So:
Close. For option #2, we are still assuming 2 new customers a year.
Apologies for the confusion. Let's recalculate the potential net profit for option #2, assuming 2 new customers per year while increasing the margin from 20% to 50% to 65% to 82%:
1. Growing the number of customers by 4 per year (without changing margin):
- Year 1: 10 customers * $100,000 * 0.2 (net margin) = $200,000 net profit
- Year 2: (10 + 4) customers * $100,000 * 0.2 = $480,000 net profit
- Year 3: (14 + 4) customers * $100,000 * 0.2 = $800,000 net profit
2. Increasing margin from 20% to 50% to 65% to 82% (with 2 new customers per year):
- Year 1: 10 customers * $100,000 * 0.5 (net margin) = $500,000 net profit
- Year 2: (10 + 2) customers * $100,000 * 0.65 = $845,000 net profit
- Year 3: (12 + 2) customers * $100,000 * 0.82 = $1,176,000 net profit
In the updated calculation for option #2, where the margin increases from 20% to 50% to 65% to 82%, while still assuming 2 new customers per year, it becomes apparent that this option leads to higher net profits over the three-year period. The combination of margin expansion and incremental customer growth contributes to significantly higher net profit compared to growing the customer count without changing the margin.
Now we're getting somewhere! Somewhere that we could have gotten with a decent spreadsheet much faster, but again, perhaps the conversational back-and-forth is a good exercise for refining the questions we'd even want to ask a spreadsheet.
From here, I asked how many customers we'd need to add via the scale option for it to outpace margin growth. It needed me to specify ACV (“assume average revenue per customer is $100,000”), after which it got the right numbers and even assumed on its own that customer count needs to be an integer.
In this case, I found its verbosity helpful, as it never just spits out a number without explaining its rationale and calculations step by step. I'd personally struggle setting up a spreadsheet to back into this last what-if answer—in fact I'd probably ask ChatGPT how.
Now it gets really interesting
Let's go forward with a thought scenario, leaving the math behind.
Very good. If we change the goal to be about profit rather than revenue, what other variables do we need to consider, and how would you go about determining the best course of action overall?
If the goal is to maximize profit rather than revenue, there are additional variables and factors to consider. In addition to customer count, net margin, and churn rate, you would need to evaluate and incorporate factors such as customer acquisition costs (CAC), customer lifetime value (CLV), and operational expenses. Here's a step-by-step approach to determining the best course of action:
- Evaluate Customer Acquisition Costs (CAC): Determine the costs associated with acquiring new customers. This includes marketing expenses, sales team salaries, advertising costs, etc. Understanding CAC is crucial as it directly impacts the profitability of acquiring new customers.
- Calculate Customer Lifetime Value (CLV): Determine the average revenue you expect to generate from a customer over their lifetime with your subscription service. CLV incorporates factors like customer retention rate, renewal rates, and average customer lifespan. It helps you estimate the long-term value each customer brings to your business.
- Analyze Operational Expenses: Consider the various operational expenses involved in running your software-as-a-subscription startup. This includes infrastructure costs, software development and maintenance, customer support, and other overhead expenses. Assessing these costs is essential to accurately calculate the overall profitability.
- Assess the Impact of Each Option: Evaluate the impact of different options on profit. Consider the net profit generated by each option over the desired time horizon, accounting for factors like customer growth, margin changes, and churn reduction. Calculate the net profit by subtracting expenses (including CAC and operational costs) from the revenue generated.
- Consider Sensitivity Analysis: Perform sensitivity analysis to understand the potential impact of variations in key variables. Assess how changes in customer acquisition costs, customer lifetime value, or operational expenses would affect the overall profitability. This analysis can help you identify areas where optimization efforts would be most effective.
- Optimize Profitability: Based on the evaluation and analysis, identify the option that maximizes profitability. Select the course of action that generates the highest net profit over the desired time horizon while considering the associated costs and risks.
- Monitor and Iterate: Implement the chosen strategy and closely monitor the actual performance. Continuously evaluate the impact of the implemented changes, gather feedback from customers, and make adjustments as necessary to optimize profitability further.
By considering variables such as CAC, CLV, and operational expenses, and by conducting a comprehensive analysis of each option's impact on profitability, you can make an informed decision on the best course of action to maximize profit for your software-as-a-subscription startup.
The bits about various costs associated with growth are what I was looking for, of course.
I added the emphasis above, because this bit about sensitivity analysis really caught my eye. (Guess I'm not much of a statistician.)
Can you explain sensitivity analysis in detail? How would you perform it and what might an example outcome be?
Certainly! Sensitivity analysis is a technique used to assess how changes in key variables or assumptions can affect the outcome or result of a particular decision or model. It helps in understanding the sensitivity of the results to variations in these variables and provides insights into the robustness of the decision-making process.
Here's an example of how sensitivity analysis can be performed in the context of maximizing profit for a software-as-a-subscription startup:
- Identify Key Variables: Identify the key variables that significantly impact profitability. In this case, it could be variables such as customer acquisition costs (CAC), customer lifetime value (CLV), churn rate, or operational expenses.
- Define Ranges for Variable Values: Determine a range of values for each key variable. For example, you could define a range of possible values for CAC, such as $2,000 to $4,000, and for CLV, such as $80,000 to $120,000.
- Determine Profitability Scenarios: Create scenarios by combining different values for the key variables within their defined ranges. For each scenario, calculate the resulting net profit using the chosen course of action (e.g., growing customer count, increasing margin, or reducing churn).
- Analyze the Results: Analyze the impact of each scenario on profitability. Compare and contrast the net profit values for different scenarios to identify trends, patterns, and sensitivities. Look for scenarios that consistently yield higher profits or identify scenarios where changes in variables have a significant impact on profitability.
- Assess Risk and Uncertainty: Consider the level of risk and uncertainty associated with each variable and scenario. Evaluate the probability or likelihood of the variable assuming different values within its defined range. This analysis helps understand the potential impact of uncertainty on profitability.
- Draw Insights and Make Decisions: Based on the sensitivity analysis outcomes, draw insights about which variables have the most significant influence on profitability. Identify potential risks and opportunities associated with each variable. Use these insights to inform decision-making and determine the best course of action for maximizing profit.
Example Outcome: Suppose, during sensitivity analysis, you find that customer acquisition costs (CAC) have a substantial impact on profitability. You observe that a higher CAC results in lower net profits across all scenarios. This insight highlights the importance of optimizing customer acquisition processes and reducing acquisition costs to maximize profit.
Furthermore, sensitivity analysis may reveal that certain variables have a more significant impact on profitability than others. For instance, you might discover that a 10% increase in CLV has a larger effect on net profit compared to a 10% decrease in churn rate. This knowledge allows you to prioritize efforts and focus on areas that offer the most significant potential for improving profitability.
By performing sensitivity analysis, you gain a deeper understanding of how changes in key variables can impact profitability. It helps you make more informed decisions, mitigate risks, and identify areas where optimization efforts will yield the greatest benefits for your software-as-a-subscription startup.
And now I've learned something I didn't expect to. Nice.
Could we have skipped right to this?
I wanted to see if the quality of that answer depended on the context established earlier. So in a new thread:
Imagine we are product managers who have calculated the ROI for various features based on revenue being the key metric and have determined the best path based on that criterion. If we change the goal to be about profit rather than revenue, what other variables do we need to consider, and how would you go about determining the best course of action overall?
The answer wasn't as good, possibly because I omitted anything about SaaS, but an LLM's memory cells really do hold context that changes answers. When I told it we're a SaaS business in the new thread, it said some interesting and correct things, but nothing particularly insightful. It didn't mention sensitivity analysis at all, so I think the modeling exercises definitely led ChatGPT in that direction.
Conclusions
- After the prompt is fully engineered (which may require lots of context-setting too), ChatGPT does a good job of building a financial model.
- A spreadsheet is naturally a better tool for modeling, but imagine explaining your spreadsheet to people who aren't familiar with the inputs, or the rationales behind the formulas. ChatGPT's chatty responses and step-wise explanations could really help get people up to speed and shortcut skepticism. A companion spreadsheet then becomes a great tool for fussing with the variables in what-if scenarios.
- As always, you have to be careful about looking for any LLM's confident lies.
- ChatGPT continues to delight me with insights I wasn't expecting, namely the sensitivity analysis.
- We've known that context matters to the LLM, but now we've demonstrated one way how. Variations of a prompt in a fresh thread will result in different and possibly less useful information.