EXERCISE: Working Capital in Financial Models

Example 1

Question

We have a company that sells product on different payment terms.

As shown in C20 to C24 our total turnover (shown in row 17) can be split into the various components. You need to:

1. Complete F20 to X23 to determine how much turnover is related to the applicable rows payment terms.
2. In the buckets below the sales, for the closing balance line (do this first) determine what you expect the closing balance to be using the days method i.e. for the 30 days bucket you have sales of R15 000 for the month, then you need to annualise the sales (R15 000 x 12 months), divide it by the number of days in a year (365) and multiply by the days in that bucket.
3. Once you know what the closing balance needs to be, you can work out what the payment must have been.

Example 2

Question

The company processes raw material into a product through 3 separate processes. Each process results in a loss of some raw material. We need to figure out how much will be available for sale.

You need to:

1. Note the raw material purchased in row 17
2. In row 29 note that we have linked the raw material purchased into the first bucket.
3. In row 30 you need to calculate the stock losses for this first process (note that we have pulled the loss % through in B30). For this example the losses are only applicable to the new material, not the opening balance.
4. In row 32, you need to be left with a closing balance based on cell B32.
5. Now go back to row 31 and determine how much can move to the next process after taking into account the losses and required closing balance.
6. Now go to process 2 and repeat the steps. Note that in row 38, all you need to do is link to the previous process’s transfers (row 31).
7. Do this for process 2 and 3.

Example 3

Question

Same company as above except now we need to work backwards. We know how much we need to sell, and want to determine how much raw material we should buy each month. In this case you need to work backwards.

1. Note that the required sales are in row 64
2. In row 50 put in the required stock level of process 3
3. In row 49 link to the sales amount.
4. In row 47 (ignore row 48 for now), determine how much will need to come from the previous process (taking the loss % in B48) into account using a similar calculations to that to determine the VAT component in a VAT inclusive price i.e. VAT Excl= VAT Inclusive / 1.14
5. You can now complete row 48.
6. Going up to process 2 do the same thing.

To see the full course contents click here.