Manufacturing Plant DCF Analysis - 10 Year Model
Latest Updates: monthly and annual three statement model added as well as a capitalization table.
Video Tutorial:
Starting and operating a manufacturing plant is going to require a significant initial investment and financial planning. If your company has decided they want to manufacture in-house, there are a lot of things to consider. The most complicated areas to model out are what values are put into the cost of goods sold as it relates to depreciation of machinery / equipment directly attributable to whatever it is you are manufacturing as well as what salaried employees are considered direct costs. Some are and some are not. The final part is actual material costs, but that won’t involve any complex accounting math or much difficulty compared to the other two items.
This model allows the user to define all of these things dynamically and accurately with proper logic. It will also display the average cost of goods sold on a monthly and annual basis for each type of product that is being made in the plant.
There can be up to 10 categories of products being made and the main attributes each item has includes:
start month
Expected Max Units Produced per Day
Operating Days per Year
Avg. Sale Price per Unit
Max Annual Labor Hours
Avg. Labor Rate
Avg. Labor Hours per Day per Unit Type
Max Required Laborers
Avg. Daily Hours Spent per Laborer
Max Working Days per Year
Avg. Labor Cost per Unit Produced
Up to 25 direct materials (and their costs)
One of the most innovative pieces of business logic in this template is the ability to define capacity attained over time. Each of the 10 product types is able to define the percentage of maximum capacity attained over the course of 10 years. This is to account for greater demand over time and general growth, but also to make the growth logical in the sense that there is a limit to how much volume a given plant can produce. This maximum output is defined in the initial assumptions above.
There are separate equipment schedules so that depreciation can be accounted for property, including an input for the percentage of each machine that can be allocated to each product type.
The final output summaries include a detailed monthly and annual pro forma that drives down to EBITDA, EBIT, EBT, and Net Earnings After Tax. There is also a high level annual executive summary and over 13 visualizations.
The user can account for potential financing sources, including outside investors, owners, and/or traditional debt. The resulting cash distributions are split accordingly, and a DCF Analysis is done for the project as a whole as well as for the investor and owner equity if applicable.
