Simple Cost of Goods Simulation Spreadsheet
Originally published: 18/03/2022 09:13
Last version published: 29/03/2022 08:46
Publication number: ELQ-14402-2
View all versions & Certificate

Simple Cost of Goods Simulation Spreadsheet

This is a simple simulation for determining the Cost of Goods (COGS) that can be customized

The COGS is usually a difficult aspect of financial modelling that can rely on guess work such as a percentage of revenue. However, if one could use a simulation to estimate costs, a more reliable picture emerges of the COGS, not necessarily perfect but at least not a thumb suck. It forces one to find prices of items and to look for suitable suppliers. This model is customizable as indicated and more "components" etc. can be added although this may make the model a bit unwieldy. It is suggested to rather use one spreadsheet per product and have a central spreadsheet that adds up all the COGS. In addition, an estimated mean and standard deviation is created that can be used in more advanced Monte-Carlo models for example. An estimate of the mean and standard deviation can be used to generate future costs of specific products when using advanced models in financial modelling. Typically, the data can be used to construct several layers of product costs per month that add up and gives and estimate per year. The user can correlate the simulated costs and profits and adjust the simulation to more accurately represent the reality for a specific type of product.

This Best Practice includes
1 Excel spreadsheet - Macro enabled

Deon de Wet-Roos offers you this Best Practice for free!

download for free

Add to bookmarks


Further information

To create a simulated cost of goods for a product

This simulation can be used for most instances where a simple simulation is required to get a basic idea of the COGS of products.

The simulation provides an estimate of product costs. It does not provide an exact value.

5.0 / 5 (1 votes)

please wait...