|
|
Models
Activity-Based Budgeting derives expense budgets from forecast levels of activities that generate expense. more...
This model develops expense budgets based on the expected levels of activities that drive expenses. For example the budget for the sales order processing group might be driven by the number of orders, order line items, and special orders. The model can handle dozens of cost centers with hundreds of expense accounts. Each expense budget can be driven by multiple activities. The budget time range can be a number of days, weeks, months, quarters, or years.
The key steps in the budgeting process are:
- Specify the cost centers and their expense accounts that are to be budgeted.
- Specify the measureable activities that drive expenses.
- Specify which activities drive expense for each expense account.
- Optional: Break expenses into a variable portion that is driven by activity counts, and a fixed portion that does not depend on activity counts.
- Input historical data for expense and activity counts (typically for 1-2 previous budget cycles). The model uses this data to compute historical comparison benchmarks for expense/activity ratios.
- Enter coefficients that quantify the expense impact of each activity on each expense account. You can specify these coefficients independently of historical expense/activity ratios; the historical ratios are provided as guidelines and points of comparison.
- Input expected activity counts for the budget time range. The model will compute expense budgets. (If you chose to separate fixed and variable expenses, enter budgets for fixed expenses, because these are not affected by activity levels.)
The main outputs of the model are:
- Budgets for all cost centers down to the level of expense accounts.
- Optional: The budget can be broken out by month or other time period, or presented as a total for the budget time range.
- A table showing which activities drive which expense accounts, the expense/activity ratios used in the budget with the historical values of these ratios, and the amount of expense generated by each activity in each expense account.
|
|
Capitalization Table tracks investment, share units, value and payout through a sequence of investment rounds. Securities can be common stock, preferred stock, convertible notes, warrants and options. more...
This model track investments, share units of securities, conversions, exercises, and preference rights through multiple rounds of financing of a company.
The model includes five types of securities.
- Common shares
- Convertible notes earn non-cash payments (called dividends in the model), have the most senior payment preference rights in liquidation, and can convert to common stock.
- Preferred stock earns non-cash payments (called dividends), have payment preference rights in liquidation, and can convert to common stock.
- Options have an exercise price to convert to common stock, a selling price, and limitless term.
- Warrants are options that are issued and guaranteed by the company.
You can set the number of series for each types of security. Each series of each type of security has its own prices and terms. Common share are all equivalent and have one series.
You can choose the number of investment rounds, their dates and names. Each investment round has five phases.
- "Start Phase": Stock splits occur and dividends in kind are paid at the start of each investment round. The value of the company at this point is used in determining prices of securities during the round.
- "New Sales Phase": Sell new securities (convertible notes, preferred stock, warrants, options, common stock)
- "Post Sales Phase": Record investments and share units after sale of new securities.
- "Conversion Phase": Stakeholders can convert notes and preferred shares and can exercise warrants and options to obtain common stock.
- "End Phase": Record security holdings, prices and values at the end of the investment round.
All five phases are assumed to occur on the date of the investment round. You cannot change the number of phases within a round, or which types of transaction occur in each phase of an investment round. (These changes can be made in the ModelSheet Authoring environment, but not in the model customizer.)
The model reports the following outputs.
- the number of share units of each series of each type of security purchased in each investment round
- the value and share units of each series of each type of security owned before and after each investment round
- the number of fully diluted common share units before and after each conversion event
- the valuation of the enterprise before and after each investment round
- the payout at liquidation to the original holders of each series of each type of security, after adjusting for any conversions of convertible notes and preferred shares, and exercises of warrants and options
- the payout at liquidation to each series of each type of security, accounting for liquidation preferences of senior securities. Tracking of liquidation preferences and valuation scenarios (see below) enable the model to accurately analyze a wide range of outcomes, from stellar success to bankruptcy liquidation.
You can specify how many valuation scenarios and how many investment scenarios you want.
- For each valuation scenario, you specify a valuation of the company at the beginning of each investment round.
- For each investment scenario, you specify different amounts of investment in the company in each round, segmented by rounds and types of securities.
The key results are collected on worksheets "Investment", "Shares," "Valuation", and "Payout."
|
|
Cash Flow Analysis tracks cash balances, sources and uses of cash over many time periods. Has selectable levels of detail for receipts and payments. Optional line of credit, asset borrowing base, factoring of receivables, bank float, and optional business or location segments to segment the cash flow. more...
The cash flow statement includes these features
- Starting and ending cash
- Sources of cash segmented into trade receipts and other receipts
- A database that tracks receipts accounts with one record per account
- Database that tracks receipt accounts with one record for each sales order (advanced version)
- Trade receivables aging report (advanced version)
- Uses of cash segmented into employee expenses and other disbursement accounts
- Two databases that track individual hourly and salaried employees, with start dates, end dates and pay rates
- Payroll schedule that breakouts out salaried and hourly payrolls, benefits, payroll taxes, travel & entertainment
- A database that tracks disbursement accounts with one record for each account
- A database that tracks disbursement accounts with one record for each purchase order (advanced version)
- Addendum that includes checking account cash float in cash flow analysis(advanced version)
The Advanced version includes these added features.
- a line of credit worksheet that analyzes the credit position of the business with
- Loan balances, funds advanced by creditors, funds applied to loans, excess borrowing capacity
- Routing of trade receipts to accounts controlled by creditors
- Borrowing based derived from analysis of eligible inventory and receivables
- Factoring of receivables (permanent sale or reversible)
- Bank cash float
- Segmenting the cash flow (by business units, locations or in other ways), with optional separate worksheets for the cash flow of each segment.
|
|
Cash Flow Analysis for a small start-up business tracks sources and uses of cash and cash balances over time. more...
This template tracks cash flow over time. You specify categories for cash sources and cash uses. It optionally provides a place to list text assumptions that lie behind the analysis. It is modeled after the SCORE cash flow template, with added flexibility in defining time range, time grain, receipts accounts, and disbursement accounts.
ModelSheet offers several planning templates for small startups. We recommend starting with 'Sales Plan for a Small Startup', next 'Cash Requirements for a Small Startup', then this template Cash Flow for a Small Startup. The information from each of these templates is useful in setting up the following templates.
|
|
Starting Cash Requirements tracks cash expenditures that a small startup business will face as it begins operations. more...
This template includes sources of equity investment, loans, loan collateral, and cash outlays for numerous startup expenses, deposits, and starting inventory. It optionally provides a place to list assumptions that lie behind the analysis. It is modeled after the SCORE Startup Expense template, with added flexibility in defining sources of funds and spending accounts.
ModelSheet offers several planning templates for small startups. We recommend starting with 'Sales Plan for a Small Startup', next this template 'Cash Requirements for a Small Startup', then 'Cash Flow for a Small Startup'. The information from each of these templates is useful in setting up the following templates.
|
|
Financial Plan for a Business includes four main financial statements and extensive backup information for many sectors of the plan. This version is not specialized for a type of business. more...
This model provides a generic financial plan for a business that is not specialized to any particular industry or type of business. The main reports are four financial statements: income statement, balance sheet, cash flow statement, and financial ratio report.
The model contains detailed backup information in many sectors. (For a simpler financial plan, see "Financial Plan for a Small Business".)
- Sales model
- Product and product families with sales units, prices and revenue segmented by selling locations
- Annual product support for each product, with optional deferral and accrual of revenue and expense in each time period during the support term
- Contract sales that are unrelated to products and product support, with optional deferral and accrual of revenue and expense in all time periods during the contract term that you specify
- Other options: sales scenarios, lead sales funnel with quality buckets, installed base tracking
- Production model: computes production units and cost of goods based on projected sales and desired inventory levels. Cost of goods is composed of direct material, direct labor, and purchased services. Includes optional production learning effects.
- Operating expense model: tracks indirect labor expense, facilities expense, admin and other expenses. You explicitly specify expenses in the early time periods. Later the model computes headcount and expense from revenue levels.
- Asset model: includes cash, accounts receivable, inventory (LIFO accounting), debt, asset purchases and depreciation.
- Liability model: includes accounts payable, short-term debt, long-term loans, bonds, and interest expense.
- Equity model: includes paid-in capital, retained earnings, dividends, and internal rate of return.
- Valuation model: values the business based on the present value of future net income, including a simple income model after the end of model time.
Not all features are included in the Light and Standard versions.
|
|
Financial Plan for a small Business includes four main financial statements and the features most needed by small businesses. more...
This model provides a financial plan for a small business that is based on four financial statements: income statement, balance sheet, cash flow statement, and financial ratio report.
The model provides detail in several areas.
- Sales model
- Specify sales by sales units and prices and discounts, or just as revenue.
- Product and product families
- Segment sales by sales channels/locations.
- Production model tracks cost of goods and services
- As a percent of revenue; or
- As cost per unit produced. Computes production units and inventory (value and units) based on projected sales and desired inventory levels.
- Operating expense model
- Tracks employment expense with wages, benefits, payroll taxes, bonuses, and commissions.
- Optionally tracks employee headcount.
- Tracks departments that you specify, and expense accounts that you specify for each department.
- Asset model: includes cash, accounts receivable, inventory (LIFO accounting), small untagged assets and large tagges assets, asset purchases and depreciation.
- Liability model: includes accounts payable, short-term debt, long-term loans, and interest expense.
- Equity model: includes paid-in capital, retained earnings, and dividends.
- Valuation model: values the business based on the present value of future net income, including a simple income model after the end of model time.
Not all features are included in the Light and Standard versions.
|
|
Financial Plan for Medical Services has all the usual features for a financial plan, plus it tracks patients, procedures, medical staff, equipment utilization, and support organization. more...
This financial plan is specialized for a medical service businesses. It tracks numerous features particular to medical services and it produces financial statements with detailed backup reports in key sectors of the model.
- Revenue model
- Most revenue is generated by procedures performed for patients. The model tracks new patients and continuing patients, and applies a collection of diagnostic and therapeutic procedures to these populations at specified rates. Fees are based on a 'fee for service' model that assigns a fee for each procedure. Procedures and revenue are segmented by location.
- The plan also includes grant revenue.
- Cost of Services model
- Medical staff: Tracks, job types, headcount, compensation including benefits and bonuses, and staff utilization rates. Staff cost of services is computed as number of procedures times fee per procedure.
- Equipment: Tracks numbers of pieces of major equipment and utilization rates. All medical equipment is considered to be leased.
- Tracks medical staff hours billed, equipment time and allocated cost, and other costs (such as supplies) associated with each procedure. Medical staff time is segmented by staff types that have different hourly fees and compensation levels.
The plan has all the features of a financial plan for a general business.
Financial statements: income statement, balance sheet, cash flow statement, and ratio report, plus backup detail for key sectors of the plan.
- Operating expense model
- Support staff: Tracks job levels, headcount, and compensation including benefits and bonuses.
- Support departments with operating expenses including staff expense
- Facilities, utilities, G&A expenses with several accounts
- Costs of underutilized medical assets are absorbed in operating expense (instead of cost of services).
- You explicitly specify expenses in the early quarters. Later the model computes headcount and expense with formulas based on revenue.
- Balance sheet
- Asset model: Includes cash, accounts receivable, supplies inventory, debt, asset purchases and depreciation.
- Liability model: Includes accounts payable, short-term debt, long-term loans, and bonds.
- Equity model: Includes paid-in capital, retained earnings, dividends, and internal rate of return.
- Valuation model: Values the business based on the present value of future net income, including a simple income model after the end of model time.
|
|
Financial Plan for Professional Services tracks major and minor engagements by practice areas and billable professional staff time, in addition to the usual features of a financial plan for a business. more...
This workbook contains a financial plan specialized for a professional service businesses. It tracks practice areas, engagements professional staff, support staff, and produces financial statements with detailed backup reports in key sectors of the model.
- Sales model: Revenue is generated by billable professional staff hours that are segmented by practice area, by major engagement, by type of professional staff, and by sales channels / segments. The key inputs to the revenue model are billed hours and hourly fees.
- Cost of Services model: Tracks professional staff hours billed, billable and applied to major and minor engagements. Professional staff time is segmented by staff types that have different hourly fees and compensation levels.
- Major engagements are segmented by practice area and sales channel and tracked individually. Each one has a start date, duration, total professional staff hours, and distribution of staff hours over staff types and over time during the duration of the engagement.
- Minor engagements are segmented by practice area and sales channel, and by amount of professionals staff time of each staff type. Otherwise, these engagements are tracked as a group.
- Operating expense model: Tracks professional staff utilization, support staff labor expense, facilities expense, admin and other expenses. You explicitly specify expenses in the early quarters. Later the model computes headcount and expense with formulas of the form Headcount or Expense = Constant * Revenue ^ Exponent.
- Asset model: Includes cash, accounts receivable, supplies inventory, debt, asset purchases and depreciation.
- Liability model: Includes accounts payable, short-term debt, long-term loans, bonds, and interest expense.
- Equity model: Includes paid-in capital, retained earnings, dividends, and internal rate of return.
- Valuation model: Values the business based on the present value of future net income, including a simple income model after the end of model time.
|
|
Financial analysis of an investment project tracks investment, income statement and balance sheet items, cash flow, and measures of return on investment. Includes blended equity, debt and lease financing. more...
This model analyzes the return on investment of an investment project. It analyzes sub-projects that have their own fixed investment, working capital, revenues and expenses within a larger project. You can optionally specify a list of products and services, each with its own price, sale units, and variable expense per sales unit.
The main outputs of the model are:
- A summary of investment, on worksheet 'Investment'
- Statement of operations, on worksheet 'Operations'
- Financial summary of all-equity financing, on worksheet 'Equity Fin'
- Financial summary of blended equity, debt and lease financing, on worksheet 'Blend Fin'
- Optional: For each sub-project,
- A worksheet with a financial summary of all-equity financing
- A worksheet with a financial summary of blended equity, debt and lease financing
- Net present value (NPV), return on capital (ROC), and internal rate of return (IRR), on worksheet 'Valuation'
The primary data inputs to the model are:
- working capital requirements, initial value, and salvage value
- revenues, operating expenses, income taxes and tax credits
Optional: a number of subprojects, each with its own initial value, start date, depreciation method and life, and salvage value, whose financial performance is reported separately and in aggregate
You can specify the number of financing scenarios included in the model. Each financing scenario can have a different blend of equity, debt and lease financing.
The original version of this model was based on an investment project analysis workbook named capbudg.xls made available for public use on the website of Professor Aswath Damodaran of NYU Stern School of Business at http://pages.stern.nyu.edu/~adamodar/ . ModelSheet Software is solely responsible for any errors in this derivative work.
|
|
|