This financial model has a robust set of assumptions that are designed to meet the needs of made to order manufacturing businesses who need to perform financial forecasting. The model drives off counts of units ordered and cash collections between the order month and shipment month. I made the assumptions robust enough that it could really be used for any manufacturing business. You can shift customer payments to be received after the shipment month by simply changing the month number in that schedule and assume the 'units ordered' count is the 'produced units' count.
$125.00 USD
After purchase, the template will be immediately available to download. This is also included in the industry-specific financial models bundle and the manufacturing financial models bundle.
Template Features:
- Forecast for up to 10 years.
- Select accrual or cash basis accounting and all logic updates accordingly.
- Includes 3-statement model (Income Statement, Balance Sheet, Cash Flow Statement) - Monthly/Annual
- Dynamically define total units ordered per month for up to 10 product categories.
- Dynamically define the percentage of cash collected at 'n' month from the order month (up to 5 collection periods)
- Define the average months it takes for an order to ship relative to the month ordered.
- Dynamically define the percentage of cash paid for cost of materials at 'n' month from order month.
- Dynamically define the number of months materials are purchased for at a time.
- Define the average cost of materials per unit by category type with 20+ potential raw material items.
- Balance sheet includes dynamic accounts receivable, unearned revenue, accounts payable, and inventory line items that all update as the assumptions are input.
- Monthly / Annual monthly detail that drives down to cash flow.
- Executive Summary that gives an annual summary of all key financial line items.
- DCF Analysis and IRR
- Options for inside and outside investor equity as well as debt financing.
- Option for exit value and this is based on a % of trailing 12-month revenue per the defined exit month.
- Lots of visualizations to digest the financial forecasting assumptions more easily.
The main thing to understand with made to order manufacturing is that sales and cost of goods sold are not recognized until the order has shipped. Just receiving an order doesn't result in any double-entry accounting event. However, receiving cash for an order that has not been shipped yet does have an effect, and that is an increase to liabilities in the form of unearned revenue. This model can automatically calculate if a cash payment received from customers happens before or after the shipment date and based on that, the cash will either increase a liability or decrease an asset (accounts receivable). If a payment is not received until months after the shipment date, this automatically creates accounts receivable and that is reduced as payments are collected on or after the shipment month.
I had to do some fancy financial modeling techniques to make sure the accounts receivable, unearned revenue, and cash all were being calculated correctly depending on when the units were shipping relative to when money was being received from customers (per the editable assumptions). Overall, it turned out awesome and works like a charm.
There is quite a bit of accounting logic going on in this spreadsheet, but the assumptions are high level enough that anyone in the manufacturing business should not have a problem entering the information and understanding the resulting forecast being produced.
You can have an arbitrarily small or large volume of units being produced. Simply enter the unit counts by month and by categories on the 'sales' tab.
Note, if you are using this for a manufacturing business that produces units based on expected demand rather than made to order, that is fine. You will just assume 'units orders' is actually the 'units produced' and then the 'month units ship relative to order month' is the 'month units are sold relative to unit production month'. And you can define when cash is collected from customers in the same way. For example, if you sell units 2 months after they are produced, you put a '2' in the box for when units ship and then you would likely put payments received in month 2 or greater depending on the exact terms for your customers. The model treats 'units produced' or 'units ordered' in the same way financially.
The template comes in a blank version and a version that has all the assumptions filled out. This should make it easier to understand how to fill out your own assumptions.
Questions and Answers From Customers Who Purchased This Template:
Customer Question:
The model I got seems to be based on a startup manufacturing company. Our company has been in business almost 10 years and we are trying to get financing from a bank for a new investment and they are looking for forecasts. I was under the assumption that this model would project for 10 years. will this work for a company that is already operational. If not, do you have a model that might work better? Thank you for your help.
Answer:
Yes, the model works for an existing company just the same as a brand new startup. Fill in the assumptions accordingly and a revenue, expense, and cash flow forecast will populate for up to 10 years worth of time. You can account for a new loan as well by entering the loan amount and terms. The 'Equipment' tab can be used to enter new one-time investments and it doesn't just have to be equipment. You could also use the startup costs section for one-time costs related to the new investment if there are non-depreciable items.
More Industry-specific Financial Models:
