Instructions for a simple "what-if" scenario that illustrates the functionality in this free Excel-based Practice Model designed for dentistry
The Practice Model is a fictitious dental practice that contains 24 months of both financial and production data for a practice with 11 employees.
Business Scenario: An industrial park near this dental practice is attracting many large businesses and the practice owner feels this might be the right time to expand his business. The owner was already considering an additional associate dentist to help with an increasing number of patients serviced by the current dental staff. In addition to the associate dentist, the owner is also considering an additional hygienist. These two new employees will increase the production staff to eight. Therefore, the practice owner believes a dental assistant and one additional administrative employee may also be necessary. The office space he is renting can accommodate this expansion (which was a factor when he relocated to this location five years ago).
Average revenue in the past 24 months is approximately $110,000 a month. The practice owner has a current forecasted salary and bonus of $260,000 after taking the forecasted year-end profits as personal compensation.
The strategic financial issue in this example is: how much additional personal compensation can the practice owner expect to earn if his practice expansion plan is successful?
To run this what-if strategic scenario, you will work only with the following (3) model segments:
- Segment 3 – Manpower Input
- Segment 4 – Financial Planning
- Segment 5 – Financial Forecasting
(In this model, you can only enter data in Excel worksheets with yellow tabs.)
Before starting this example though the model, be sure to have a printed copy of these instructions in front of you.
Step 1 - Go to model segment 5 - Financial Forecasting and note the current monthly and annual forecasts in columns K and L. The monthly and annual revenue forecasts are $148,259 and $1,779,106 and the profit forecasts are $9,791 and $117,497 respectively. (This current forecast already includes all four new employees contemplated in our scenario.)
Step 2 - To calculate the incremental personal compensation that could be available to the practice owner through this proposed expansion, go to model segment 3 - Manpower Input and note that the current annual profit forecast of $117,497 (as explained in Step 1) also appears in cell R5.
In cell R6, enter the current practice owner's projected annual bonus of $115,691 that appears in cell J6. Next, change the projected annual bonus that appears in cell J6 from $115,691 too $231,509. (Note that the current annual profit forecast that appears in cell R5 has changed to zero.)
Step 3 - Enter $231,509 in cell R7 and the incremental personal compensation calculation (for this "what-if" scenario) of $115,818 will appear in cell R8.
To better understand and practice using this model, eliminate the four "what-if" employees, which resets the practice owner's forecasted bonus forcast back to $115,691.
Step 1 - Go to the model segment 5 - Manpower Input tab and enter "0" in columns H, J, and L for rows 40, 57, 77, and 78. (Once you view the "Manpower Input" tab screen design, you will quickly see that these are the cells that were used to enter the four what-if employees estimates for annual compensation to create the expansion scenario.)
Step 2 - Go to the model segment 4 - Financial Planning tab and enter 0 in cells N12 and N29. These are the production estimates used for the two employees (the associate dentist and hygienist). Note the green-colored cells to the right will clear.
What you have done is merely remove the four what-if planning employees from the projected monthly and annual income statement forecasts.
Step 3 - Return to the model segment 5 - Manpower Input tab and, in cell J6, change the practice owner's projected annual bonus forecast from $231,509 to $115,691. (Note the profit forecast that appears in cell R5 is zero. In theory, before the "what-if" scenario was entered into the model, the practice owner's annual bonus forecast in the model was set at the amount which resulted in his S-corporation Ordinary business income or loss (line 20 form 1120S tax return) to be zero. )
If you're feeling adventurous and removed the four planning employees, go back to the "Planning" tab and change some of the production forecasts for the current dentists and hygienists on staff. Then go to the "Forecasting" tab and see how these changes in production estimates for the current production staff affect the monthly and annual income statement forecasts. Then go to the "Manpower Input" tab and adjust the practice owner's annual bonus forecast until the annual income statement profit forecast is close to zero. (It's easy to get the profit to be exactly zero. Try it.)
The website does not require any contact information for this free practice model download. If you choose the "open" option after clicking the download link on the "Home" page, the model will open in an Excel browser format. If you save the browser or choose "save" after clicking on the download link, the file will be saved like any other Excel (.xls) file. The Excel browser format makes it very easy for someone who has never used Excel to review or play with the model.
This is an example of just one of the five important functionality features offered by this Excel-based model designed for dentistry.