Depletion with a Spreadsheet

The following figure illustrates how the depletion calculation works with declarative logic in a spreadsheet. Row 15 shows the formulas used to calculate values in each column.

Screen showing how the depletion calculation works with declarative logic in a spreadsheet.

The spreadsheet works this way:

Procedure

  1. Starting with the required amount of fish for the day (cell B6, which is referenced by cell D10), the model tries to satisfy the requirement using available fish, starting with the oldest fish.
  2. The oldest fish available is 3 days old. The quantity available is 10.25 Kgs (cell E13), which is less than the quantity required, so the model uses all 10.25 Kgs (cell F13).
  3. The model subtracts the quantity used from the quantity required and determines that 33.13 Kgs (cell H13) are still required.
  4. The model then moves to the 2 days old batch of fish. The quantity still required from the 3 days old batch becomes the current required quantity (cell D14)for the 2 days old batch.
  5. The quantity available from the 2 days old batch is 39 Kgs (cell E14), which is greater than the quantity required, so the model uses only the quantity required (cell F14).
  6. The model subtracts the quantity used from the quantity required and determines that 0 Kgs (cell H14) are still required.
  7. The depletion is complete. The model has successfully satisfied the requirements for 43.38 Kgs of trout on Jan. 10 by depleting inventory from the oldest available batches of fish.
  8. The quantity of 2 days old trout remaining after depletion (5.87 Kgs, cell G14) will become the available quantity of 3 days old trout on Jan. 11.