A pharmaceutical company is developing and planning to launch a new oral drug for cancer. The product manager needs a 10 year forecast of net sales to make decisions about the product's potential sales. Your assignment is to complete a 10 year forecast of Net $ of pharmaceutical sales using the Excel Spreadsheet approach. The Chain Ratio Spreadsheet Shell is in the folder in Moodle. This is a population based forecasting method using the assumptions below and the Excel file provided. Some assumptions are constants and some are variables. The assumptions are used to calculate intermediate and final values. If you get the first column correct, it's likely you have done the whole 10 years correctly. The assumptions are: US population - 335 million in year 202020 Growth rate for the population is 1% use this rate to calculate existing plus added population each year Prevalence of the disorder (# of patients treated) = 12% of the population existing in a year Use Prevalence and US Population to calculate # of Existing Patients Diagnosed and Treated Incidence of the disorder - 7% representing newly diagnosed patients each year Use Incidence and US Population to calculate # New Patients Diagnosed and Treated in a year Total # of Patients Diagnosed and Treated with RX-Existing + New Patients Diagnosed and Treated in a year # of Patients Captured in Market Share = 25% of treated patients are getting your pharmaceutical Calculate Gross & Captured Per Treated Patient per year based on $35 per month gross S per Patient Calculate Gross $ Lost Due to Discounts and Chargebacks at 2% of Gross Sales Use Gross $ Captured and Gross $ Lost to calculate Total Net $ for your product in each year Be sure to format your row cells properly. S are not #. Create your cells using the correct formulas etc. to do calculations. Ask if you have questions and be sure to write a statement of what the forecast means to a AutoSave ON H Week 2 Chain Ratio Spreadsheet Model Shell 2020 - Compatibility Mode Excel Search File Home Insert Data Review View Help Arial Draw Page Layout Formulas 12 AA ===> A EE General Insert 52 Delete Format WE 47 Paste BIU $ % 8-98 Conditional Format as Cell Formatting Table Styles Styles Sort & Filter Clipboard 15 Font Alignment Number Cells Editing . 310 =35*88 J M D E F G H K L 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 1 Population Based Forecast 2020 US Population (million) 335 3 Population Growth 1% 4. Prevalence 12% 5 # Existing Patients Diagnosed and Treated 40.2 6 Incidence 7% 7 # New Patients Diagnosed and Treated 23.45 8 Total Patients Diagnosed and Treated with RX 63.65 9 # of Patients Captured in Market Share 25% 10 Gross $ Captured Per Treated Patient 2227.75 11 Gross $ Lost Due to Discounts and Chargebacks 111387.5 12 Net $ for Your Product -109160 13 14 15 16 17 18 19 20 21 Model HE Ready
