Inventory & Cash Flow Optimization

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 business owner who manages a medium-sized business, we 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.”

We 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, we will share the approach and the tools used to build a simple business modelisation of this problem using Python.

This small business will be used as an example. 

They sell cups made from renewable materials to coffee shops and distributors.

Problem Statement: Business Planning

This part will briefly introduce the elements collected to help you understand our customer'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, we 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.

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.

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 our business owner's 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

• 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

• 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

• Non-recurring costs
  These costs, paid in one shot, can include purchasing marketing material, special employee bonuses or distributor penalties.
• Commissions Costs
  They work with independent sales representatives who take a 30% commission on sales to coffee shops.

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 ($)
  Initial Scenario: coh_0 = 124,733 ($)
• Average cost of goods sold (COGS): cogs ($/Pallet)
  Initial Scenario: cogs = 5,057 ($/Pallet)
• Average logistics costs per pallet: log_cost ($/Pallet)
  Initial Scenario: log_cost= 417 ($/Pallet)
• Average profitability per pallet: avg_profit ($/Year)
  Initial 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 Experts, we would start by examining the logistic flows and the inventory management rule.

What if we reduce the order quantity?

The first reaction when our customer explained his liquidity issues was to question the order quantity.

Do you 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

We usually believe that air freight is mainly used for high-value products that require fast delivery (luxury items or automotive parts).

However, did 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 the business owner 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%.

Do you want to try yourself?

Inventory & Cash Flow App

A prototype of this model has been deployed on a web app: LogiGreen App

This prototype, currently in development, is already available for test.

How to test it?

You can test the model without uploading data by just following the steps below

Step 1: Go to the business planning page

• 🔗Link: Business Planning Page

Step 2: Select 'Cash Flow & Inventory' on the left side bar

You will arrive in the presentation page of the module where you can find a brief presentation of the model with the inputs parameters and the results provided.

Step 3: Go to the 'Analysis' tab

You can select 

• Logistics parameters for the inventory management and lead times
• Sales parameters related to the channels, price and commissions
• Add payment terms for suppliers and customers

Step 4: Launch the Analysis ​

You can visualize the results.

Do not hesitate to refresh if you face a problem.

Any question?

For any inquiries, please contact us at contact@logi-green.com

This blog post is based on articles originally published on Medium

• Business Planning: Inventory & Cash Flow Optimization by Samir Saci