THE SPREADSHEET PERPETUAL INVENTORY METHOD: A simple and non-mathematical method to calculate stocks from flows using spreadsheets by Tim Walshaw

CHAPTER ONE
 THE BASIC PROBLEM
 What are stocks and flows?
 Stocks are an aggregation of goods or objects, or financial quantities. Stocks can also be populations, that
 are aggregations of people or other creatures. At any point of time these stocks are a fixed amount.
 However, stocks do not just appear. They have to be created by a flow of objects in order to add to the size of these
 stocks. Also, there could be a flow of objects out of these stocks. These inward flows are often called additions,
 births, or net migrations. These outward flows are often called depreciation or in the case of populations,
 mortalities, net emigrations. The population is a balance of inflows and outflows over time.
 All Perpetual Inventory Methods relate these stocks and flows. Many simple Perpetual Inventory Methods simply
 add and subtract flows from stocks in order to get a final answer. These additions and subtractions could include
 depreciation and appreciations, as well in the case of populations, births and mortalities. As will be seen later in
 this book, these methods are primitive, and carry with them basic errors that steadily cumulate so as to throw the
 value of the final stock figure into significant error.
 More complex methods use mathematical formulae. Some of these formulae are tied to the Perpetual Inventory
 Method. Others are related to Actuarial methods. Others to Demography. Others to Ecological estimations. All
 these mathematical methods contain methods in common. But they all suffer from inaccuracies – mainly due to the
 use of simplifying assumptions, averaging, and various mathematical short cuts.
 The Spreadsheet Perpetual Inventory Method (SPIM) described in the book is more sophisticated, and closer to
 reality. One of the most frequent characteristics of flows is that the value of the flow changes over time. The value
 of the flow can increase or decrease, not necessarily at a constant rate, or its trend can even move up and then
 down. Standard mathematical formulae cannot handle changing movements in the value of the cohort’s flows well.
 The total stock value is the sum of the individual flow values at any point in time. As will be shown in this book,
 SPIM can handle a complex set of flows simply and easily, and derive an accurate stock value at any point in time

CHAPTER TWO
 THE STRUCTURE OF THIS BOOK
 The fundamental question is how do you estimate the value of stocks from flows?
 Hitherto there have been many separate methodologies have been used. To date these include:
 The basic mathematical methodology of the standard mathematical equation Perpetual Inventory Methods.
 Accounting methodologies.
 Actuarial methodologies.
 Demographic Methodologies
 Ecological Methodologies
 While all these methodologies basically have the same overall aim - the estimation of stocks or populations from
 flows - often completely different techniques have grown up.
 The SPIM, as will be described, is a single simple methodology that combines all the different methodologies
 mentioned above in a single simple form. There is no need to use complex mathematical models. Indeed, the use
 of the SPIM is preferable because it produces better results that are more accurate and detailed. 
I shall describe the Spreadsheet PIM first. Once this methodology was understood, I go on to provide a SPIM
 alternative to each of the methodologies mentioned above.

THE SPREADSHEET PERPETUAL INVENTORY METHOD: A simple and non-mathematical method to calculate stocks from flows using spreadsheets by Tim Walshaw