The report.xls module of the CAPP - Computer-Aided Profit Plan™ measures the progress toward the profit plan goal and objectives. To reach the profit plan goal, progress toward that goal must be measured and evaluated. The Analysis of Operations report compares actual values to planned values and identifies and analyzes variances between actual results of operations and the profit plan target. Interpret the results of analysis to make informed decisions on what actions need to be taken to reach the profit plan target.
Click on the REPORT.xls link to open the Microsoft Excel® Operations Analysis workbook. In Excel, go to the FILE PRINT menu and select PRINT ENTIRE WORKBOOK. Print the entire workbook with sample data and graphic charts. The report.xls workbook contains the following page tabs.:
-
Current Data
-
Prior Data
-
Analysis
-
Graphic Charts
-
Revenue and Profit
-
Total Labor, Direct and Indirect Labor
-
Overhead & Indirect Labor
-
Cash Flow Plan
-
Absolute Values
-
Full Time Equivalents
-
Net Revenue and Operating Profit per FTE
-
Utilization Rates
-
Per Direct Hour
-
Multiples of Direct Labor
-
Revenue Factor
-
Cash & Accounts Receivable at Year-End
-
Total Cash & Accounts Receivable at Year-End
-
1. Current Data
Enter the actual values from your general ledger income statement for the current period in this section of the spreadsheet workbook. Enter the time analysis actual values for the current period in the Analysis of Operations section of this spreadsheet along with any overtime for the current period. The current period values are totaled in the first column to calculate the year-to-date actual values for the profit plan year. The last column shows the profit plan monthly objectives. The profit plan monthly objectives are calculated by dividing the annual profit plan by twelve monthly periods. Copy and Paste the current period column in the Data Entry spreadsheet to the Report spreadsheet current period column. The year-to-date column is linked to the Report spreadsheet and is updated automatically. The graphic charts workbook is linked to the accumulated values in the table at the end of this spreadsheet. The graphic charts for revenue and expense are created in this workbook and then moved to the charts.xls spreadsheet file.
Enter the actual year-to-date values for the prior year in this spreadsheet. All other cells are calculated values linked from other spreadsheets. This spreadsheet is used to write the Executive Summary Report where current year-to-date values are compared to the year-to-date profit plan and the actual year-to date values for the prior year.
2. Prior (Year's) Data
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 (page 7) of the labor budget workbook.
The prior year's key indicators are compared to the current year-to-date and profit plan key indicators. The current year's key indicators are more meaningful when compared to prior years' and the profit plan key indicators.
This spreadsheet is used to calculate the key indicators of financial performance for prior year's history. Graphic charts of the ratios and multipliers are linked to the data in this spreadsheet. The key indicators of financial performance are most useful when relating current experience to prior performance or to a budget. The key indicator ratios are more useful when studied over several reporting periods to establish patterns. The graphic charts of key indicators are meaningful only if the manager understands the basis, limitations and values of each ratio. Studying the key indicators on a continuing basis enhances the meaning and understanding of the values.
Enter the actual values for prior year's income, expense, cash balance, aged accounts receivable, time analysis, and overtime in this spreadsheet. The analysis of operations and key indicators of financial performance for each year are automatically calculated in this spreadsheet. Enter the oldest year in the left column and subsequent years to the right. You may hide any columns to the right that are not used. This improves the appearance of the graphic charts linked to this spreadsheet. The year-to-date actual values are shown in the next to the last column to the right with the profit plan values in the last column.
3. Analysis
All of the values in this section of the workbook are calculated values except for the current period values copied from the DataEntry section (see above). The Profit Plan Progress Report calculates the variance analysis between the profit plan and actual values for the current period and year-to-date. The variance analysis for the key indicators of financial performance are automatically calculated.
The last section of the Analysis of Operations Report shows the calculation of the price-volume variance analysis for the important categories of the profit plan. The price-volume variance analysis values are linked to the Executive Summary Report. The price/volume variance analysis is calculated in the Analysis of Operations report for Net Revenue, Direct Labor, Overhead and Operating Profit. Price variance is due to the difference actually charged for an hour of direct labor and the planned average billing rate. Volume variances are due to the difference in the actual number of direct labor hours charged to projects and the profit plan available direct labor hours based on the target utilization rates for technical employees.
4. Graphic Charts
The first four graphic charts show the progress toward the monthly objectives and profit plan goals for the current year. Update the accumulated actual values each month for these four graphic charts using regular spreadsheet techniques. To update a line graph, right click on the line to be updated. You only need to update the actual values. The Profit Plan Budget line is already fully extended to the year-end period. Click on SOURCE DATA and select the Series line to be updated. Then, click on the spreadsheet icon for Values. The spreadsheet data will pop up showing the current row data for the chart line. Highlight the data extending the selection to include the next month. Then, close the Source Data Values window and select the next Series line to be updated and click the spreadsheet icon for that value and the spreadsheet will pop up showing the highlighted data for that line. Follow the same procedure as before. After all values have been updated, close the window and the updated chart will appear.
-
Revenue and Profit
-
Total Labor, Direct and Indirect Labor
-
Overhead and Indirect Labor (Shows how much of the current year's overhead variance is due to variance in indirect labor).
-
Cash Flow Plan (Shows the relationship between the planned cash value and actual cash value and accounts receivable).
The other graphic charts compare current experience to prior performance and the profit plan. Ratio analysis and the key indicators of financial performance are more useful when studied over several reporting periods to establish patterns. They are meaningful only if the manager understands the basis, limitations and values of each ratio. Ratio analysis on a continuing basis enhances the meaning and understanding of the values.
