All large corporations have an all-encompassing business model – often in Microsoft Excel or similar software - that they use for strategic planning, operations analyses, and monthly reviews with top management. The Dental Practice Financial Calculator provides this same style of all-encompassing strategic business planning model for dentistry.
The Dental Practice Financial Calculator is a strategic financial management tool, not Practice Management Software (PMS). It doesn’t automate bookkeeping, set appointments, store images, or provide other dentistry services. Our strategic business planning model for dentistry serves a different purpose. It focuses on the financial aspects of practice management. No other dental-practice program addresses the full range of strategic financial issues such as calculating the fair market value of a practice or bringing in partner.
The model was designed to allow you to calculate the incremental impact to “personal compensation” for any “what-if” scenario you run. For example, the model lets you calculate the incremental personal compensation you could expect by increasing the service rate charged per hygienist visit by $10. (For an S-Corporation dental practice, this is the bottom line answer that any owner would want to know for any business scenario.)
The best way to understand the functionality that has been designed into our Excel-based model is to run the example given in our free Practice Model. It’s a great way to learn how to use both the model and Microsoft Excel. The practice model provides a functional example of a fictitious dental practice. It contains all the data you need to try every feature of the software, including the generation of multiple simulated strategic plans, forecasts, and what-if scenarios.
This free Practice Model contains 24 months of financial and production data for a dental practice of 11 employees. We do not require you to enter any contact information to obtain this free download. If you choose the “open” option after clicking on the download link, 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 a person who has never used Excel before to review or play with the model. There is no difference between this file and any other Microsoft Excel file you have saved on your computer. You can have as many copies of the model as you want by saving the Excel file using different names.
When using the model for an important strategic decision, such as comparing scenarios, save each option as a separate file so you can go back and review each scenario and document it. Keep in mind, every month of new data you enter updates the entire model to the last period that has been entered. This is why time period files related to important prior strategic decisions should be saved for historical documentation. This is how these all-encompassing business models in Microsoft Excel are used by large corporations.
Below is a brief description of each model segment. Excel files are known as workbooks. Each Excel workbook (.xls) file can contain a large number of what is referred to as worksheets. The sheet tabs that appear at the bottom of the screen are color coded and contain the name and number of each model segment.
There are 20 worksheets in this model that contain graphics or reports. By design, the only way to navigate or change the view of each screen in each segment tab is by using the scroll bars. Use your mouse to click on the yellow cells in any yellow-colored segment tab to enter or change data. Each screen has been carefully designed for optimal presentation of data. In most model segments that contain graphics or reports, numbers are rounded in thousands for easy review. These model segments also separate the conventional income statement into four major activity-based categories. (Explained in details in the Financial Forecasting and Financial Forecasting worksheets.) The model was designed to enable you to quickly understand the financial drivers that affect the profitability of the practice and spot unusual items that may require further investigation.
The segment tabs (worksheets) in the model fall into four major categories:
In the first category there are three primary input segments - color coded in bright yellow - that are used to enter practice data into the model. The financial and productivity input segments require updated data each month to keep the model current and up-to-date. Once the manpower input segment is populated and reconciled against payroll records, this segment only requires updating when payroll changes occur.
The second category is the model segments that contain yellow input cells that perform various what-if analyses. (These segments are colored coded in pale yellow segments tabs.)
The third category of model segments - which do not contain yellow input cells – are colored blue. These segments contain purposeful graphics or reports that do not require any input. Throughout the model, the mouse can only be used to click on yellow input cells. The scroll bars are the only way to navigate or change the view of the screen.
The fourth and final categories of model segments are colored white. These segments contain instructions or information relating to the model. They do not contain any yellow input cells and the scroll bars are the only way to navigate or change the view.
1 - The Financial Input segment uses the same income and expense classifications (and line numbering) used by the IRS in Form 1120-S U.S. Income Tax Return for an S-Corp. From a big-picture perspective, this allows you to classify and record expenses using the same categories required by the tax filing.
The data you enter into the financial input segment creates four unique income-statement reports that provide a better methodology for reviewing practice financials. The reports separate the income statement into four major activity-based categories so you can quickly understand the financial drivers that affect the profitability of the practice and spot unusual items that may require further investigation.
There are two methods to populate the financial input segment. You can use practice data from your practice management software and related financial statements. Or you can use a second methodology that eliminates the needless and costly work to generate monthly and annual financial statements (whether they’re prepared by an accountant or using QuickBooks or other financial software).
With this simpler methodology to close your books you can eliminate the need for financial statements. Best of all, your practice financial data will be right where it should be – in an Excel-based model designed for dentistry. (This second method is explained in full detail in the Learn How model segment.)
2 - The Productivity Input segment collects information about practice productivity in just three data fields for each dentist or hygienist each month. These three data fields should be easily attainable from any practice management software solution.
3 - The Manpower Input segment collects salary information for employees and lets you perform what-if analyses for labor planning. This enables you to fully understand the financial impact of adding new production or administrative employees. You can add new employees or add what-if employees for practice planning. This helps you understand the potential financial impact of new production or administrative employees - before they are hired.
4 - The Financial Forecasting segment enables you to compare historical data against your current run-rate model and projected financial forecast. These detailed statistics help you make crucial practice decisions and get a clearer view of the future. You can also generate what-if analyses that simulate various planning strategies simply by changing any variable (e.g. patients per day, billing rates, practice expenses, etc.). The Forecasting module displays income statement results for seven practice scenarios. Each scenario is presented in an activity-based format for easy identification of major trends.
5 - Production Planning -- Perform what-if analyses for existing employees and fully understand the financial impact of new production employees. You can model various productivity outputs for your existing employees and what-if employees that you add in the Manpower Input module. The productivity scenarios you create do not change the current run-rate projections - only the projected forecast.
6 -The Trend Analysis feature helps you put your current-year financial results in perspective by comparing them to your three prior years of financial results. One key metric: the percentage of revenue for each activity-based expense category. This helps you understand how current financial results compare to your recent history. This software segment includes simple instructions to pull your prior years’ financial data from your Form 1120-S U.S. Income Tax Return for an S-Corp.
Below is a summary of the four additional software segments that allow you to maintain or create a balance sheet.
7 - Balance Sheet – You don’t need to maintain a balance sheet to use this model. There are also no tax-reporting reasons to maintain a balance sheet. Believe it or not, it’s very easy to create a balance sheet using our unique method that gives you the same exact balance sheet positions that you’d generate using QuickBooks or similar financial accounting software. (It is a good general practice to create a balance sheet at the end of each year.)
8 - Trial Balance – The trial balance calculation is only useful if you are maintaining a balance sheet in this model. All accounts in the chart of accounts are known as the trial balance. All account balances in this statement of accounts must total to zero. If the trial balance does not total to zero, there is a mistake that must be adjusted. This segment in the model is a calculation that allows you to determine if your trial balance accounts total to zero.
9 - Journey Entry Register – This segment allows you to maintain a record of transactions that normally fall outside the scope of the day-to-day operations of a dental practice. If you are maintaining a balance sheet, you should record journal entries as an audit trail to prove some balance sheet positions (e.g. a debit or credit transaction made to an equity account).
10 -Tax –IRS form 1120-S -In addition to the income statement, balance sheet, and trial balance, the DPFC 2011 also creates a populated copy of your annual Form 1120-S U.S. Income Tax Return for an S Corp. All the figures in this populated Form 1120-S represent your current year-to-date income statement.
As explained above, the data entered into the financial input segment creates the following four additional unique income-statement reports that provide a better method for reviewing practice financials. The reports separate the income statement into four major activity-based categories so you can quickly understand the financial drivers that affect the profitability of the practice and spot unusual items that may require further investigation. The data in all four reports is expressed in thousands ($ 000s) for easy reviewing.
11 - The Activity-Based P & L separates the conventional income statement into four major activity-based categories. This report enables you to quickly understand the financial drivers that affect the profitability of the practice and spot unusual items that may require further investigation.
12 - Dashboard (Stoplight) Graphics - On one screen, you can view six key graphics that alert you to any variances that must be addressed. There is a graph for all four activity-based categories, and a similar graph for profit and cash in the bank. All graphs highlight the same information for each respective category.
13 - Percentage Graphics – Activity-based categories are expressed as a percentage of dental service $s provided. This allows you to quickly see expense trends and monthly variances.
14 - The Cumulative Income Statement Graphic shows a graphical view of the cumulative dollars and the percentage of (revenue / collections) dollars consumed for each activity-based category.
The next two model segments display historical data displayed in the Production Planning segment in a graphical format.
15 & 16-Productivity Graphics - Dentist and hygienist productivity appear clearly in graphical form. These reports help you better understand your personal productivity and compare the performance of other professionals in the practice. They also give new practice owners (who may have less employee management experience) a tool to discuss productivity with employees. (The model generates a second graph for hygienists using the same production metrics.)
The next four model segments fall outside the scope of the day-to-day business of owning a dental practice. The simple terminology in the FMV tabs is intended to be a teaching tool and reference guide to define commonly used financial formulas used in most FMV calculations.
Beyond the rich functionality, the model can actually serve as an excellent tool for learning practice-management concepts. The terminology throughout the model has been carefully chosen and structured as concisely and as clearly as possible to explain what you need to know. In clear, understandable terms, the model includes explanations of financial formulas and terminology that you will need to understand when buying, selling, or bringing a partner into a dental practice.
Today’s dental students will most likely have much better computer skills than the dentists from whom they will be buying their practices. These new dentists will also most likely have some knowledge of Microsoft Excel. In the next 10-15 years, supply will greatly exceed demand for dental practices. Getting to know how to use the free practice model on the website will give these new dental entrepreneurs a significant advantage at the bargaining table when they purchase a practice.
17 - The Fair Market Value Appraisal (FMV Tab 1) is divided into three tabs for easy viewing and printing. This valuable document estimates a dollar value for the business and can also be used as a financial document for a potential buyer to secure financing to purchase a practice. When viewed or printed, the information in these three tabs simplifies various business transactions or legal issues, such as assigning a value for a practice for pending partnerships or other practice buy-in agreements. This document could be used in wills, estate plans, divorce settlements, or other disputes. Our team devoted many hours reviewing the financial documentation that judges use when deciding how to value small businesses.
18 - Fair Market Value Appraisal (FMV-Tab 2)
19 - Fair Market Value Appraisal (FMV-Tab 3)
20 -The Partnership Calculator presents users with a choice of three easy-to-understand, logical formulas for compensation and profit distribution. In other words, this is where partners go to understand and agree upon compensation and profit distribution. These methods simultaneously encourage individual productivity.
The home page of the website includes three important links. The first offers a free download of the Practice Model. (The website does not require any contact information for this free download.) A second link displays instructions that explain the second methodology to populate the model that eliminates the needless and costly work to generate monthly and annual financial statements. The third link displays instructions for a simple what-if scenario (using the Practice Model) that illustrates the functionality of this Excel-based model designed for dentistry.