Calculate the year-over-year change percentage

Assignment Help Supply Chain Management
Reference no: EM133555208

General Instructions:

You'll use the dataset from "Mid-Term_Project_Material.html/.ipynb" (Syntegra datasets - Medicare CCLF Claims), that have been cleaned and prepared to analyze and address the key business questions below.

Business Problem Statement:

The Cardio Vascular Metabolic (CVM) diseases are a cluster of conditions and risk factors that increase the likelihood of developing heart disease, stroke, and/or type 2 diabetes. It is characterized by a combination of several interconnected metabolic abnormalities, including Obesity, Insulin Resistance, High Blood Pressure (Hypertension), Dyslipidemia and Elevated Fasting Blood Sugar. The presence of three or more of these factors is typically used to diagnose this metabolic syndrome. Most common CVM diseases and conditions include heart diseases like coronary artery disease, Stroke, Type 2 Diabetes, Atherosclerosis, Non-Alcoholic Fatty Liver Disease (NAFLD), Chronic Kidney Disease (CKD), Sleep Apnea, etc. Some popular brands in the market for CVM diseases are Xarelto, Touejo, Lantus, Eliquis, Jardiance, Crestor, Metoprolol, Plavix, etc.The leadershipof a Pharmaceutical Company is seeking to gain insights into the trends and landscape of medical procedures conducted in hospitals and clinics. As a member of the Commercial Analytics team, your role is to furnish the leadership with valuable insights, addressing the following Key Business Questions (KBQs) that will inform the decision-making and strategy development of the CVM Sales & Marketing leadership team.

You will need to analyze the Syntegra Medicare CCLF Claims dataset to help answer the following Key Business Questions:

1. Based on the trends for the share of CVM claims as a percentage of total claims over the years 2016 through 2018, what are some business insights you can gather? What are some additional analyses you could do based on these trends?
a. Hints:
i. Calculate the share of CVM claims from years 2016-2018 using top 100 HCPCS/CPT codes
ii. Reference the Medicare Analysis discussed during class (Top Procedure + Analysis #1)
iii. CVM includes HCPCS/CPT codes related to Cardiac field/procedures
iv. Think about business insights in terms of key decisions that sales and marketing leadership needs to make, e.g. sales force, sales force tactics (how to word the messaging sent out to HCPs), patient experience
b. Expected Technical Output:
i. A 100% stacked bar graph
ii. Each bar represents a year
iii. Each bar consists of percentage of CVM and non-CVM claims

2. Evaluate the HCP behavior in context of claim volume from 2016-2018. How many HCPs are submitting 1 CVM claim; how many HCPs are associated with more than 10 claims, etc.? Once you perform this analysis, explain how this trend can influence the sales force deployment. That is, how would you segment the HCPs and how would you allocate In-Person (sales force) vs Non-Personal Promotions (NPP, i.e. Emails, Social Media, Digital etc.) efforts?
a. Hints:
i. Find the healthcare provider claim count distribution for the CVM claims identified above in increments of calendar quarter
ii. Reference the Medicare Analysis discussed during class (Analysis #3)
iii. Each healthcare provider has a unique NPI ID; note that Medicare datasets may have multiple columns for NPI IDs including ones for the facility and operating providers that should be not accounted for
iv. Use the following segmentation strategy:
1. Segment #1. Disease Aware (HCPs with 1 CVM claim)
2. Segment #2. Trialists (HCPs with 2-4 CVM claims)
3. Segment #3. Rising Stars (HCPs with 5-9 CVM claims)
4. Segment #4. High-Volume Prescribers (HCPs with 10+ CVM claims)
5. How would you determine which sales tactic is best for which segment?
b. Expected Technical Output:
i. A stacked Bar Chart
ii. Each bar represents a calendar year (e.g. 2016)
iii. Each section in the stacked bar stands for one segment and its value in the bar is the number of providers for that segment

3. Evaluate the Patient Age demographics in the context of claim volume from 2016-2018. Bucket the patients into groups based on their age and explain the trends. How would youposition the Marketing Budgets and the Promotionswith respect to the changing landscape of the CVM claims and the respective patient segments?
a. Hints:
i. Investigate the trend for patient age demographics for the CVM claims in years 2016-2018
ii. Utilize the solution for Q2
iii. To calculate the patient age, create a patient age column and subtract the year of patient birth date from the year of the claim date so you have the patient age at the time of the claim.
iv. Use the following Segments for the patient age:

1. Segment #1:18 - 59 (Patients with age from 18 - 59)

2. Segment#2:60 - 69(Patients with age from 60 - 69)

3. Segment #3:70 - 79 (Patients with age from 70 - 79)

4. Segment #4:80+ (Patients with age 80 and above)
v. Count the number of claims corresponding to the patients within each bucket by year.
vi. Also, calculate the year-over-year change percentage in the claim volume for each age group.
vii. Which marketing and promotion strategies would focus on the patients?
b. Expected Technical Output:
i. A stacked Bar Chart

1. Each bar representing a year (2016 - 2018)
2. Each section in the stacked bar stands for an age group and its value in the bar is the number of claims from the patients in that age group
ii. A table with year-over-year change percentage in the claim volume for each segment

Scoping the Project The write-up includes an outline of the steps taken in analyzing the data set during the project. The purpose of the final answers to key business questions (above 4) is made explicit.

Attachment:- Instructions.rar

Reference no: EM133555208

Questions Cloud

Provide two examples of current research on interventions : Provide two examples of current research on interventions to reduce or eliminate compulsive/impulsive behavior related to addiction.
How to address the patients current drug therapy plans : For this Discussion, you will be assigned a patient case study and will consider how to address the patient's current drug therapy plans.
Discuss her investments and her current asset allocation : discuss her investments and her current asset allocation. Her planner feels that there is a significant possibility that interest rates are poised to rise
Review community state or federal policy evaluation : Review community, state, or federal policy evaluation and reflect on the criteria used to measure the effectiveness of the program or policy described.
Calculate the year-over-year change percentage : Calculate the year-over-year change percentage in the claim volume for each age group - Investigate the trend for patient age demographics for the CVM claims
Which accounting tool would you use to query databases using : which accounting tool would you use to query databases using specific statments to investigate business assumptions such as show me sales of insurance by
Write the goal statement utilizing s.m.a.r.t. objectives : write the Goal Statement utilizing S.M.A.R.T. objectives (Specific, Measureable, Attainable, Relevant and Time Bound): What is in Scope? What is out of Scope?
Discuss example of research study in one of your literature : Discuss an example of a research study in one of your literature review articles that needed IRB approval and describe why IRB approval was needed in instance.
What is professional nursing standards in care of spiritual : What is the professional nursing standards in the care of spiritual, religious, and cultural influences that impact the health of clients.

Reviews

len3555208

10/25/2023 10:35:44 PM

See the instructions and documents below. Read through the instructions first. Use the Mid-Term Project Material as your starting point and answer the 3 Key Business Questions (KBQ''s), using the Syntegra Data Set and appending to the below HTML file. Submit your deliverable as an HTML file to Canvas

Write a Review

Supply Chain Management Questions & Answers

  Supply chain network design for cwc

This assignment explain the supply chain management process of cwc. What is the current annual supply chain cost?

  Criteria when making new location decisions

Identify a number of the typical criteria used when making new location decisions

  Draw the project network

Determine the mean critical path for Brent's job search process. What is the variance of the project duration?

  Compare and contrast economic - market, and relevancy

Please compare and contrast economic, market, and relevancy value.

  Brushing up on inventory control

Health system in Pennsylvania

  Supply chain strategy

Supply chain strategy

  What is the economic order quantity for fans in units

Nittany Fans of Lewistown, Pennsylvania, is a distributor of industrial fans used in plants, warehouses, and other industrial facilities.

  What factors reduce the capacity of the organization

What factors reduce the capacity of the organization to get its objectives?

  What is the total annual cost

What is the total annual cost

  Supply chain management questions

Briefly define the following two supply chain metrics: (i) inventory turnover ratio and (ii) supply chain velocity.

  Explain the supply chain management concept

Explain the supply chain management concept, Differentiate between public and private sector organisations in terms of their supply chain objectives and strategies

  How warehouses can add value in the supply chain

How warehouses can add value in the supply chain

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd