Click on the PLAN.xls spreadsheet workbook link to open and view or download the Microsoft Excel® PLAN.xls spreadsheet file. From MS-Excel spreadsheet program, Go to the PRINT options and select PRINT ENTIRE WORKBOOK. Print the 32-page workbook with sample data to get an overview of this workbook. The calculated values in the PLAN.xls workbook are linked to the REPORT.xls workbook to compare planned values to actual values. The PLAN.xls workbook contains 14 page tabs listed below:
- Labor Budget
- Labor Summary
- Formula Variables
- Profit Target
- Profit Plan
- Profit Plan Analysis
- Time Analysis
- Multiple of DPE
- Rates and Multipliers
- Cash Flow Plan
- Tax Provision
- FTE Calculator
- Labor Category
- Prior Year's Key Indicators of Financial Performance
Each Page Tab is described below.
1. Labor Budget
The first step in the development of a bottom-up profit plan is the labor budget. The labor budget spreadsheet is composed of three sections:
-
Data entry area for hourly pay rates, utilization rates and benefits.
-
Calculated values for direct and indirect hours and paid-time-off benefit hours.
-
Calculated values for direct and indirect labor dollars and labor-related mandatory and customary benefits.
The last two columns show the DPE (direct personnel expense) rate and billing rate for each employee. The DPE rate is calculated by multiplying the raw labor rate times the benefits factor. The billing rate is calculated by multiplying the raw labor rate for each employee times the effective multiplier from the profit plan analysis spreadsheet. The totals in the labor budget spreadsheet are linked to the other spreadsheets in this workbook and the other workbooks. Any changes to the labor budget are automatically updated in the other spreadsheets.
No overtime is included in the labor budget since that would be an undesirable long-term commitment. If overtime were included in the labor budget, the firm would be committed a year in advance to work overtime just to meet its profit target. Total available hours in the labor budget are measured as full-time-equivalents (FTE). A full-time-equivalent is based on standard hours of 2080 hours per year (40 hours per week times 52 weeks per year). By using standard hours and full-time-equivalents, the key indicators of financial performance are more comparable among firms and the same firm from period to period.
2. Labor Summary
The calculated values on this page are linked from the labor budget spreadsheet. This shows the analysis of labor dollars and hours by principal and employee with the average labor rates and average direct labor rates for each category and firm-wide.
3. Formula Variables
The formula variables on this page are linked to the calculated values in the labor budget for federal and state unemployment taxes, FICA and Medicare taxes, bonus percentage of base pay, retirement plan contribution and estimate for raises. Any changes to the formula variables automatically update the values in the labor budget and profit plan spreadsheets.
4. Profit Target
The bottom-up profit plan begins with the operating profit target before discretionary profit sharing distributions and income tax. The bonus and retirement plan contribution calculated in the labor budget is added to the desired increase in net worth.
The operating profit target is composed of:
-
increase in net worth
-
+ income tax provision
-
+ discretionary cash bonus and retirement plan contribution
-
+ dividend or principals' bonus or return on investment
-
- non-operating income such as interest and gain on sale of equipment
-
= operating profit target
5. Profit Plan
The profit plan establishes the billing rates and profit multipliers to be used in the coming year. The profit plan establishes the key indicators of financial performance and milestones used to measure and control the progress toward the profit plan goal for the coming year. This example of profit planning is a bottom-up budgeting process that begins with the operating profit target. The operating profit target calculation is shown on page 4 of the labor budget. The profit plan establishes the profit target and required revenue and level of expense necessary to support the labor budget and attain the profit plan goal.
The required revenue is calculated as follows:
-
Operating Profit Target (shown on page 4 of labor budget)
-
+ Indirect Expense (includes indirect labor and labor-related benefits)
-
+ Direct Labor (from labor budget)
-
= Net Revenue
-
+ other direct and reimbursable expense (estimated as percentage of net revenue based on prior year's experience)
-
= Required revenue to support current staff level at desired operating profit
Classify revenue by type. This facilitates revenue projection when DPE/Hourly and extra service revenue is estimated as a percentage of fixed-fee revenue. Typical revenue types are:
-
Fixed fee
-
DPE, Hourly
-
Extra Service (DPE, Hourly)
-
Other operating income
-
Reimbursable expense revenue
-
Unbilled services revenue
Enter the prior year's actual final income statement values in the reference budget column. All profit plan labor and labor-related expenses are automatically copied from the labor budget spreadsheet. The operating profit target values are automatically copied from calculated values in the labor budget workbook. To complete the profit plan, enter the other indirect expense budget values using the prior year's actual values in the budget reference column as a guide. When the data entry into the profit plan spreadsheet page is complete, the calculation of the key indicators is automatically updated in the profit plan analysis of the labor budget workbook.
6. Profit Plan Analysis
This page is a summary and analysis of the calculated values linked from the PLAN.xls page of the REPORT.xls workbook. The key indicators of financial performance are shown on this page. The profit plan key indicators calculated on this page are compared to the actual values in the REPORT.xls workbook.
7. Time Analysis
This page is a summary of the totals linked from the labor budget for technical and non-technical total labor dollars and total labor hours. The annual values are divided by twelve to calculate the monthly objectives for dollars and hours. Full-time-equivalents are calculated by dividing the total annual hours by 2080 standard hours (52 weeks X 40 hours per week). Utilization rates are calculated for both dollars and hours. The utilization rate based on dollars is the most significant. The ratio of technical to non-technical is calculated. The values on this page are compared to the actual values in the 3-ANALYSIS section in the REPORT.xls workbook.
8. Computation of Benefits Factor for Multiple of Direct Personnel Expense
The calculated values on this page are linked from the LABOR page of the PLAN.xls spreadsheet. The values are automatically updated with any change to the labor budget values. The target multiple of direct personnel expense is calculated by dividing the DPE factor into the profit plan net multiplier.
9. Summary of Budget Rates and Multipliers
This is a summary of the important rates and multipliers used for project budgeting and billing. The values in the Annual Profit Plan column are linked from page 6-Profit Plan Analysis. The values in the Project Budget Multipliers column may be adjusted to round off the values to be used for actual project budgeting and billing.
10. Cash Flow Plan
This page is a model cash flow plan beginning with the operating profit target from the profit plan. The operating profit is spread equally over the twelve periods. The other increases and decreases to cash are spread over the appropriate periods. The net increase or decrease to the beginning cash balance is accumulated over the twelve periods to calculate the ending cash balance for each period. The monthly ending cash values are used to create the cash flow plan graphic chart. The actual cash and accounts receivable balance shown on the chart is linked from the DATA ENTRY page of the REPORT.XLS workbook.
The cash flow plan prorates the operating profit target and other non-operating revenue over twelve periods to create a straight-line cash flow chart. Enter other increases and decreases to cash to get the net increase or (decrease) to cash for the period. Add the net increase or (decrease) to the beginning cash balance to calculate the projected cash balance. Create a table showing the accumulated cash balance for each period. Use the table to create a line graph chart of the projected cash balance for each period. Add rows in the cash flow plan to enter the actual cash balance and accounts receivable balance for each period. The cash flow plan chart shows the progress toward the cash flow target for the plan year. The Cash Flow Plan graphic chart compares profit plan cash projection to actual cash-in-bank and accounts receivable for the year-to-date.
11. Computation of Federal and State Income Tax Provision (Optional)
This page shows the computation of the federal and state income tax provision for a regular C Corp. on the accrual basis. The actual cash-basis tax could be more or less depending on how cash receipts are managed at year-end. This computation is based on the desired increase in net worth plus any dividend distribution to shareholders. The values on this page are linked from the Labor Budget page of the PLAN.xls page. This page is linked to the 4-PROFIT TARGET page for estimated Corp. Income Tax Provision (on the accrual basis).
12. FTE Calculator (Top-down Budget Method)
This page is optional and is used primarily during hard times when total firm revenue is decreasing. This template calculates the required technical and non-technical full-time-equivalents required to produce the projected available revenue at target budget rates and multipliers. This page is optional and is not linked to any other spreadsheet page.
13. Labor Category
This page calculates the average cost and billing rates by labor category such as Principal, Project Manager, Senior Drafter, Jr. Drafter, etc. Use the labor categories for project labor budgeting at various rates for each project budget category. Use the raw labor rate values in the Labor Budget. This page is optional and is not linked to any other spreadsheet page.
14. Prior Year's Key Indicators of Financial Performance
This page includes the detail line items for income and expense for comparing prior year's Income Statements to the Profit Plan for the current year. This includes the Time Analysis detail for prior years and calculation of the key indicators of financial performance showing full-time-equivalents, utilization rates, multiples of direct labor, and values per direct hour. At the end of this page is a comparative percentage analysis of labor and labor-related expenses and non-labor related expense.
