Financial Model, Manufacturing

10-year Financial Projections, 5-year Financial Projections, Accounting, Accounts Payable, Accounts Receivable, Average Order Value (AOV), Budget, Budgeting, Business Valuation, Cap Table, Cash Flow Analysis, Cash Flow Projections, Cash-on-Cash Multiple, CFO, DCF Model, Debt Schedule, Debt Service Coverage, Discount Rate, Excel, Financial Analysis, Financial Feasibility, Financial Model, Financial Planning, Financial Projections, Financial Statements, Forecast, Forecasting, Income Statement, Inventory, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), NPV (Net Present Value), Pricing, Private Equity, Pro-Forma, Profitability Analysis, Return on Invested Capital (ROIC), Return on Investment (ROI), Revenue Projections, Scenario Analysis, Startup Financial Models, Terminal Value, Three Statement Model | Three Financial Statements, Venture Capital

Video Overview:

This template was built for anyone in the manufacturing industry. Specifically, the basis was for a made-to-order (build-to-order) manufacturing business, but I have made the financial model so that it is completely useful for volume businesses and regular manufacturing as well. There is a switch to make the model use accrual-basis or cash-basis accounting. You can simply select one or the other with a drop-down. All formulas / financial statements / reports update accordingly. This will affect things like revenue recognition and if you have AR / AP / Inventory / Unearned revenue or not.

Here is the flow of logic:

The model begins with an order log for the user to fill out. It runs for a maximum of 120 months, and there are up to 10 product categories (they could represent a basket of products for each type or individual SKUs depending on what is being manufactured).

Based on the volume of orders entered, all other logic is derived. This includes when cash is collected from the customer (up to 5 payment periods, simply enter the % collected ‘n’ months from the order month). Additionally, you can define the month the shipment happens relative to the order month. It could be 6 months, 12 months, or what have you. That makes this useful for building large-scale items that take a long time to ship and have various payment terms for the customer. It would also work for shipments that happen every month, as the input can be any positive number.

As a result of the configuration of the above assumptions, you could have unearned revenue (cash collected before the order is shipped) as well as accounts receivable (cash collected after the order is shipped), and you could even have a combination of the two depending on how you structure customer payment terms. This all shows up on the balance sheet, cash flow statement, and income statement. Getting the logic correct for these flows was one of the most difficult pieces of modeling I’ve ever had to do, but it turned out to be a masterpiece.

In order to account for possible terms on the purchase of materials, I put in a separate schedule where the user can define the % of purchased paid ‘n’ months from the purchase month of materials. The model assumes purchases happen at the date the order has been received and works forwards from there. This will result in accounts payable and cost of goods sold line items as well as inventory purchases on the statement of cash flows.

There are also inputs for equipment / capital expenditures, debt financing, equity financing, a robust operating costs schedule, and input for ‘other income’ if applicable.

Example manufacturing business this would fit: You build airplanes and receive an order every few months. The average airplane takes 14 months to build from start to finish. When the order is received, the customer pays 50% of the price upfront (in the order month) and 50% at the time of delivery (month 14). At the point of order, you have unearned revenue that doesn’t hit the income statement but does increase cash. In month 14, the entirety of the sales is recognized as revenue, and the unearned revenue account zeroes out.

Looking back at the order month, you also must buy all the required materials for the airplane in that month. You may have terms on those purchases and say 50% is paid upfront and 50% is paid 2 months later or what have you. That can all be configured with a few inputs. This will decrease cash and increase accounts payable at the order month and then reduce payables 2 months later when the rest is paid off.

The model also has a configuration for the months in advance inventory is purchased for. You can put a ‘4’ for example and that means you will always have purchased enough inventory to last the next 4 months. When the inventory purchases are made, inventory goes up by the full purchase amount (no matter what the payment terms are), and then when the airplane is shipped, inventory goes down by the amount of cost of goods sold (based on cost per unit / units sold).

During the work in process time, you spend labor, rent for the factory (or depreciation if you own the plant), rent for equipment (or depreciation of equipment), utilities, money for materials, other wages, and so on. All the indirect costs to run the facility are considered costs to the manufacturer, and if there is depreciation, that is a non-cash item that hits before the contribution margin. This all must flow correctly and not be double-counted.

You may also have other operating costs for the business that are not directly related to producing the goods. This could be the CEO’s salary, the accounting staff’s costs, the director of marketing, and other overheads. This model has a robust section to enter the expected monthly cost of overhead items and adjust them on a yearly basis as well as indirect overheads related to manufacturing. They flow to the correct areas of the financial statements accordingly.

Equipment purchases and building construction or purchase can be defined on their own schedule, and dynamic depreciation expenses auto-populate based on the date of entry, useful life, and date in service / purchase month. The depreciation allocation is even broken down for each of the product categories so you can define the % of each piece of equipment that is allocated to each product category. For example, you may use one machine for making product type 1 and product type 2 evenly, and therefore, 50% goes to type 1, and 50% goes to type 2 for that piece of equipment.

I’ve also included the ability to plan for an exit if needed at the end of the forecasting period. If you want to show the cash flow effect of that, simply flip the ‘include terminal value’ switch to ‘Yes’ and then enter a percentage of trailing 12-month revenue that you are valuing the business at. This is defined on the ‘Global Control’ tab.
You can also plan for financing any startup costs, capex, and/or operating burn with an initial loan as well as equity. Those inputs are defined on the ‘cap table’ and debt amount on the ‘Global Control’ tab.

Outputs include monthly and annual financial statements, an annual Executive Summary, a DCF Analysis, and plenty of visualizations and manufacturing KPIs.

Note, the file comes with all the assumptions cleared out, so it is ready for you to enter your own data into. I also included a version with data in it so you can see how an example scenario could work.