|
Financial Modeling Specialists Anything and Everything Excel |
|||
|
Recent Engagements
Financial Projection Model: For a startup in the internet-based subscription-customer business space, we built a financial projection model employing a sophisticated growth planning methodology that allows the user to specify a set of targets for periodic growth in customer counts, and the planning periods during which the respective growth targets apply. Customer counts – and revenue – are automatically grown according to the growth targets. The model also comprehends a sophisticated customer attrition methodology. The user can specify a set of attrition rates, and the periods during which each attrition rate applies. The model automatically determines the cumulative customer attrition, and the net active customer count in each planning period and calculates subscription revenue accordingly. Fully parameter driven, this methodology is especially valuable for startups in early stage planning when financial plans are in high flux and subject to frequent revisions. As with all of our models, print-ready income statements, balance sheets, and cash flow statements are incorporated.
Financial Projection Model: For a web-based internet start-up we developed a financial projection model using our proprietary methodology that allows the user to set -- and change at will -- both growth targets, in this case unique website visits, and the planning period those targets are expected to be met, and automatically grows the visits over the intervening time periods. The model included multiple revenue streams and projected financial performance metrics including IRR for the business as an enterprise.
Merger & Acquisition Analysis Model: For a consultant in the field of mergers & acquisitions, we built a fully customized Acquisition Analysis Model for use in structuring and evaluating potential deals. The model included a highly creative approach to modeling annual growth which allows the user to specify a year-on-year growth profile that satisfies a compound annual growth [CAGR] target for the specified multi-year period.
The model also includes a sophisticated approach to determining a maximum earn out payment constrained to achieve a pre-determined Weighted Average Cost of Capital [discount rate], and/or a price cap, and business valuation analyses for multiple growth scenarios.
Results for various CAGR Cases are summarized in a concise table for ease of comparison and decision making.
Commission Analysis Model: For a provider of medical imaging services we developed a custom Commission Analysis Model that provides reports of commissions payable to the company’s representatives based on the number of imaging scans referred by physicians. The model analyzes scan count data by referring physician and scan type, determines the representative to credit for the scan, and calculates commissions using four-tiered tables that set commissions based on goals for each representative. User inputs include tier bounds, which are based on total scan count objectives for each representative, and commission paid per scan for each tier of scan counts.
Incorporated in the model are automatic sub-analyses that dice the data in multiple ways, e.g., by representative and then scan type, scan type then commission tier, and total commissions by tier by scan type.
A later enhancement provides the option for multiple commission tables, each of which can be set to become effective as of a user-defined date. The previous tables continue to be applied for historic data.
Job Bidding / Costing Model: For a landscaping contractor we developed major enhancements to their job bidding and costing model. Sales representatives use this model to price out jobs and determine commissions, and provide information to the production department. The enhanced model automatically shows the reps lists of products and services in 18 different categories.
The model uses Visual Basic for Applications code to display additional reports used by office staff and the construction department.
The model is extremely user friendly and has resulted in significant improvements in productivity for both the sales representatives and office staff.
Process Automation ~ Visual Basic for Applications® Programming: For a registered investment advisory firm we automated their process for analyzing client asset holdings prior to recommending portfolio re-balancing.
Previously a 100% manual process, a client’s portfolio was extracted from a database, manually copied, pasted and re-formatted in an analysis file, then manually summarized by asset class. This required at least three quarters of an hour per client, was highly error-prone, and resulted in non-uniform reporting formats owing to the manual nature of the process.
We wrote Microsoft® Visual Basic for Applications® code that extracts portfolio balance sheet data from the database output file, reorganizes and re-formats the balance sheet in a uniform, client-oriented manner that can subsequently be copied directly to a client report, and automatically summarizes the portfolio by asset class. Re-balancing recommendations are then made for review with the client.
The automated process executes in less than five seconds, even for large portfolios. Further, numerous built-in quality control checks assure all assets have been captured and summarized correctly, and agree with the data source.
Additional automated features include capturing and displaying portfolio performance comparison information in tabular and graphical formats.
Scheduling Tool ~ Visual Basic for Applications® Programming: For a religious organization we wrote a program in Microsoft® Visual Basic for Applications® that schedules participants in upcoming services, a task previously done manually and requiring up to eight hours for a six-month schedule.
The new tool schedules the roster of available participants for one of six roles at each of two services every Sunday. Each person on the Roster can specify a preference for either or both services, and any limitations of roles they can perform. Individuals who cannot be scheduled for a particular service are added to a Reserve List, and given priority to be scheduled at the next opportunity.
The tool also includes an Availability Table, in which individuals may specify Sundays they will not be available.
Market Sizing Model: For a growth strategy consultant we custom-built a market sizing model that leveraged a range of sources of information -- from U.S. Census Bureau population projections to clinical studies data to primary research results.. The model grouped annual populations by age and gender, then into further client-specified sub-categories. Criteria for apportioning sub-category populations were fully parameter driven and changeable by the user. Key benefits of this model for our client were its usability, clarity, robustness, and the application of several sophisticated arithmetic techniques to manipulate parameters to suit available research results and known conditions. We also created a variety of graphical outputs of the model’s analytical results for use in reports and presentations to the end user.
Planning Tool: For a web-based provider of payment processing services to the property management industry we worked directly with the CEO to create a customized and highly sophisticated financial projection tool.. The model ran Visual Basic for Applications code to aggregates current-customer information from their custom built information management system, prospective customer data from SalesForce.com®, and accounting data from QuickBooks®, and projects transaction revenue by product. The results will be used for internal and external reporting purposes.
Consulting Operations Planning Model: Financial Modeling Specialists custom designed and built a highly complex financial projection model for a startup internet consulting firm’s incorporation in its business plan, and for detailed operations planning. Using over 300 user-variable parameters, this model projects five year Income Statements, Balance Sheets, and Cash Flow Statements. Among the more sophisticated elements of this model are:
Other features of this model include: Backlog Analysis; Headcount Analysis; User-variable executive, professional, sales, and administrative staff bonuses.
Planning Model: For an internet-based provider of language tutoring service we built a 100% customized planning model that was key in securing vital investor funding for expansion of the business The principals of the company had developed a proven one-on-one language tutoring approach utilizing the internet, and wanted to expand from their initial location and language. We created a fully parameter-driven financial projection tool based on their business and operations model that facilitated what-if scenario evaluation and analysis. The model produces consolidated projected income statements, balance sheets and cash flow statements for their initial location and two expansion sites, as well as the headquarters site. Additional expansion locations can be easily added.
Business Plan for New Restaurant: FMS wrote the narrative and developed the supporting financial projections which enabled a restaurateur to secure over $300K in funding to build-out and completely equip a new restaurant.. Based on input from our client we created a customized financial projection model to accurately mirror his vision and plans for his establishment. We also published the business plan document. Our client intends to continue using the financial model to augment planning as the business grows. The restaurant, serving continental style cuisine with an American influence, seats 90, and features live music on Friday and Saturday evenings
Real Estate Loan Proposal: For a real estate property management entrepreneur we prepared several complete loan proposal packages for submission to potential lenders, and developed a financial projection model to support the loan requests. The deliverables comprised a bound proposal document including an introduction, executive summary describing the lending opportunity, background information on the principals, and other supporting information, for example, analysis of occupancy rates for similar properties in the market area, and photographs of the property. The financial projection model is 100% parameter driven and thus easily updatable for last minute changes and new projects.
Planning Model of the Firm: Working closely with the president/CEO of a network software developer, we are creating a comprehensive five-year planning and forecasting model of the firm. This model encompasses seven market segments and multiple types of services within each segment. Phase I is a forecast of revenue, variable expenses, and direct capital spending as the market is penetrated. Phase II will fold in capital expenditures for shared assets, operating expenses, overheads, and G&A and financial expenses, all modeled to support the growth of the business. The ultimate product will be a net income forecast. One hundred percent parameter-driven, this model allows our client to test the effect of an interactive set of assumptions and immediately see the impact on the bottom line.
Real Estate Project Cash Flow Model: We created a customized, parameter-driven project cash flow model for a real estate development project business plan. This model integrates the construction and sale of residential living units, common and recreational facilities, retail space, a school building, and required infrastructure in five phases, projecting cash flow as the project progresses. The model calculates ROI for the equity investors based on the timing of their investment and pay back. Timing of sales, and timing and duration of construction activities are all user-variable. Other features of the model include: provision for cost inflation; land acquisition and associated costs and timing; receipts and offsets for advance deposits on sales; retention and repayment of construction contract withholds and interest accrued on withholds; provision for a contingency on project costs; short term borrowing, retirement and loan interest; interest earned on short term investments; and a warranty reserve for buyers. All parameters for these features are user-variable.
Management Information Model: For a rapidly growing company in the automobile brokering business we developed a management information model that integrates their sales activity information and accounting systems to provide a comprehensive set of management reports not available from either system alone. The report package produced by the model includes monthly and YTD P&L by account executive, an analysis of profitability by individual broker and by product type, and numerous performance metrics presented in both tabular and graphic formats.
Revenue Projection Model: The multiple-parameter-driven, five-year revenue projection model we built for a consultant to a restaurant franchisee allowed our client to tailor the revenue stream to fit economic trends in the franchisee’s expansion territory. The model provided for multiple combinations of outlet types and revenue streams within outlets. Results were presented in both tabular and graphic outputs.
Service Operations Model: We built a financial model of this ophthalmologic instrument manufacturer's $12 million service operation that analyzed each of its service activities - warranty, contract, and billable - for each of its instrument family groups. The model integrates information from the accounting, Service Management and cost accounting systems to calculate total cost and compute gross margin by service activity and product line. Among the most important uses of the model results is the determination of the warranty-cost-of-sales for each instrument, and thereby the balance sheet warranty liability.
Five Year Planning Model: We rebuilt and automated the five-year planning model for a pre-IPO startup health data management firm. We took a cumbersome, static model and revamped it completely so that the CEO could test ideas and options and prepare projections for presentation to venture capital investors. In the course of this work we identified and corrected key modeling relationships that were causing incorrect outcomes. The model automatically produces five-year income statements, balance sheets, cash flow statements, financial performance ratios, and numerous other customized presentations and analyses of sales and profitability requested by the client.
Business Plan Model: We automated the financial model for a five-year business plan for a software developer to add user flexibility and allow scenario testing and evaluation. This model facilitated planning for new software products and follow-on services to customers.
Financial Projection Model: As a sub-contractor to a business planning consultant, we built the financial projection model for a business plan. Designed to the consultant’s specifications, this model produced five-year income statements based on complex logic for staffing up as revenue grew. The end user can determine the type and number of staff positions and start dates based on contract additions, and the type and timing of new contracts. The model times expenditures according to user inputs with automatic adjustments for hiring lead times and personnel search expenses.
Sales Forecasting Model: We developed a sales forecasting system for a $450 MM consumer products manufacturer that tracked over 200 products in more than 1,000 variations of size and packaging. The model produced annual and monthly forecasts based on input from major account managers. We also built a forecast accuracy model to measure and report performance against forecast; a sales trend reporting model that automatically ranked the 15 largest customers and the 20 best selling products for each customer and produced comparative reports of actual to budgets and forecast. The report automatically re-sequenced monthly as the customer rankings, and product rankings for each customer, changed.
R&D Expense Model: For the R&D department of this cutting-edge integrated medical instrument manufacturer we developed a custom R&D Project Expense reporting model to integrate the company's existing financial and project accounting systems and produce a flexible portfolio of project management reports not available from either system alone. Reports included headcount and spending by activity and project, with monthly and year-to-date summaries.
POS Data Analysis: We built a POS (point-of-sale) data analysis model for this major consumer products marketer to analyze the effect of TV and radio advertising campaigns. This user-interactive model consolidates POS data from multiple files, analyzes the data according to user input, and produces reports comparing the effect of media placements on sales against a control group. This model was built with Visual Basic for Applications programming that performed all tasks including writing formulas unique to the data being analyzed.
Compensation Analysis Model: We developed a custom compensation analysis model to help a growing San Francisco Bay Area business structure a compensation package for their Sales Team Managers and Account Executives. The interactive model allowed management to test compensation alternatives and predict the effect on the bottom line.
Cost Analysis Model: For this international ophthalmologic instrument manufacturer we built a cost analysis model for predicting the production cost of prototype instruments. The model imported component part information from the bill of materials system, cost data from the accounting system and, along with vendor quotes for production quantity orders, analyzed the instrument’s direct materials cost for production quantities.
Financial Statement Projection Model: For this specialty manufacturer we built a 5-year financial projection model which uses cash expenditure projections to create projected income statements, balance sheets, cash flow statements and financial performance ratio analysis. The model is driven by planning parameters that relate cash expenditures to anticipated financial and operational strategies.
Loan Analysis Model: For this major San Francisco Bay Area mortgage loan broker we automated and consolidated a set of static mortgage loan reporting spreadsheets that had been in three separate files to allow one-time data entry and automatic production of summary reports and analyses by loan representative, lender, zip code and more. Commission Model: As a follow-on project for our mortgage loan broker client we designed a model to compute commissions for loan representatives. This model is linked to the Loan Analysis Model and automatically calculates commissions based on several factors for each representative's monthly loan results.
Copyright © 2000 - 2020 William E. Crisick. All Rights ReservedLast modified: 03/23/20FMS Home Page What Clients Say Industry Expertise Modeling Objectives Feedback Contact FMS |
||