Manufacturing Plant Financial Model - Up to 10 Years

I was going to do a financial model that was just about making 3D printers, but then I realized the assumptions and logic required would actually fit any manufacturing business. So, this model is actually a general use template for the manufacturing of any kind of widget (3D printers, valves, or what have you).



Pricing
Specs:
  • Goes for a period of up to 10 years.
  • Includes logic for depreciation / direct labor / raw materials / plant/building / capacity rate.
  • Monthly and annual P&L detail.
  • Up to 10 different widget types to build up from.
  • Dynamic equipment schedule with assignments for each widget type that uses a given equipment type.
  • Options for bank and investor funding.
  • Cash distribution schedule for investors and owner/operators.
  • IRR and DCF analysis for project and funding entities.
  • Plenty of visuals.
  • An executive summary roll-up that focuses on the key financial items.
This model really gets in-depth. For example, on direct labor, the user will choose the maximum individuals that will be working on a given widget type, the max hours they will spend per day, and their avg. hourly rate. For unit production, the user picks the max units produced per day and max working days per year. Both of these inputs are 'maximum' for a reason. This is because you have a schedule called 'capacity' where the user can define the % of this maximum that is reached in each of the 10 years. The user will also define the avg. sales price per widget type (you will be able to see the resulting avg. cost per unit per widget type over time as well).

I knew a manufacturing financial model was going to be difficult. The main reason why so many granular assumptions are required is that to truly know what your cost per unit is as well as gross margins, you have to know how much of the depreciation / raw materials / direct labor go into each widget type. It is not easy to build properly.

This is one of the only business models where you have depreciation in as a cost of goods sold. This is done because if you use a piece of equipment in order to produce the widget that you are selling, then part of its 'cost' is the equipment. Since the equipment has a depreciation schedule, you can only take a certain amount of that cost and apply it to the widgets produced during that time.

Some of the depreciation is able to be allocated to the widgets it is used for and there are also a lot of extra slots to account for on-going depreciation for things that get split evenly between all widgets in production (like the factor/plant or storage rent/utilities/etc...).

The model drills all the way down to earnings after taxes and cash flow after taxes. The exit multiple is based on EBIT rather than EBITDA because of the way these types of businesses are valued. 

In general, this template is really good at giving the user enough scale for strategic planning and enough granularity to account for a wide array of scenarios but is high level enough to be used in a large majority of manufacturing use cases.

There is also logic for payroll taxes and benefits that happen on top of the defined direct labor costs. This falls into a spot within the cost of goods sold section of the P&L detail.