A business's financial health depends heavily on its short-term assets, such as inventory and Accounts Receivable (A/R), along with short-term liabilities, such as Accounts Payable (A/P). If these elements are managed well, then the business will have the cash to invest in finding potential customers, developing new products, and hiring new talent. We refer to these three pivotal financial measurements as working capital.
We can find inventory, A/R, and A/P, as separate line items in the balance sheet that we created for our financial perspective in a previous chapter. However, there is also a series of additional analyses that all three have in common. For example, the analysis of the average number of days that a product is in inventory, a customer takes to pay an invoice, or the business takes to pay a vendor invoice requires the same type of data model and formulation. We can also make this information more actionable if we include it in a product, customer, or vendor stratification. As an example, we will complement the customer stratification that we began to create in our sales perspective.
After we examine what each has in common, we also look at the distinct operational analysis of each measurement that helps us maintain a healthy working capital. For example, a customer aging report can help lower the average number of days a customer takes to pay an invoice. Inventory stock level analysis can also help procurement know when to purchase the correct amount of inventory and lower just the right amount of a product.
In this chapter, we review the following topics while we create our QlikView working capital perspective:
- Working capital data model (snapshots)
- Account rotation and cash-to-cash analysis
- A detailed analysis of working capital
- Inventory stock level analysis and customer aging report
- A more complete customer stratification
Let's get started and look at how we combine these three elements of working capital into one data model.
The working capital data model can be constructed in a variety of ways. The most important feature of the data model is its ability to accumulate account balances over time. In Chapter 3, Financial Perspective, we accomplish this by adding an as-of calendar. However, we can also create a model that uses periodic snapshots and avoid accumulating individual transactions after every user selection. A periodic snapshot is a recurring event that saves a copy of the data at the end of every day, week, or month.
In this chapter, we will use a periodic snapshot to measure following events in the data model:
- Month-end inventory balances by item and warehouse over three years
- Day-end inventory balances by item and warehouse over the last the last three months
- Month-end balances of A/R invoices over the last three years
- Month-end balances of A/P invoices over the last three years
Periodic snapshots do not record individual payments or inventory movements, which may be important for some banking or operational analysis. However, such details are not important when we first analyze working capital.
If we've only recently started to create data snapshots, some of the analysis we perform will be deficient as many metrics are calculated over 90-day periods. However, we sometimes have the option to recreate past snapshots using transaction-level data. Even if they are not completely accurate, they are often worth the effort. The decision on whether we wait until we have enough real snapshots or to recreate past snapshots frequently depends on which option takes less time. It also depends on whether the opportunity gained by having them now is greater than the resources spent to recreate the past.
Many of the dimensions that we use to describe these events are the same dimensions that we've used in previous perspectives. We reuse the same tables so that it is easier to maintain the data models and to ensure that everybody in the organization is viewing the same information. Let's take a look at the dimensions and metrics that describe these events:
|
Dimensions | ||
|---|---|---|
|
7Ws |
Fields |
Comments |
|
Who |
|
This is a dimension that we first saw in Chapter 2, Sales Perspective. |
|
Who |
|
This is who provides products or services to the business. This dimension has similar information to that of the |
|
What |
|
This is a dimension that we first saw in Chapter 2, Sales Perspective. |
|
When |
|
These are the same dimensions that we've seen in the previous perspectives. However, instead of recording, for example, the date of an invoice or a payment, it records the date when a snapshot was taken of a customer's outstanding balance. |
|
How |
|
This dimension allows periodic snapshots with different frequencies to be loaded into one data model. For example, we load daily inventory snapshots of the past few months and monthly ones of the past few years. We do this so as to only upload the data that is useful. Otherwise, we risk degrading the QlikView application's performance. |
|
Where |
|
This dimension describes where we store goods so that they can easily be distributed to the customers who purchase them. We measure inventory levels by Warehouse. |
|
Metrics | ||
|
7Ws |
Fields |
Comments |
|
How Many |
|
This is where we store a predefined time that is needed to receive an item in inventory, which helps procurement know when to purchase or produce a product. |
|
How Many |
|
This is where we measure the outstanding balance of each customer invoice. The outstanding balance is the original invoice amount minus any corresponding payment or credit memo amount. In the ERP system, we link invoices with their related payments and credit memos through a bookkeeping process called reconciliation. |
|
How Many |
|
This is the same concept as |
|
How Many |
|
This is where we measure both the quantity and monetary value of the business's inventory. |
While the calendar dimension is related to every event, every other dimension describes only one event. For example, supplier only describes month-end A/P invoices. It is helpful to understand the relationship between dimensions and metrics in a data model in order to know what type of analysis we can perform. However, we cannot obtain this information explicitly from the QlikView table viewer nor the previous 7Ws table.
Therefore, We use the following table to explain the relationship between metrics and dimensions in a data model. We insert all the metrics in the first column and then create a column for each dimension. The
X records where a relationship exists and helps us determine how we can visualize the data:
|
Dimensions Metrics |
Month/Year |
Date |
Customer |
Supplier |
Item |
Warehouse |
|---|---|---|---|---|---|---|
|
A/R Invoice Balance |
X |
X | ||||
|
A/P Invoice Balance |
X |
X | ||||
|
Inventory Balance Quantity |
X Past three years |
X Past three months |
X |
X |
We maintain the relationship as it is likely to exist in the ERP system. For example, payments do not include information about items. This is not always good enough for the visualizations that we want to create. Even though payments don't include item detail, we may want to know the estimated average number of days that a customer pays for a certain item. We examine how to resolve this problem as we develop the analysis and visualizations for the working capital perspective.