Skip Navigation Archive: U.S. Department of Health and Human Services
Archive: Agency for Healthcare Research Quality
Archive print banner

Instructions for Using the Diabetes Cost Calculator for Employers

This information is for reference purposes only. It was current when produced and may now be outdated. Archive material is no longer maintained, and some links may not work. Persons with disabilities having difficulty accessing this information should contact us at: Let us know the nature of the problem, the Web address of what you want, and your contact information.

Please go to for current information.


About the Calculator
Information Needed To Use the Calculator
Getting Started with the Calculator
Navigating the Calculator

About the Calculator

The diabetes cost calculator was developed in response to a request from members of the Mid-Atlantic Business Group on Health for a tool that would allow its employers to demonstrate the potential savings that could be realized with improvements in diabetes care.  The calculator provides estimates of an employer's current diabetes-related medical costs and costs due to lost productivity.  The calculator also projects potential savings associated with improvements in hemoglobin A1c (HbA1c), a key measure of diabetes control, among the organization's employees and dependents who have diabetes. (HbA1c levels indicate how successfully patients are managing their diabetes, with lower levels indicating greater success.)

Activating Text-to-Speech Function for Users with Visual Impairments

Users with visual impairment may activate the calculator's text-to-speech function.  To turn on text-to-speech, press Ctrl-Shift-s from the Welcome screen of the calculator.  The keyboard shortcuts that users can activate from the Welcome screen follow:

Ctrl-Shift-s turns the text-to-speech on and off.
Ctrl-Shift-c starts a scenario (equivalent to clicking "1 run scenario").
Ctrl-Shift-r shows results (equivalent to clicking "2 View Results").
Ctrl-Shift-a shows additional data (equivalent to clicking "3 View Additional Data").

Return to Contents

Information Needed To Use the Calculator

In order to provide more precise estimates for individual employers, the calculator requires employers to enter basic information about their organizations.  The information that you will need to have to use the calculator is listed below.

The following are required inputs to the calculator:

  1. State.  If your organization operates in more than one State, you will need to create a separate scenario for each State.
  2. Industry. Choose from the drop-down menu the industry category that is the best match for your organization. 
  3. Number of employees by age group: 
    1. # employees ages 18-44.
    2. # employees ages 45-64.
    3. # employees age 65 and over.
  4. Sick leave policy.  To estimate costs of employees being absent from work due to illness (i.e., absenteeism), the calculator will ask if your employer provides paid sick leave.

In addition to the four items listed above, the calculator will allow you to enter other information about your organization if you have it.  If you have this information, the estimates that the calculator produces will be more precise for your organization.  However, if you do not have this information, the calculator will use default data based on your location, industry, and firm size. 

The following are optional inputs in the calculator:

  1. Number of covered lives with diabetes.
  2. HbA1c levels of covered lives with diabetes.
  3. Average hourly wage by age group.
  4. Percentage of employees who select family medical coverage.
  5. Total number of covered children or average number of children per employee with family medical coverage.
  6. Percentage of minority employees.
  7. Percentage of female employees.

Return to Contents

Getting Started with the Calculator

1. Check your security settings in Excel.  The calculator is powered by macros, which means you may need to modify your security settings in Excel® to allow the macros to operate.  From the toolbar in Excel, go to Tools > Macro > Security > Security Level.  On the Security Level tab, select a security level of Medium (Figure below).

Screen shot showing Security screen in Excel® with Security Level set to Medium.

Note:  The calculator was developed in Microsoft Excel® and operates with a Visual Basic interface.  Because the calculator is in Excel, it may not function for a user who does not operate in a Microsoft environment. 

2. Tell Excel® to trust access to Visual Basic Project. From the toolbar in Excel, go to Tools > Macro > Security > Trusted Publishers.  On the Trusted Publishers tab, be sure that the box next to "Trust access to Visual Basic Project" is checked.

Screen shot of screen in Excel® showing Trusted Publishers with Trust access to Visual Basic Project box checked.

3. Save the calculator and report templates to your desktop or to a file folder.  Before you start working in the calculator, save the calculator and the three report templates to your desktop or a file folder.  Any reports generated from the calculator will be saved to the same location.  If the calculator and the reports are not saved to the desktop or a file folder, the company-specific reports generated by the calculator will not save correctly.  Note that the report templates must be named as follows:

Note: At this time it is necessary to use Word® files for certain features of the calculator to work properly. HTML versions of each Word® file are provided. The HTML version includes field codes that indicate data that the calculator will use for the required output. Each of these field codes is followed by a description of the data required so that a user can manually enter data when using the HTML version.

4. Go to the Welcome Tab.  When you open the Excel® file, it should automatically take you to the Welcome tab, from which you enter the calculator.  If it does not take you to this tab automatically, you can switch to this tab by clicking on the Welcome tab at the bottom left corner of the screen.  To enter the calculator, select Run Scenario.

Screen shot of Welcome screen of diabetes calculator showing information about the calculator, options to run a scenario, view results, and view additional data.

5. Choose Run Scenario. The calculator will ask you if you want to Create A New Scenario or Load Scenario.  If this is your first time using the calculator, choose Create A New Scenario. You can save any of the scenarios that you enter into the calculator and return to them at any time.

Screen shot of Start screen, with options to create a new scenario, load a scenario, and delete a scenario.

Return to Contents

Navigating the Calculator

After you get through the pages with the required and optional user inputs described earlier, you will be asked to choose between two approaches to modeling costs and savings:

Screen shot showing options to choose a goal to reduce average HbA1c level or shift a portion of the population below a target HbA1c level, or to choose an intervention.
  • If you select Choose Goal, you can use the calculator to estimate the impact of improvements in diabetes control (as measured by blood glucose control) regardless of the intervention used to achieve the improvements.  You can choose one of two options: (1) reduce the average HbA1c level of a population with diabetes, or (2) shift a portion of a population with diabetes below a target HbA1c level. 
  • If you select Choose Intervention, you can select from five interventions that have been found to lead to improvements in diabetes control.  If you know the estimated costs of the intervention you select, you can enter that as well.  If intervention costs are entered, the calculator will provide a net savings that includes the cost of the intervention.  Note that you cannot select more than one intervention at a time.

Calculator Outputs

The calculator yields several outputs that are available in different formats.  After you choose an approach and an intervention or goal, the calculator will automatically take you to a Results page that summarizes key outputs of the calculator, such as estimates of:

  • Number of employees and dependents with diabetes.
  • Medical and indirect costs associated with diabetes.
  • Potential medical and indirect savings associated with improvements in diabetes control.

From this page, you can link to a number of charts and graphs with additional information that may be useful in interpreting the calculator results.  The page also has a link to an Executive Summary that presents your results in a brief narrative document.

Assumptions and Sources

Throughout the calculator there are help buttons that provide definitions, assumptions, and sources.  There is also an Assumptions and Sources Tab in the calculator, where you can find additional information about the calculator's methodology and the evidence on which it is based.  Wherever possible, citations to the original source are provided for users who would like more information.

Return to Contents

Current as of November 2008


The information on this page is archived and provided for reference purposes only.


AHRQ Advancing Excellence in Health Care