ACC Value Challenge User Manual
Contents
1. after adjusting the security level Options View i Calculation Edit General Transition di Custom Lists Security Tab Color International Save Error Checking Spelling Security File encryption settings For this workbook Password to open File sharing settings For this workbook Password to modify C Read only recommended Digital Signatures Privacy options C Remove personal information from file properties on save Macro security Adjust the security level For files that might contain macro viruses and Macro Security S ia specify names of trusted macro developers Macro Security Button 2 The model has been tested for various outcomes We thus do not expect users to encounter any problems However if at any point the program underlying the model fails to run the window shown below will pop up Simply click End to cancel the error message Next reinitialize the model 2 Copyright 2008 Association of Corporate Counsel acc com by clicking the Initialize Model button on the assumptions page of the model This will restore the model s original settings Microsoft Visual Basic Run time error 13 Type mismatch The End button will cancel the error message GUIDANCE FOR USERS I General Guidelines 1 Inputting Changes The model has two basic pages e The first page is labeled Assu2. No similar function has been provided for lateral hires If you wish you can simply raise the number used in the cell for the cost of recruiting first year associates to take into account the recruitment costs of lateral hires even though the number in this cell will be applied only to the first year associate T7 Copyright 2008 Association of Corporate Counsel acc com classes Such an approach should still give a reasonable approximation of overall results Alternatively you can insert zero in this cell and adjust the overhead factor below to take into account recruitment costs But by using this alternative you will not see the incremental costs and or improvements from increased retention and reduced turnover unless you specifically adjust the overhead number Changes made to cost of recruiting a first year associate will automatically update the revenue and profits report Go to the Profitability Report sheet at anytime following changes to first associate recruiting costs to see the changes in revenues and profits G STEP 7 INPUT OVERHEAD AS A PERCENT OF TOTAL REVENUE 1 Overhead will usually include associate benefits salaries and benefits for other non partner personnel rent insurance etc Since firms have so many ways to calculate and manage overhead we have kept the model as simple as possible and ask that users simply estimate overhead as a percent of total revenue which will be total partner associat
3. C Association of Corporate Counsel User Manual for the ACC Law Firm Model BACKGROUND As part of the Association of Corporate Counsel ACC Value Challenge ACC commissioned a major worldwide economic consulting firm to develop a computer based model of basic law firm economics The model allows in house counsel and law firms alike to test assumptions and input data for example numbers of associates and partners rates overhead etc and see how changes in these and other factors can affect a firm s efficiency and profitability There are a number of reasons we commissioned the model and are making it publicly available e For law firm attorneys and staff As we explore ways to improve the value of legal services we appreciate that firms will be concerned with the unknown By making the model publicly available we hope to give firms a rudimentary tool for testing how changes in staffing or other factors might impact their operations and economic results e For in house attorneys General Counsel and other in house lawyers wear many hats They are first and foremost the primary lawyers for their companies practicing in a wide range of substantive areas But they also are general contractors serving as the chief purchasing agents for outside legal services Elsewhere in a company by having a detailed understanding of the business dynamics of suppliers purchasing agents are able to work with suppliers to control costs and efficiencie
4. ates partners and other timekeepers for a typical large leveraged law firm You should first revise these numbers in the green shaded textboxes to the actual current numbers of any firm size i e large leveraged law firm mid sized regional law firm or small law firm As part of Step 1 there is also a green textbox below the associate grid for you to input an assumed attrition rate of associates After you input an assumed attrition rate for associates this percent will be applied to each class of associates and over all five years Alternatively you can set the assumed attrition rate for associates at zero and input instead specific numbers of associates in any of the classes in any of the 4 Copyright 2008 Association of Corporate Counsel acc com years If you then click the Profitability Report button the revenue and profits results will be based on the specific numbers you had inserted 4 The model assumes that each year twenty percent of the eighth year associates become fifth tier partners that forty percent remain eighth year associates and that forty percent leave the firm These proportions remain constant in all years You can overwrite these assumptions by manually inserting specific numbers in the cells 5 If you want to provide for attrition of partners or other timekeepers this must be done manually The model has not assumed any automatic increase or decrease in partners or other timekeepers other
5. e and any other timekeeper billings before write offs or write ups Depending on the interest of users future versions of the model might be designed to test more specific components of overhead Unless or until that is done users can generally estimate a reasonable percentage for this purpose They likewise can test various changes in this factor for various scenarios Changes made to overhead will automatically update the revenue and profits report Go to the Profitability Report sheet at anytime following changes to overhead costs to see the changes in revenues and profits III Resulting Revenues and Profits Page 2 Profitability Report of the model l 3 At the top of the Profitability Report page you will see the staffing that results from the assumptions you have made on the first page of the model for each associate partner and other timekeeper class You then will see the total revenue in U S Dollars generated by each group of professionals in each year Next the model reports resulting numbers showing average revenue per partner and average revenue per attorney The growth in percent for these revenue parameters from year to year is also provided 8 Copyright 2008 Association of Corporate Counsel acc com e Average revenue per partner Obtained by dividing total revenue by the number of partners for a given year e Average revenue per attorney Obtained by dividing total revenue b
6. inflation rate for future years at zero in which case associates will simply move up to the next year s salary without an additional inflation adjustment and other timekeepers will stay at a fixed salary year after year The revenue and profits report will also automatically update to incorporate changes made to the current salary structure or to the salary growth rate textbox As in other functions you can also input specific salaries in any of the future years for any class of associates and other timekeepers After you click the Profitability Report button the results will be updated based on the specific numbers you have inserted F STEP 6 INPUT THE COST OF RECRUITING A FIRST YEAR ASSOCIATE 1 2 3 Use this green textbox for inserting the average cost to recruit a first year associate taking into account all out of pocket costs travel for interviewers and candidates meals entertainment etc lost billable time for interviewers both on campus and at the firm the all inclusive costs for running the summer program costs of support personnel for the hiring function and the hiring committee costs for paying recruits while they are taking the bar exam costs to relocate recruits estimated costs of training first year associates before they are billing to full capacity etc Most firms tell us this number ranges from 250 000 to 400 000 This number will then be applied to the first year classes year to year
7. ll stay constant unless you input specific billing rate changes in specific cells 4 Youcan also modify targeted billing rates in specific cells after which you will need to click the Profitability Reports button to update and view the resulting revenue and profits report 5 Copyright 2008 Association of Corporate Counsel acc com C STEP 3 INPUT ANTICIPATED ANNUAL BILLABLE HOURS l Again start with the green shaded textboxes and input the current annual billable hour targets for associates partners and other timekeepers You can insert the same target of billable hours for every class or you can insert different numbers for different classes The same billable hour targets are then applied to each of the forcasted growth years Year 1 to Year 5 of the model for each class As in earlier functions both the assumptions and profitability pages will update automatically based on the assumed levels of current and future billable hour targets and based on entries made in the green shaded textboxes You can also modify targeted billable hours in specific cells for each timekeeper class If you do so when you then click the Profitability Report button these revised numbers will be used to revise the revenue and profits report on the Profitability Report page D STEP 4 INPUT THE ANTICIPATED RECOVERY RATE 1 Insert here the assumed recovery rate for all billings taking into account write offs premium billi
8. mptions and is the place where you will input most of the information you want to test for different scenarios e The second page is labeled Profitability Report and it will display profitability revenue and similar results depending on the data you input There is a simple rule of thumb when inputting data e Any changes you make to the green shaded cells sometimes called textboxes will automatically update all the relevant cells throughout the model including on the Profitability Report page e Any changes you make to the white cells unlike the green shaded textboxes will not automatically update in the rest of the model Rather if you manually make changes to the white cells you will then need to click the Profitability Report button discussed more fully below for those changes to be spread through the rest of the model 2 Changes in the Base Year Assumptions To set the base year assumptions insert numbers in the green shaded textboxes The model will automatically update both the growth assumptions and the resulting revenues and profits Go to the Profitability Report sheet following changes to any of the green shaded textboxes to view the resulting revenue and profits report There is no need to click the Profitability Report button or any other button on the Assumptions page when changes are made to the current year s assumptions 3 Copyright 2008 Association of Corporate Counsel acc com
9. ngs etc For most firms this will be in the 88 to 96 range One scenario you might want to test on the model is assumed improvements in recovery rates with less attrition improved expertise more efficient staffing of matters etc Changes made to the anticipated recovery rate will automatically update the revenue and profits report Go to the Profitability Report sheet at anytime following changes to the recovery rate to see the changes in revenues and profits Please note that a decimal input such as 0 85 in this textbox will be interpreted by the model as 0 85 and not 85 E STEP 5 INPUT SALARIES FOR ASSOCIATES AND OTHER TIMEKEEPERS l Input the current salaries for associates and other timekeepers 6 Copyright 2008 Association of Corporate Counsel acc com 2 Below these cells there also is a green textbox for you to input an assumed 5 percent of salary increases for future years on top of the current salary structure If you input some percent say 3 associates will be assumed to advance to the next salary level year after year and those salaries will have been further increased by 3 each future year The salaries for other timekeepers will likewise increase each year year after year at the percent that is inserted here Please note that a decimal input such as 0 03 in this textbox will be interpreted by the model as 0 03 and not 3 Alternatively you can set the assumed salary
10. s while improving quality to the benefit of both sides By using the ACC commissioned model we hope corporate counsel will have a useful tool for these purposes e For law students consultants media and others All are encouraged to use the model to test their own theories identify opportunities write articles and otherwise help promote ways to improve value in legal services We would like to hear from you as to how you and others are using the model what you have learned and what enhancements and modifications you would suggest Also we l Copyright 2008 Association of Corporate Counsel acc com hope you will share with us any articles or other materials that are derived from use of the model or other elements of the Value Challenge Please send your comments and materials to accvaluechallenge acc com This Model was created as a stimulus for discussion and no other purpose ACC makes no representations regarding its fitness of use and expressly disclaims any liability whatsoever arising from use of this Model or any part of its contents GETTING STARTED MACRO SETTINGS 1 In order to successfully run the model the user s macro security permissions must be set to medium at most In order to adjust your macro security levels From the Tools menu click Options then click the Security tab as shown below Click the Macro Security button then set the security level to Medium Click OK
11. ternative scenarios following the steps in paragraph 8 above You can then print the saved reports In future versions of the model we may attempt to build ways to retain each tested scenario and provide an automatic way for doing comparisons of different scenarios That function does not however exist with this initial version of the model FEEDBACK We would like to hear from you as to how you and others are using the model what you have learned and what enhancements and modifications you would suggest Please send your comments and materials to accvaluechallenge acc com 9 Copyright 2008 Association of Corporate Counsel acc com
12. than a percent of eighth year associates being moved into the fifth tier partner level each year see paragraph 4 above 6 The model does not include any increases for lateral associates or lateral partners or the hiring of additional other timekeepers from one year to the next If you wish to include the hiring of professionals in one or more of these categories you can manually input specific numbers in the relevant cells After you click the Profitability Report button these additional hires will be included in the calculations based also on the other assumptions that will have been made for Associate attrition billing and salary rate increases etc 7 Another way to handle lateral hires is to slightly reduce the assumed attrition rate for associates B STEP 2 INPUT INFORMATION REGARDING BILLING RATES 1 In the green textboxes input the firm s current billing rates for associates partners and other timekeepers 2 At the bottom of the billing rate grid for associates partners and other timekeepers you can also input an assumed billing rate increase This rate increase is in addition to what is shown for the firm s current rate schedule Please note that a decimal input such as 0 05 in this textbox will be interpreted by the model as 0 05 and not 5 3 Alternatively if the assumed billing rate of inflation is set to zero associates will be advanced to the next rate year to year but the rate schedule itself wi
13. that is in the green shaded textboxes For a detailed description of the contents and organization of the Profitability Report page please see section III below Changes in the Assumptions for Years 1 to 5 Alternatively you may want to manually modify numbers in one or more of the forecasted growth cells Year 1 to Year 5 Such changes will only affect the cells that are modified Additionally the revenues and profits page labeled Profitability Report will not update automatically To update the revenues and profits page after modifying the contents in any of the growth cells click the Profitability Report button The base assumption textboxes green shaded areas will accept any numeric value including decimals and numbers whose digits are separated by commas If any of the base year assumption textboxes becomes blank or contain a non numeric character the model will prompt the user to enter a numeric value For example if a user types q5 in setting the attrition rate instead of 15 a message box appears that says Please Enter A Numeric Value see below Click OK and then enter a numeric value in the relevant textbox Microsoft Excel Please Enter 4 Numeric Yalue II Revenue and Cost Assumptions Page 1 Assumptions of Model A STEP 1 INPUT NUMBER OF ATTORNEYS AND OTHER TIMEKEEPERS l At the outset the base year green textboxes will be pre populated with numbers of associ
14. y number of partners and associates for a given year but not other timekeepers We also provide total overhead for each year in U S Dollars The model then reports resulting total profits which equal total revenue minus associate and other timekeeper salaries overhead and first year associate recruiting costs This is the amount that remains for distribution to partners Finally the model reports average profits per partner and average profits per attorney e Average profits per partner Obtained by dividing total profits by the number of partners for a given year e Average profits per attorney Obtained by dividing total profits by the number of partners and associates for a given year but not other timekeepers Each time you click the Modify Assumptions button on the Profitability Report page the model switches back to the Assumptions page where the user may modify any of the existing revenue and cost assumptions or initiate an entirely new scenario Any time you want to save an entire report including whatever final assumptions you had input click the Save Report button At that point a new excel workbook will be activated You will be prompted to specify the location in which to save the report file This file will contain two sheets the assumptions you input as well as the revenue and profits report If you want to compare different scenarios save the relevant pages before you proceed with al
Download Pdf Manuals
Related Search