Financial Modeling

Working Capital Schedule

Firstly, let us begin with the ‘Working Capital Schedule,’ which is a statement of change in the net working capital of a company. 

 

Working Capital – Steps 

  • Create line-items - for example debtors, creditors, inventories, 
  • Working capital items
  • Reference the historical balances
  • Project future working capital balances
  • Calculate projected cash-flows
  • Link to balance sheet and cash-flow statement

Working Capital Schedule - Assets

 

 

From the above schedule, we can notice that with an increase in the scale of operations and cost of goods sold, all the individual line items of working capital are increasing. Hence, year on year, we have to invest more and more in the working capital for the business. 

 

Working Capital Formula

Working Capital = Current Asset - Current Liabilities

 

(For financial modeling purposes - Current asset ignores cash; and current liabilities ignores revolver- which is overdraft facility)

 

Current assets on the balance sheet

  • Trade receivables
  • Short-term loans and advances
  • Inventory
  • Other current assets

How to Forecast Current Asset Items ?

Forecasting Trade Receivables

 

 

Assumption: We need to assume Receivable Days Outstanding. Receivables Days Outstanding is the average number of days it takes trade receivables to be converted in cash i.e., how long the company takes to collect cash from its account receivables.  


On the basis of this assumption and the forecasted sales amount the accounts receivable is calculated. Then, link these accounts receivable to the balance sheet.

 

Forecasting Inventory

 

 

Assumption: We need to assume Inventory Days Outstanding. Inventory Days Outstanding is the average number of days it takes the company to convert inventories into sales. 

 

On the basis of this assumption and the forecasted cost of goods sold amount the inventory is calculated. Then, link this inventory amount to the balance sheet.

 

Forecasting other items

  • % of net sales.
  • Can use historical data and project it into the future.
  • These items are usually not a substantial amount as compared to accounts receivable and inventory.

Linking Working Capital to Balance Sheet

 

The highlighted region comes from the working capital schedule. 

 

WORKING CAPITAL SCHEDULE – (LIABILITIES)

 

Current Liabilities

  • Create line-items
  • Working capital items
  • Reference the historical balances
  • Project future working capital balances
  • Calculate projected cash-flows
  • Link to balance sheet and cash-flow statement

Current liabilities on the balance sheet are: 

  • Trade payables
  • Short-term provisions
  • Other current liabilities
  • Even short-term debt (also called as revolver) is a part of the current liabilities items, but it is forecasted later.

FORECASTING CURRENT LIABILITY ITEMS

 

 

Forecasting Payables

 

Assumption: We need to assume Payable Days Outstanding. Payable Days Outstanding is the average number of days a company takes to pay back its trade creditors/suppliers. 

 

On the basis of this assumption and the forecasted cost of goods sold amount the accounts payable is calculated.

 

Link this ‘account payable’ amount to the balance sheet.

 

Forecasting other items

 

For forecasting other items in the current liabilities section:

 

The most common way is: % of COGS

 

Can use historical data and project it into the future.

 

These items are usually not a substantial amount as compared to accounts payable.

 

Linking Working Capital to Balance Sheet

 

 

The highlighted region comes from the working capital schedule.

 

Linking Working Capital to Cash Flow

 

The highlighted region comes from the working capital schedule.

 

Illustrative Flow of Funds

Did you like this unit?

18 1

Units 6/14

Loading related modules...