Executive Summary - Automated Analysis
OBJECTIVE
The objective is to produce a quick and meaningful one-page analysis of operations along with graphic charts of key indicators of financial performance with a minimum amount of data entry and update. The data comes from the monthly Time Analysis and Financial Statements produced by the Company's accounting system. It should only take about thirty minutes to produce a complete financial report and analysis of operations with supporting reports from the accounting system.
CONCEPT
The automated Executive Summary and Analysis of Operations report is composed of an Excel spreadsheet file (REPORT.xls) linked to a Word.doc file (ExecutiveSummary.doc). All values in the ExecutiveSummary.doc file are updated automatically when the values in the spreadsheet (REPORT.xls) are changed or updated. No manual editing of the ExecutiveSummary.doc file is required. Certain words in the ExecutiveSummary.doc file change automatically depending on whether the value in the spreadsheet is positive or negative. Words such as more, less, increase, decrease, favorable or unfavorable change automatically to be consistent with the variance analysis in the REPORT.xls spreadsheet file.
EXCEL SPREADSHEET WORKBOOK FILE - (REPORT.xls)
Open the spreadsheet file REPORT.xls before the ExecutiveSummary.doc file so the Word.doc file will update faster. The REPORT.xls spreadsheet workbook consists of fifteen tabs. The first tab contains the data entry area for the current year's data. The second tab contains prior year's data. The third tab contains the variance analysis for the current year-to-date actual compared to the current year-to-date budget and current year-to-date actual compared to last year's year-to-date actual values. The last twelve tabs are graphic charts. The first four graphic charts require monthly update to extend the actual year-to-date line-graph values for the new period. Follow the procedure below to update the line-graph values.
To manually update a line graph, right click on the line to be updated. 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.
PAGE TABS:
1-Current Data: In the first section of this tab, enter sub-totals from the current period's Time Analysis Report and Income Statement in the highlighted unprotected cells in columns C through N. The Time Analysis should be sorted with subtotals for technical and non-technical staff. The year-to-date values are calculated automatically in column O. The period number in cell O5 is updated by the period macro. Update the period ending date in cell O6. Update last year's year-to-date actual from last year's Time Analysis and Financial Statements in Column P. Enter the Annual Profit Plan values in Column Q. The monthly objective is calculated automatically in Column R. Update the cash balance on Row 48 from the current period's Balance Sheet. Update the Aged Accounts Receivable data from the Aged Accounts Receivable Report for the current period. The period update macro copies the Current Period Cash and Receivable data to the appropriate rows in Column O. The first four graphic charts are linked to data in the Current Data tab.
2-Prior Data: Enter prior year's actual historical data for up to thirteen prior years. Enter the oldest prior year in Column C. Hide any unused columns to the left of Column P so the graphic charts appear correctly.
3-Analysis: All values in the Analysis tab are calculated values. No data entry is required in this tab. Column G, I and J are hidden. Unprotect the worksheet to unhide and view the values in Column G, I and J. To unprotect worksheet, go to TOOLS, PROTECTION, and UNPROTECT SHEET. The values in Columns G, I and J are words linked to the ExecutiveSummary.doc file for over/under, more/less, and increase/decrease. Click on the word's cell to view the formulas that change the text value of the word. No passwords are used to protect and unprotect a worksheet.
PAGE TAB-GRAPHIC CHARTS
1.-Revenue: Graphic chart comparing budget to actual for the current year's net revenue and operating profit.
2.-Labor: Graphic chart comparing budget to actual for the current year's total labor, direct and indirect labor.
3-Expense: Graphic chart comparing budget to actual for the current year's overhead and indirect labor expense.
4-Cash Flow Plan: Graphic chart comparing actual cash balance and accounts receivable to the cash plan for the plan year.
5-Absolute Values: Graphic chart comparing actual year-to-date values to the profit plan budget and thirteen prior years' actual values for total revenue, gross profit, direct labor, net revenue, overhead expense, break-even and operating profit.
6-FTE: Graphic chart comparing actual year-to-date values to the profit plan budget and thirteen prior years' actual values for technical full time equivalents and non-technical full time equivalents using a stacked bar graph.budget and up to thirteen prior years' actual.
7-Per FTE: Graphic chart comparing actual year-to-date values to the profit plan budget and thirteen prior years' actual values for net revenue and operating profit per total staff and net revenue and operating profit per technical staff.
8-Utilization Rates: Graphic chart comparing the current year-to-date actual utilization rates based on firm-wide dollars, firm wide standard hours and technical only hours to the profit plan
9-Per Direct Hour: Graphic chart comparing actual year-to-date values to the profit plan budget and thirteen prior years' actual values for net revenue, average direct labor rate, overhead expense, break-even and operating profit.
10-Multiples of Direct Labor: Graphic chart comparing actual year-to-date values to the profit plan budget and thirteen prior years' actual values for net revenue, overhead rate, break-even and operating profit.
11-Revenue Factor: Graphic chart comparing actual year-to-date values to the profit plan budget and thirteen prior years' actual values for labor percentage of net revenue, revenue factor, net multiplier and utilization rate (based on dollars).
12-Cash & Accounts Receivable: Graphic chart comparing actual year-to-date values to thirteen prior years' actual values for Cash and Accounts Receivable at year-end.
13-Total Cash & Accounts Receivable at Year-End: Graphic chart comparing actual cash and accounts receivable to prior year's in a stacked bar graph.
WORD.DOC FILE - (ExecutiveSummary.doc)
All variables in the ExecutiveSummary.doc file are linked to the report.xls spreadsheet file. To view the link, right click on the shaded value and then click on Linked Worksheet Object to Open Link or Edit Link. All updates to the ExecutiveSummary.doc word.doc file are automatic. The ExecutiveSummary.doc file is composed of two pages including the report cover sheet and the Executive Summary Analysis of Operations text. The report cover sheet lists the contents of the report, which includes a printout of the Balance Sheet, Income Statement, Profit Planning Monitor and Time Analysis from the firm's general ledger accounting system. These reports serve as backup for the Executive Summary - Analysis of Operations report. A summary of aged accounts receivable and unbilled services by client may be included with the report.
The Executive Summary Report has three sections and is limited to one page of text. The first section data comes from the Time Analysis Report and describes the variances in utilization and full time equivalents from the profit plan and for the same period-to-date last year. A spreadsheet is maintained to analyze the overtime worked by each staff member. Overtime is calculated by taking the difference between total hours worked and standard hours (40-hour week) for the period-to-date. The full time equivalent value of overtime is calculated in a table in the report.xls spreadsheet file.
The second section of the Executive Summary contains the analysis of variances from the profit plan and for the same period-to-date last year for the condensed Income Statement including key ratios and multipliers from the Profit Plan for the same period-to-date last year including:
Total Revenue
Net Revenue
Revenue Factor
Direct Labor
Overhead
Break-even
Operating Profit
The third section of the Executive Summary includes comments about Cash and Accounts Receivable on the Balance Sheet. The current year-to-date balances are compared to last year's year-to-date balances and accounts over ninety days old are compared. Other extraordinary comments could be included in the Executive Summary Report, but would have to be manually updated and edited each period. Place the graphic charts behind the Executive Summary Report. A printout of first three spreadsheet page tabs including Current Data, Prior Year's Data and Analysis may be placed behind the graphic charts in the report but could be excluded since the data is redundant and most of the information is included in the Executive Summary and graphic charts.
