Making 2 graphs in (tableau software) about Medical Care database provided in excel

Chats idea:

State level expenditures: timeline 2000-2013 totals and how the increased & changed over time

Map for the states by county with highest veteran rate. Comparing unique patient

GOE & Construction (Dashboard)

AZ chart, compares other areas with same population

Progression of time???? Increase in veterans etc.

Data link:

https://catalog.data.gov/dataset/geographic-distribution-of-va-expenditures-fy2013

data descriptions and information

Unique patient: “A unique patient means that even if a patient is seen multiple times during the EHR reporting period they are only counted once."

GOE: n expense incurred in carrying out an organization's day-to-day activities, but not directly associated with production. Operating expenses include such things as payroll, sales commissions, employee benefits and pension contributions, transportation and travel, amortization and depreciation, rent, repairs, and taxes.

Introduction

Each Fiscal Year (FY), the Department of Veterans Affairs’ (VA’s) Office of Policy and Planning (OPP) publishes the Geographic Distribution of VA Expenditures (GDX) report. This report provides the estimated dollar expenditures for major VA programs at the state, county, and Congressional District levels. Expenditure data are grouped by the following categories: Compensation and Pension, Education and Vocational Rehabilitation and Employment, Insurance and Indemnities, Construction, General Operating Expenses, Loan Guaranty, and Medical Care. Veteran population estimates at the state, county and Congressional District level and the number of unique patients who used VA healthcare services are also included in the report. The following information provides a brief description of the data included in the report, the methods used for data collection, and the sources of the data.

Veteran Population

As of FY 2012, the estimated Veteran population data by state and county are based on the VA VetPop2011 model. This model is VA’s source for official estimates and projections of the Veteran population.

Compensation and Pension

The Compensation and Pension (C&P) category includes expenditures for the following categories: compensation payments, pension payments, Dependency and Indemnity Compensation, and burial allowances. Most expenditures reported in this category reflect monthly payments made to individuals. Retroactive payments and one-time payments are also included. Data is obtained from USASpending.gov.

Education and Vocational Rehabilitation and Employment

Education and Vocational Rehabilitation and Employment (E&VRE) are separate programs, but are combined into one category for display purposes in GDX. Expenditures in this category include: automobile and adaptive equipment, specially adapted housing, Survivors’ and Dependents’ Educational Assistance (Chapter 35), Vocational Rehabilitation for Disabled Veterans (Chapter 31), Post-Vietnam Era Veterans’ Educational Assistance (Chapter 32), Montgomery G.I. Bill for Selected Reserves (Chapter 1606), Reserve Educational Assistance Program (Chapter 1607), Montgomery G.I. Bill (Chapter 30), and Post-9/11 Veterans Educational Assistance (Chapter 33). Expenditure data for E&VRE are also obtained from USASpending.gov.

Insurance and Indemnities

The data reported for this category are provided by the VA Regional Office and Insurance Center (RO&IC) in Philadelphia, Pennsylvania. This category consists of VA expenditures for death claims, matured endowments, dividends, cash surrender payments, total disability income provision payments, and total and permanent disability benefits payments. It includes Veterans Group Life Insurance, National Service Life Insurance, Service Disabled Veterans Life Insurance, United States Government Life Insurance, Veterans Reopened Insurance, and Veterans Special Life Insurance. It does not include Traumatic Injury Protection Under Servicemembers' Group Life Insurance, Family Servicemembers' Group Life Insurance, or Servicemembers' Group Life Insurance. The RO&IC provides OPP with monthly extracts of payments to recipients by zip code for the purposes of aggregating these monthly payments by state, county, and Congressional District.

Construction and Related Costs

The Construction expenditures category includes funding for Major Projects, Minor Projects, Grants for Construction of State Extended Care Facilities, and Grants for Construction of State Veterans Cemeteries. The source of the Construction data is the Financial Management System (FMS).

General Operating Expenses

General Operating Expenses (GOE) represent the costs necessary to provide administration and oversight for the benefits provided by VA. This includes costs for overhead and human resources. This category does not include payments made directly to beneficiaries. The source of the GOE data is FMS.

Loan Guaranty

Prior to FY 2008, Loan Guaranty expenditures were included in the E&VRE category. Currently, all Loan Guaranty expenditures are attributed to Travis, Texas, where all payments are processed. VA will continue to improve data collection for future GDX reports to better distribute loan expenditures at the state, county and Congressional District levels.

Medical Expenditures

Medical expenditures data come from both the Allocation Resource Center (ARC) and FMS. The specific programs reported for this category are: medical care, General Post Fund, medical and prosthetic research, and miscellaneous accounts.

Unique Patients

The count of unique patients is based on the home residence of the patient. The number of patients, along with associated county and Congressional District codes, are provided by the ARC.