The Spreadsheet Perpetual Inventory Method: A simple and non-mathematical method to calculate stocks from flows using spreadsheets

By Tim Walshaw

What is the Spreadsheet Perpetual Inventory Method? It is a method to use a Spreadsheet to derive a stock value from a continuous list of flow variables. In the Spreadsheet Perpetual Inventory Method the flow inputs are totalled for the specific period of that cohort of flow data to provide the total stock in that period.
The Spreadsheet Perpetual Inventory Method not only provides more accurate and dependable results than the Perpetual Inventory Method using mathematical equations, but it is simpler to use. The results are more understandable to the average person. Also, you do not require years of study as an actuary or a demographer to learn and use complex formulae. All you need to understand is the relationship between flows, including depreciation and appreciation when they are used, and stocks at any point in time. The user just has to understand simple arithmetic. Indeed, after the spreadsheet has been set up, the user just needs to know just how to use the spreadsheet and how it works. Yet this spreadsheet produces more accurate results than using mathematical equations.
In this book the Spreadsheet Perpetual Inventory Method (SPIM) is described clearly and precisely, and this methodology can be used estimate the total stock or population at any time from the flows of the inputs and outputs. These flows can be continuously depreciated and appreciated at the same time, yet produce accurate totals.

• Language: English
• Publisher: Lulu.com
• Release date: Jun 20, 2022
• ISBN: 9781471661334

Book preview

THE SPREADSHEET PERPETUAL INVENTORY METHOD

A simple and non-mathematical method to calculate stocks from flows using spreadsheets

BY

TIM WALSHAW

Copyright © 2022 Tim Walshaw  All Rights Reserved.

All right reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, without the prior permission in writing of the publisher, nor be otherwise circulated in any form or binding or cover than that in which it is published and without a similar condition including this condition being imposed on the subsequent publisher.

Previous related publication by Tim Walshaw:

The Laffer Curve. Published 2021.. Double Entry Bookkeeping. Published 2017. Taxing Economic Rents Published 2016. Increasing Returns to Scale: A Simple Way to Make Good Investments, and Not Bad Investments, When Investing in Company Shares. Published 2014. Economic Rents, the Hidden Profit: How to Find Safe Companies to Invest In. Published 2015.

<2022>

INDEX

TABLE INDEX

INTRODUCTION

What is the Spreadsheet Perpetual Inventory Method?  It is a method to use a Spreadsheet to derive a stock value from a continuous list of flow variables. In the Spreadsheet Perpetual Inventory Method the flow inputs are totalled for the specific period of that cohort of flow data to provide the total stock in that period. 

The Spreadsheet Perpetual Inventory Method not only provides more accurate and dependable results than the Perpetual Inventory Method using mathematical equations, but it is simpler to use. The results are more understandable to the average person. Also, you do not require years of study as an actuary or a demographer to learn and use complex formulae. All you need to understand is the relationship between flows, including depreciation and appreciation when they are used, and stocks at any point in time. The user just has to understand simple arithmetic. Indeed, after the spreadsheet has been set up, the user just needs to know just how to use the spreadsheet and how it works. Yet this spreadsheet produces more accurate results than using mathematical equations.

In this book the Spreadsheet Perpetual Inventory Method (SPIM) is described clearly and precisely, and this methodology can be used estimate the total stock or population at any time from the flows of the inputs and outputs. These flows can be continuously depreciated and appreciated at the same time, yet produce accurate totals.

For the rest of this book the Spreadsheet Perpetual Inventory Method is described by the abbreviation SPIM to shorten the text.

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