The HIV/AIDS Bureau (HAB) administers the Ryan White Comprehensive AIDS Resources Emergency (CARE) Act.  Enacted in 1990, reauthorized in May 1996 and in 2000, the CARE Act is the largest source of Federal funding specifically directed to provide primary care and support services for low-income, uninsured, and under-insured persons living with HIV disease.  Under Title II of the CARE Act, formula grants are awarded to States and eligible U.S. Territories to improve the quality, availability, and organization of HIV health care and support services.  In addition to other specific service programs, Title II funds AIDS Drug Assistance Programs (ADAPs).  ADAPs provide medications to low-income individuals with HIV disease who have limited or no coverage from private insurance or Medicaid, in all 50 States, the District of Columbia, Puerto Rico, the Virgin Islands, and Guam.

HAB's Division of Service Systems (DSS), AIDS Drug Assistance Program Branch (ADAP), monitors all 54 State ADAPs and coordinates all technical assistance and training activities associated with the ADAP program. 

CARE Act programs and ADAPs specifically are responsible for complying with an array of legislative and program requirements that include planning, budgeting, formulary review, cost-effective purchasing and delivery system of drugs, coordination of activities with other CARE Act and HIV/AIDS‑related services, evaluation of program activities and outcomes, and data reporting.  In addition, the HIV/AIDS Bureau has identified the following four priority areas that CARE Act programs face in coming years:

• Better serving the underserved in response to the HIV/AIDS epidemic's growing impact among under-served minority and hard‑to‑reach populations.  This requires programs to assess the shifting demographics of new HIV/AIDS cases in their area and adapt/change care systems to the needs of emerging communities and populations.
• Ensuring access to existing and emerging HIV/AIDS primary medical care that can make a difference. The quality of HIV/AIDS medical care, including combination antiretroviral therapies and prophylaxis/treatment for opportunistic infections, can make a difference in the lives of PLWH.
• Changes in the health care delivery system and the role of CARE Act services. Programs need to consider how CARE Act services are utilized in filling gaps in care, including coverage of HIV/AIDS?related services within managed care plans (particularly Medicaid managed care) and coordination of CARE Act services with other funding sources.
• Documenting Outcomes. Policy and funding decisions at the Federal level are increasingly being determined by outcomes. Programs need to document the impact of CARE Act funds on improving access to quality care/treatment along with the areas of continued need. Programs also need to ensure that they have in place quality assurance and evaluation mechanisms to assess the effect of CARE Act resources.

In order to use the ADAP budgeting tool it is necessary to have at least Windows 98 for the operating system for the computer and Microsoft Excel 95.  When the file is opened, some of the pages are read-only.  This is important because information should not be entered on those pages.  When beginning to use this tool, it is best if 18 months of data has been entered.  Currently all of fiscal year 2001, and part of fiscal year 2002 should be entered with 15 months of data in total.  The program will work with 2001 funding only however, the data will be more complete with 18 months of data. 

Before opening the HAB Budget Forecast Worksheet for the first time, Microsoft Excel’s security level must be set to Medium.  The security level is set in Excel on the Security Level tab (Tools menu, Macro submenu, Security command).

When opening the file for the first time the zip file must be opened.  Your computer system must have Winzip or PKUnizip to open this file.  If a user does not have this software an evaluation copy can be downloaded from http://www.winzip.com/.   A user can click the right button of the mouse when the file is highlighted.  One of the choices on the drop down menu is Extract To… where the user can select where the newly expanded file will be set up.  Once the file is unzipped, selecting it will open it.

 Selecting the file the user double clicks the Excel file name to open the Excel spreadsheet.  A message box will appear advising you that Macros will be used.  Click the ‘Enable Macros’ button.  This will allow many of the features available in the spreadsheet to work.

 After clicking the ‘Enable Macros’ button, the message box will disappear and another message box will appear asking the user if they want to update linked information from another worksheet.  This message box is generated because there are some cells in the workbook reference the next Fiscal Year that is not yet in the system.  The user should simply click ‘No’ to allow the workbook to continue opening.

 When the worksheet opens, a user can move around by using either the tabs on the bottom of the page or the special HAB menu choice on the menu bar.

 Menu Bar Example

 Tabs Example

The Actual page is the summary page that will give the user a quick overall view of the data entered in the worksheet. This is also where the data is entered that is in the budget when the fiscal year begins. A user will be able to change the numbers in the Budget: section to show the actual dollars spent, and this is one of two places where the user enters in information.  This will affect the data throughout the workbook, especially on Reported. 

On the actual page under fiscal year 2001, enter the projected Title II base funds.  These are the funds from the Title II base award that are being contributed to the AIDS Drug Assistance Program (ADAP).  Then there is the ADAP earmark, the state contribution, and a Title I EMA contribution if Title I contributes dollars to ADAP, 340B rebate, supplemental award for severe need, and other funds received from another Ryan White program. For example: if there was an arrangement with Title III.  The other category would be other additional funds.  For example: If the state has a walkathon, an AIDS ride, or some type of private funding for the program.  By entering that data, the program knows what the anticipated fiscal year funding is going to be in that year.  This is not a projection of expenses but looks at the starting budget.

The Actual page includes a Quick Chart feature. This will give a representation of the contribution to the ADAP by category for each fiscal year.  This tracks how each one of the categories changes each fiscal year.  The Quick Chart can act as a handout to show how the state ADAP earmark is increasing or if it begins to be flat from spending year after year. It could also depict how a states contribution have increased, decreased, or remained flat over the years. Quick charts can be pie or bar charts.  To return to the Actual page, click the Actual worksheet tab.

 Quick Chart Example

 On the reported page there are four areas; budget, actual expenditures, total actual expenditures and forecasted expenditures.

The forecasted values for fiscal year 2001 will equal the actual values because by end of the year, the forecasted values are going to be the same as the actual.  As the data is entered each month, it will re-write and overwrite the forecasted expenditures.

For fiscal year 2002, the actual expenditures will be lower than the forecasted expenditures.  This is because the actual expenditures are going to be throughout the year up to a certain point.  If April, May, and June are entered, then only three months of actual expenditures are reported.  However, the program is going to anticipate the trends continuing forward and therefore the forecasted expenditures will be complete for the entire fiscal year.  This will allow the user to anticipate for any month in the year, the anticipated remaining dollars, or the deficit, depending on the amount being spent going into the year.  It can be corrected on a month-by-month basis.

 On the bottom of the page are several important notes. The purpose of the notes is to clarify how the percentages are noted.  They are:

• The anti-retroviral medication
• How a percentage is calculated
• How the percentage is calculated for opportunistic, administrative, planning and evaluation, insurance, quality management and flexibility. 

When looking at anti-retroviral percentage, this is the percentage of anti-retroviral cost, over total drug cost.  The administrative cost must stay within 10 percent. Currently it is slightly over 10 percent.  Depending on which year for actual expenditures, the administrative cost represents a total in relation to the total ADAP budget, of the ADAP earmark. Planning and evaluation, quality management and flexibility are also taken off the ADAP earmark.  Insurance can be off of the total ADAP dollars.

To help you capture a graphical representation of the data you are collecting on the reported page, a Quick Chart button is included.  Anytime you click on this button it will automatically create a bar graph of the data you have on the Reported page.

 Quick Chart Bar Graph

Also a pie chart can be automatically made for each Fiscal Year by clicking the Chart FYYearExpenses.  This button is available for all fiscal years, including any new additional fiscal years you add in.

Chart of FY Expenses

This spreadsheet allows the user the flexibility of selecting the way Rebates are recorded in association with drugs.  This can be selected in one of two ways.  The first way is select from the box on the Actual screen.  There are two check boxes in the upper right hand corner of the page, Manufacturer Rebates and Drug Rebates.  Clicking on either one of these will create a new worksheet, with its tab being located right after the Reported tab on the bottom of the spreadsheet.  This can also be selected by selecting the HAB menu option in the top menu bar.  Once the choice is made, the rest of the workbook is adjusted to show the selected rebate type in the worksheets.

Enter for each quarter, fiscal year the rebates by drug manufacturer.  The program will put the values on the reported page.  In the budget it will report it back to the initial budget, so that it continues to add and offset the expenditures throughout the year. If the actual page was changed to drug rebates, it will look at the drug by name and by quarter.  This information will change the rebate dollar amount on the reported page.

 Example of Mfr Rebate tab

 Example of the Drug Rebate tab

Rebate By Manufacturer
Since some states record the rebate information differently from others, this workbook has the flexibility to handle rebate information by the name of the manufacturer or the individual drug. Just enter the new value on the cell.

Example of a Manufacturer Rebate Page

Rebate By Drug Name
Rebates can also be tracked by Drug name.  A new drug can be added by going to the HAB menu item an selecting Add new drug.  This will  add  the new drug name to all future worksheets.  However, if an existing drug name is changed on a worksheet, the change will be reflected in that one worksheet only.  All future worksheets will have the original drug name.        

Example of a Drug Rebate Page

Also, you can add a new drug manufacturer or drug name to the workbook.  Select either “Add New Manufacturer” or “Add New Drug Name” from the “HAB” menu. Then follow the directions.  The user can review the Current Manufacturers list to verify the manufacturer does not exist in the workbook.  Then the user will type in the manufacturer name under Enter New manufacturer Name Below.  Finally the user will click on the drop down box arrow to Select a Starting Fiscal Year Below.   When the user is happy with the choices made they will click on the Save Manufacturer button.  The new manufacturer will be listed in all fiscal year pages from the selected year till the highest Fiscal year in the workbook.

Using the two drop down boxes select the correct Fiscal Year and Month, the user will click on the Open Worksheet button.  The selected worksheet will open and the new tab will be created at the bottom of the worksheet.   The user will enter the information in this one spot and the data will be populated throughout the worksheet.

For instructional purposes, April 2001 is the main page where information will be entered each month.  Additional columns or rows cannot be added to the workbook.  By adding column or rows, it will negate the other pages as far as any information being transferred.  This page will also not allow names to be written over. The only information that’s being taken from this page and shared throughout the workbook is the information that gets transferred in column B.

In column B, it is important to answer the unduplicated number of users on a monthly basis.  This will determine the relationship of the increase or decrease in drug costs by month.  It’s important to enter the values monthly.  Each time a value is entered for a drug on a monthly basis, it’s going to look at the number of unduplicated number of users for each one of those values.  When it is projecting forward, it’s going to base those projections on the projected unduplicated number of users.

The information for administrative, planning and evaluation, quality and flexibility must also be entered monthly.  If a budgeted number is entered, those projected values will go through the rest of the fiscal year and into the next fiscal years.  Once this occurs those values must be zeroed out on other pages.  The idea is that the administrative dollar will be entered and one twelfth of the amount that is going to be spent on administrative throughout the year.

Premiums, deductibles, co-payments values should be entered.  In the event that premiums and deductibles are entered, they will add up to the insurance total dollar amount.  However, if the value is changed and an insurance total dollar amount is entered, those pages are going to be transferred to the fiscal year summary pages.

Each anti-retroviral medication by drug name, the total cost, and the amount spent on each of the drugs for April fiscal year 2001 should also be entered.

The Other information, located at the bottom of the fiscal month page, is for information only. If the number of prescriptions is entered to determine the average cost per prescription, these values will not be tracked. The only information that is transferred to other pages is the information that is entered in column B. For example: Ten thousand entered as a value for unduplicated number of users. On the summary page for 2001 there are ten thousand unduplicated numbers of users. The information on fiscal year 2001 summary is not going to be projected, because the fiscal year 2001 summary is complete and available.  Projections will not begin until the fiscal year 2002 summary. 

The projection page is majority blue because the information that is in white has been entered.  Information that is blue is projected.  For May 2002 part is white and blue indicating actual and projected expenditures on the fiscal year 2002 summary page. This information is going to be included in the totals.  The totals for each category will be on the reported page.  All of the information will be in the actual expenditures.  The actual ends up in the total, fiscal year remaining for Actual. The actual expenditures will then begin to change each month.

The forecasted expenditures are going to look at the forecasted value throughout the entire fiscal year.  The fiscal year remaining after forecasted expenditures will indicate how much money is left over, how much of a deficit can be anticipated, regardless of the month in the fiscal year

The workbook currently goes up to the 2004 Fiscal Year, but the user has the ability to add a Fiscal Year.  Note: Prior to adding a new Fiscal Year, all worksheets except Actual, Reported and the FY200x Summary worksheets MUST BE HIDDEN.  If other worksheets are visible, the routine will not work.  Once it is verified that only the above worksheets are visible, the user clicks HAB on the menu, then selects the Add New Fiscal Year option.  The workbook will see what the highest fiscal year is and pop open a window that will advise the user what the next fiscal year tab will be.  The user will select Yes or No to continue.

Each fiscal year in the HAB Budget Forecast workbook will have its own worksheet.  To review that year, the user will click on the tab for that fiscal year at the bottom of the worksheet; or by clicking on the HAB menu option and moving the mouse over the Fiscal Year Summaries choice.  This will show the user another menu that has a list of all of the fiscal years in the workbook.  Select the fiscal year you want to open.  When it opens, some of the cells will have a blue background.  This is to quickly notify the user that the values in the cell are projected numbers, not actual numbers.  When actual numbers are recorded by the workbook, the worksheet will change the background to white.

The workbook is closed the same way any other Excel file is closed.  You can either click the X in the upper right corner of the screen, or you can select File then click on Close.  The workbook will ask you if you want to save your changes.  Click on the appropriate button for your choice and the workbook will close down.

Individual Fiscal Month of a Fiscal Year:
The Insurance field, found on cell B11, is a total calculation of cells B12, B13, and B14.  If a user enters a number into cell B11 the calculation will be overwritten, so make sure you enter numbers in the following fields.

The Antiretroviral Medication field, cell B19, is a total of the Necleoside Analogs (RTIs), Non-Necleosides(NNRTI),  Nucleotide (NtRTI), and Protease Inhibitors (PIs).

Also the Average Cost/Prescription will automatically average the amounts in the Total Cost by the Number of Prescriptions.          

Fiscal Year Summary Pages:
The monthly amounts for each of the fields, except for the Total field in the B column, are taken from the individual Fiscal Month sheets.  When information is entered on the monthly sheet, it is automatically brought over to the summary pages.  It is also at this point that the cell color will change from Blue, forecasted, to White, actual.

The Reported Summary Worksheet:
This spreadsheet is for viewing only; there is no data entry here because the values are derived from other sections of the workbook.  The BUDGET values are derived from the corresponding cells on the worksheet named Actual. The ACTUAL EXPENDITURES values are the totals of the corresponding cells on the appropriate Fiscal Year Summary worksheets.  The FORECASTED EXPENDITURES are the combined totals from each individual fiscal year summary page.
 

This process must be followed for each sheet, since removing protection is sheet specific.

First select the spreadsheet to remove the protection by selecting its tab at the bottom of the work page.   Then go to the menu bar on the top of the Excel program and click on the Tools option.  This will present you with a drop down menu.  Move your mouse down the menu until you get to the Protection choice, then hold the cursor over the choice.  A fly out menu will appear with about 3 choices.  The first choice is usually Unprotect sheet, click on this choice.  The Unprotect Sheet window will pop up with the cursor blinking in a text box, type in  hrsa  – all letters in lower case.   Then click the OK button.  The sheet is now unprotected and you may change anything on this worksheet.  To change any other sheet, just repeat the process.
SYSTEM REQUIREMENTS