Business Planning with Python – Inventory and Cash Flow Management
Business planning of small businesses to manage inventory, predict liquidity needs and maximize profitability with data.
Cash flow management can be defined as the process of monitoring and optimizing the net amount of cash receipts minus cash expenses.
After talking with a friend who manages a medium-sized business, I discovered that cash may be the biggest bottleneck to growth.
“We have to refuse orders as we don’t have enough cash to pay suppliers for stock replenishment.”
As a supply chain data scientist, I quickly connected this problem to sourcing, inventory management and distribution planning.
Can we develop a python model to simulate financial and good flows to support business planning?
In this article, I will share the approach and the tools used to build a simple business modelisation of this problem using Python.
We will use my friend’s small business as an example. They sell cups made from renewable materials to coffee shops and distributors.
Summary
I. Problem Statement: Business Planning
How to use business analytics to help a company selling renewable coffee cups?
1. Inventory Management Simulation
Implement an inventory management rule to meet customers' demand.
2. Financial Analysis: Costs & Revenue
Map all the financial flows covering costs and revenue along the year.
3. Cash Flow Simulation
How much cash on hand you have on a weekly basis to run your business?
II. Business Planning Optimization
What can we do to solve liquidity and profitability issues?
1. Scenario 1: Order Quantity Optimization
What if we reduce the order quantity from 8 weeks to 6 weeks coverage.
2. Scenario 2: Air Freight for Inbound Logistics
What if we cut the replenishment leadtime by using air freight?
3. Scenario 3: Sales Channel Optimization
What if we overpass sales representive by selling to distributors?
4. The Optimal Scenario
Let's combine the two best options.
III. Conclusion
1. Improve the model
Let's see the potential improvements we can bring in the model
2. Revenue optimization
Next step is to focus on the pricing strategy to maximize revenue.
3. Sustainable Business with Data Analytics
What about the environmental impact of this business?Problem Statement: Business Planning
This part will briefly introduce the elements I collected to help you understand my friend’s business model.
These points cover
- Inventory Management: order, receive, store and deliver products
❓ When do we need to order to meet customers’ demands? - Finance: costs and revenue flows
💡 Profit & Loss Analysis weekly analysis. - Commercial: sales channels, service level agreements and commissions
❔ How much profit do we make if we sell to XXX?
We will model each of these elements to understand how they interact with each other and to optimize the overall value chain.
Inventory Management Simulation
To start, we will implement an inventory management rule at the model’s core to meet customers’ demands at the lowest cost.
The inventory management rule is a cog in the machine as
- Inventory can become a bottleneck for commercial growth
You can’t ship items you don’t have on hand. - Replenishment capacity is limited by your financial situation
You need cash on hand to pay orders. - Strategic decisions influence the way you manage inventory.
For instance, freight (air, sea) lead time impacts the safety of the stock.
This module generates replenishment orders based on customer demand, lead times, and safety stock parameters.
For this exercise, I used the historical sales from 2023 to simulate what would have been the optimal inventory management.
“We are continuously checking the inventory, and we want to cover at least 8 weeks of coverage for each order.”
To answer this request, let’s introduce a continuous review policy (s, Q)
- Continuous review means that the inventory team will check the inventory level daily.
- (s, Q) If the inventory level is below a certain level s (Pallets), you must order Q (Pallets).
The reorder point is the inventory level you need to meet customers’ demands until you receive your shipment.
We define it using the replenishment lead time, a target cycle service level and the standard deviation of customers’ demand.
I won’t detail too much this part that is not the focus of the article. For more details, check the article linked below 👇 ,
The results look like the chart below.
📈 Legend
- The scatter plot in blue represents the optimal order policy.
- The green plot is the inventory on hand (ioh), i.e. the number of pallets stored in the warehouse.
- The dotted line in the third chart represents the reorder point s.
You can observe that you have a replenishment order when the inventory on hand crosses the dotted line.
💡 Observations
- I am not sure that this policy is the most optimal.
We only translate my friend’s standard operating model into an algorithm. - We remember that the order quantity and the replenishment lead time can be tuned to minimize inventory.
Now that we know when to reorder, we can include financial flows to visualize the cash on hand.
Financial Analysis: Costs & Revenue
The previous section describes the business from the logistics point of view without considering the financial flows.
But my friend’s main issue is the limited liquidity available to order goods to replenish the stock.
Therefore, we will map the financial flows to calculate the available cash on hand for each week.
Revenue
The historical sales are split by sales channel
- Distributors pay 4 weeks after shipment.
4 weeks after each sale, they are credited with the invoiced amount (Unit Price x Volume) - Coffee shops pay when they place the order.
At the end of each week, they are credited with the invoiced amount (Unit Price x Volume)
💡 Observations
Because we don’t consider the previous year’s sales figures, it is normal for the distributor channel to see no revenue for the first four weeks.
Fixed & Variable Costs
- Sourcing & Inbound Logistics Costs
The suppliers and freight forwarders must be paid when shipments leave the factory.
💡 Observations
Orders are ready to be shipped one week after the order is created.
- Storage & Structure Costs
They include the storage of pallets (using a unit price in $/pallet/day) and other recurring costs, such as HR costs and equipment.
💡 Observations
My friend was lucky not to pay minimum fees for storing his pallets in the warehouse.
Commissions Costs
My friend works with independent sales representatives who take a 30% commission on sales to coffee shops.
Non-recurring costs
These costs, paid in one shot, can include purchasing marketing material, special employee bonuses or distributor penalties.
If we summarize, we have
- Revenue flows include the sales from two channels.
Turnover = (Turnover Distributors + Turnover Coffee Shops) - Total costs include fixed, variable, and non-recurring costs.
Total Costs = (Variable Costs + Fixed Costs + Non-Recurring Costs)
💡 Observations
- We have very low structure costs, with less than 10% for fixed costs.
- Commissions represent the second biggest cost category.
Now that we have the visibility of the financial flows, lets have a look at the liquidity balance per week.
Cash Flow Simulation
Calculating the weekly cash flow can help us understand how much cash is needed to sustain this activity until the end of the year.
- Cash Flow = Turnover – Costs
💡 Observations
- The cash flow is always positive, except when paying suppliers and freight forwarders.
How much cash do we have on hand?
If we assume that we start the year with no cash (bad idea),
- The minimum amount of cash on hand is –124,733 $
- The cash on hand is negative in week 3 and week 4.
💡 Conclusion
They would need at least 125k $ at the beginning of the year to run the activity smoothly and pay suppliers on time.
The next section will define several performance indicators and simulate scenarios to provide data-driven business insights.
Business Planning Optimization
Now that our model is in place, we can play with the parameters and simulate different scenarios.
Each scenario will be assessed using four indicators.
- Initial cash on hand needed at the beginning of the year: coh_0 ($)I_nitial Scenario: coh_0 = 124,733 ($)_
- Average cost of goods sold (COGS): cogs ($/Pallet)I_nitial Scenario: c_ogs = 5,057 ($/Pallet)
- Average logistics costs per pallet: log_cost ($/Pallet)I_nitial Scenario: log_cost= 417 ($/Pallet)_
- Average profitability per pallet: avg_profit ($/Year)I_nitial Scenario: avg_profit = 3,686 ($/Year)_
The idea is to measure the business and operational performance along the value chain versus the initial scenario.
Scenario 1: Order Quantity Optimization
As a Supply Chain Engineer, I would start by examining the logistic flows and the inventory management rule.
What if we reduce the order quantity?
My first reaction when my friend explained his liquidity issues was to question the order quantity.
Do you need really need to order for 8 weeks of coverage?
Ordering 8 weeks on average is a way for him to secure enough inventory to avoid worrying about stock-outs (i.e. orders cancelled due to missing inventory).
Now that we have an optimal inventory management rule with a safety stock, we can try to reduce order quantity to Q = 6 weeks of coverage.
Looking at the projected inventory on hand, we closely avoid the stock-out, and the impact on the profitability is not negligible.
- You need less cash on hand at the beginning of the exercise.
Scenario 1: coh_0 = 74,733 ($) | -41 % - A large reduction in the Cost of Goods Sales (COGS).
Scenario 1: cogs = 4,928 ($/Pallet) | -2.6 % - A better profitability per pallet sold.
Scenario 1: avg_profit = 3,815 ($/Pallet) | +3 %
💡 Conclusion
This quick win provides more buffer for the liquidity needs and brings additional profit.
This feedback triggered a profound reflection on the strategic vision of this business’s value chain.
- 🙋♂️ Why not switch to air freight for inbound logistics?
Air freight is costly but provides more flexibility, i.e. lower average inventory. - 🙋♀️ Should we only sell to distributors?
The distributors’ payment terms are longer (4 weeks), but we don’t have to pay sales commissions and have lower outbound logistic costs.
These interrogations are legit, but answering them requires complex calculations that our model can fully automate..
Scenario 2: Air Freight for Inbound Logistics
I experienced that air freight is mainly used for high-value products that require fast delivery (luxury items or automotive parts).
However, I proposed to my friend to do the exercise
- Air freight fares proposed by the forwarder are 3 times higher
- The delivery lead time goes from 4 weeks to 1 week.
We can now reduce order quantity from 8 weeks to 3 weeks of coverage.
💡 Observations
- The average inventory level is lower than previously, which can lead to reduced storage costs.
- We are ordering more frequently and in a lower quantity.
Unfortunately, this does not compensate for the prohibitive air freight costs.
- This leads to an increase in the Cost of Goods Sales (COGS).
Scenario 2: cogs = 5,511 ($/Pallet) | +8 % - That results in a lower profitability per pallet sold.
Scenario 2: avg_profit = 3,232 ($/Pallet) | -12% - Fortunately, you need less cash on hand at the beginning of the year.
Scenario 2: coh_0 = 17,288 ($) | -86 %
To conclude, this is not a great idea as it reduces profitability in the long run.
Scenario 3: Sales Channel Optimization
For this last scenario, we will focus on the sales channel strategy.
To whom and how do we sell our cups?
In the current scenario, we have a mix of direct sales to coffee shops and partnerships with distributors.
If we switch to distributors only,
- Payments are received 4 weeks after shipment
- We don’t have to pay commissions on sales.
0 % sales commission vs. 30 % for direct sales - We can optimize deliveries with combined shipments.
-50% in outbound logistic costs vs. direct sales
The first impact is that we have to wait four weeks to get our first payments, which impacts liquidity needs.
- You need more cash on hand at the beginning of the exercise.
Scenario 3: coh_0 = 197,602 ($) | -58 %
However, you are cutting the commission costs, which improves the profitability.
- Great impact on the Cost of Goods Sales (COGS).
New Scenario: cogs = 3,172 ($/Pallet) | -38 % - A better profitability per pallet sold.
New Scenario: avg_profit = 5,068 ($/Pallet) | +37 %
The Optimal Scenario
This little exercise provides better visibility and insights on maximising profitability without impacting the business.
If my friend wants to maximize the profitability of his business, he needs
- To get more orders from distributors and stop direct sales.
- Switch to six weeks of coverage when ordering from suppliers.
If he follows this plan, data says that he may increase its profit by 33%.
Conclusion
This approach enables the translation of opaque operational procedures and business practices into a simple model.
Improve the model?
This model enables us to understand how each component of the value chain interacts with each other.
The idea was to provide in one click the answers to questions like:
- What if I switch from sea to air freight?
- What is the best sales channel?
- What is the impact of logistic costs on the overall profit?
What’s next? More granularity and additional cost structures.
Although this simple model already provides key strategic insights, it has limitations.
- Purchase cost structure ** should include MOQ and degressive pricin**g.
Based on this structure, you can find the optimal order quantity to minimize the cost of ordering and receiving your products.
More details in this article,
- Forwarders and transportation companies invoice based on volumes and service level agreements.
If we offer flexibility to logistics service providers, they will have more opportunities to optimise their routes and reduce their prices.
As a supply chain solution manager, this was a frequent exercise
An example is in this article.
- Fixed costs must be detailed by category: CAPEX, HR, utilities, …
I have shared an example of the cost breakdown of warehousing operations on my YouTube Channel,
- Sales pricing can include reductions for shorter payment terms or degressive amounts based on ordered volumes.
- We can expand the scope to multiple items to sell and consider a mix of products to optimize the costs and revenue.
We can use linear programming and Python to help my friend maximize profitability by selling the right items while considering liquidity, storage, and supplier capacity constraints.
You can learn more about this methodology in this article,
Next Step: Revenue Optimization
In the following article, I tackle the topic of revenue growth and profitability.
Revenue Optimization is implementing strategies to maximize a company’s revenue while maintaining profitability.
What if we implement degressive pricing to boost sales?
My friend partnered with a senior executive with over 25 years of experience in the Food & Beverage industry.
My Friend: “How can I assess her pricing strategy to ensure that we stay profitable?”
This article uses the model to assess pricing strategies with five growth scenarios.
After implementing a degressive pricing mechanism, the objective is to estimate the minimal growth required to keep the same profitability per pallet sold.
For more details, check the article.
Business Planning with Python – Revenue Optimization
What about the environmental impact of this business?
Profitability x Sustainability
We can optimize the supplier selection based on profitability or sustainability constraints.
This initial model is considering a single supplier for our coffee cups.
We want to reduce the amount of water used to manufacture our cups.
However, my friend is working on diversifying his sourcing by qualifying suppliers in different parts of the world.
After collecting data from these different suppliers, we can use a simple web application I developed to help us design the optimal supply chain network.
The algorithm automatically selects the best suppliers based on an objective, such as minimising cost or a specific environmental metric.
It creates the supply chain flows to produce and deliver goods to your customers.
For more details, check this article
About Me
Let’s connect on Linkedin and Twitter. I am a Supply Chain Engineer using data analytics to improve logistics operations and reduce costs.
If you need consulting or advice for your supply chain transformation, please contact me via Logigreen Consulting.
If you are interested in data analytics and supply chain, please visit my website.
💌 New articles straight in your inbox for free: Newsletter
📘 Your complete guide for Supply Chain Analytics: Analytics Cheat Sheet
Share This Article
Towards Data Science is a community publication. Submit your insights to reach our global audience and earn through the TDS Author Payment Program.
Write for TDS
