The model used to produce Synogy’s Financial Proforma Program is a proprietary Excel program developed by Hank Tate (firstname.lastname@example.org). It is designed to present dynamic financial planning information in a logical fashion with the ability to quickly see the background assumptions used to produce every line-item value shown and to readily model financial scenarios by changing the background assumptions.
Philosophy of Design for Synogy Financial Proforma Program
Screen captures demonstrating examples of some of the following are included in Section III.
A business goal cannot be reached unless adequate funds are available to cover all costs of reaching the goal.
Failure to plan for all costs is planning to fail.
Accurate cost projections cannot be determined by guessing.
Every line-item expense represents a collection of smaller component expenses necessary to accomplish the purpose of that line-item.
Accuracy in financial projections is enhanced by breaking each line-items cost down into its component expenses.
Each line-item breakdown needs to be clearly marked to facilitate lookup of component costs.
Financial projections only have value when calculated from actual and realistic costs for each component required to produce a line-item expense.
Example: The expense for an employee is not just their salary and taxes. To project a realistic cost for each employee, ALL expenses of that employee must be assessed, including:
Cost of employee setup (expenses for desk, chair, phone, computer, software, etc.)
Equipment required (every lab technician may require $40k of lab equipment)
Travel: Car, air travel, per diems for days out of office
All assumptions for component expenses must be available for quick review and for ease in changing underlying assumptions to provide the ability to perform “what-if” financial modeling.
Line-item costs must be determined from real-life assessment of what must be done to accomplish the task – NOT defined by general percentages of other projected numbers.
Example: Cost of sales or marketing CANNOT be projected by a percentage of projected revenues. To try and do so would produce the fanciful result that there would be no marketing expenses in the early days of the company before sales had commenced. Instead actual costs must be determined by assessing the methods to be used and actually calculating all of the component costs for those methods.
What marketing materials will be used?
Success rate of materials – how many necessary to bring in each customer?
Cost of each piece of collateral material?
How will they be distributed? Cost of distribution?
How will they be followed up? Cost of follow-up?
Method for closing sales? Cost of closing a sale?
Assumptions for cost will change over time, and all costs do not change at the same rate. Some expenses grow over time, some will decrease with volume as the company grows. There needs to be a way to adjust each baseline assumption over the life of the projections.
Salaries typically go up each year – how is the salary increased automatically for each year of the projections
The per-item cost of collateral materials or parts will go down as the size of orders increase.
Staffing requirements should be calculated based on growth of the company. Greater accuracy will result if support staff size can be correlated to the number of customers, and designed so that the timing of hiring is adjusted automatically for each “what-if” model.
Example: Instead of manually guessing a time for hiring additional customer support staff, the projections should take an assumption for how many customers each customer support staff can handle and then automatically increase or decrease the staff based on the sales growth assumptions.
Physical plant and office requirements must be adjusted according to growth of staff. Accuracy of the projections will improve if the facilities size is recalculated based on the number of employees in the company each year. The components of facilities required should be calculated – not guessed:
Number of staff
Square feet of office required for each staff
Common areas necessary
Facilities clean-up & maintenance
Any special equipment or design issues covered
Capital Expenses need to be separated from general expenses
Cash flow must be analyzed monthly to determine how much money must be provided to complete the project. Since marketing, sales, and product development will precede sales the startup expenses will produce a financial “hole” before revenues begin. The amount of capital required is based on the “hole” during the year, not on the financial status at the end of a year. It is impossible to assess actual cash requirements based on annual net profit or loss.
The trustworthiness of financial projections will be reflected by how effectively a company shows the details of its analysis to break all line-items into component costs.
Perception of the professionalism of a company’s management will be enhanced by showing the details of its financial analysis.
Building a strong detailed financial projection is one of the most effective tools for helping management think through the details of HOW the company is going to do business so the company can PLAN effectively the process of turning its vision into reality.
Design of SYNOGY financial projections
The following sections of information are included in Synogy’s complete financial package:
Explanation of Synogy Financial Proforma Program
Explains the design and philosophy of the proforma.
A detailed explanation of the assumptions used for creating the financial projections.
5-year Annual Report
This report shows only the annual totals for each of the five years. All monthly columns are hidden to enable annual totals to be shown in a short report. The categories and layout is the same as the Annual Summary Report.
Collects the financial details of Synogy’s projections into an summary report showing each month for each year.
This report presents all of the detailed calculations from which the Annual Summary Report and the 5-Year Summary Report are derived. All financial projections are broken down into component assumptions and calculations. All assumptions are shown at the beginning of each section of calculations. Most projections are dynamically based upon financial calculations driven by the assumptions rather than being directly entered, and are designed so that the entire package is recalculated when any assumption is changed.
The Synogy Financial Proforma Program is based on a proprietary spreadsheet that enables dynamic financial proformas to be built very quickly while showing all of the component expense breakdowns for each line-item. The design of the spreadsheet enables multiple functional areas to be created very quickly by duplicating an existing functional area and changing a few variables.
Since the spreadsheet is proprietary and complex the spreadsheet itself is not distributed, but the PDF printouts reflect the following structure of the financial proforma spreadsheet.
The top section of the spreadsheet collects all appropriate information and financials into a Summary Report, by month, for each year. The 5-year Annual Report presents the same information with only the annual totals. The Summary Reports include the following sections:
Growth Projections - describes the appropriate growth statistics which drive the Revenue projections
Revenues - describes the various Revenue sources of the company
Cost of Goods
Equity Placement Fees
Marketing & Sales Expenses
Net Income (Loss)
Sources & Uses of Cash
Capital Expenditures by Functional Area, including:
Furnishings & Equipment
Total Investment Required
Net Present Value (calculated at 10% interest)
NPV on Net Income
NPV on Cash
The bottom section of the spreadsheet presents a Detail Report of all the assumptions and calculations which drive the Summary Reports. Each section of calculations starts with a statement of the key assumptions used to drive the financial calculations. All employee calculations reference a common collection of assumptions which is presented after the sections calculating growth and income, and prior to the sections calculating expenses.
Each division of the company provides for four to eight levels of staff. Each staff level is independently calculated for salary, benefits, overhead, travel, conventions, recruiting expenses, and bonuses. Staff growth for each employee level is calculated independently. Each staff expenses section has the following calculations:
Staff Expense Calculations
New Staff Calculations
Total Cumulative Staff
Payroll & Benefits
Travel, Conference, & Entertainment Expenses
Recruiting & Bonus
Billable Adjustment (for billable employees
Adjustments to each year’s assumptions are made in columns separating each year’s Detail Report Calculations.