Forecasting4

xlsx

School

Thompson Rivers University *

*We aren’t endorsed by this school

Course

6250

Subject

Finance

Date

Feb 20, 2024

Type

xlsx

Pages

1

Uploaded by AdmiralComputer13551

Report
0dd704995075589ca3e4433cfc2a8b925a6aac6f.xlsx: Bundy Pharma Page: 1 of 1 Bundy Pharmaceuticals Inc. Data Historical Sales NPV Discount rate 10% Year Unit (#) Sales Unit revenue $10.00 per unit sold 1 487,000 Plant construction cost 2 560,000 Fixed $5,000,000 3 601,000 Variable $10.00 per unit capacity 4 689,000 Fixed operating cost $1.50 per unit capacity 5 788,000 Variable operating cost $5.00 per unit sold 6 854,000 7 902,000 8 957,000 9 991,000 10 1,010,000 Step #1: Find the Best Forecast for the next 10 years (life of manufacturing plant) Note: Doing a 10 year forecast is questionable, especially when you are using years as a predictor but since have no other data, then using years is our only choice - Chart the past 10 years in the space below - Apply different types of trend lines make sure you display R squared and equation on the chart - On your chart below find the best trendline forecast. Make sure the R squared and equation is on the chart in textbox on your chart identify the best trendline to use for the forecast Step #2: # of Units sold is increasing trend - so we know that time series will not be the best forecasting tool Note: Since the sales are increasing then time series may NOT the best tool. But we will use it as it is fast. - So ignore this fact and run a 10 year Triple Exponential Time series forecast (Excel Forecast Sheet) - In the cell below what would be the # of units sold using Excel Time series Forecasting tool for years 11 to 20 you can just copy and paste as values from the forecast sheet Year Unit (#) Sales 1 487,000 2 560,000 3 601,000 4 689,000 5 788,000 6 854,000 7 902,000 8 957,000 9 991,000 10 1,010,000 Year 11 Year 12 Year 13 Year 14 Year 15 Year 16 Year 17 Year 18 Year 19 Year 20 Step #3: Now you have the Sales Forecasts for year you need to build a model to find the NPV with a initial capacity of 1 M units Note: format your number appropriately - no decimals needed Current selling price of Drug $10.00 per unit sold Fixed Operating Costs $1.50 per unit of capacity Variable Operating Costs $5.00 per unit sold Corporate NPV discount rate 10%per year Initial Plant Capacity = units Year 11 Year 12 Year 13 Year 14 Year 15 Year 16 Year 17 Year 18 Year 19 Year 20 Construction costs Forecasted # of Units Sold Actual # of units sold Sales Revenue Fixed Costs Variable Costs EBITDA NPV Step #4: Create a Data Table of NPV at various plant capacities Plant Capacity 900,000 950,000 1,000,000 1,050,000 1,100,000 1,150,000 1,200,000 1,250,000 1,300,000 1,350,000 1,400,000 Step #5: Use Solver to find Exact optimum plant capacity Optimum plant capacity Step #6: Create a chart of the NPV at various plant capacities in the space below. This is why you need the data table Chart MUST be client ready. Put a note on your chart as to the exact Optimum plant capacity to build (step #6) Bundy Pharmaceuticals Inc. (BPI) is investigating what happens when it shifts it drug manufacturing business from overseas contract suppliers to domestic manufacturing. Question: How big of plant to build (too small lose sales too big inefficient). Building the right sized plant is critical so you have to come up with the BEST possible forecast for # of units sold for years 11 to 20. Then you will build a discounted cash flow model using the NPV. Assume right now you are at the start of Year 11. Also assume the plant will be constructed at the start of year 11 and you will incur all the construction costs in year 11. Also in year 11 it will operate and produce the product The problem is that once you decide on the initial plant capacity it can NOT be changed To build this discounted cash flow model assume you plant has a capacity of 1 million units. Create the model based on this and find the NPV. Once you have tested and made sure it is correct then use a data table to find the NPV at various capacities from 900,000 to 1.4 million, increments of 50,000 units. The capacity with the best NPV is what you are looking for. History: Sales over last 10 years has shown slow growth as shown by table above. Revenue: - Current selling price of Drug = $10 / unit Costs: - Range of plant capacities considered 1.0 to 2.0 million units or!!!! year - Total costs of construction is made up of both fixed and variable construction costs and this equals $5 million + approximately $10 per unit of annual capacity (example 1.5 million unit plant = $5 million + $10 * 1.5 million = $20 million - Assume you will build and operate the manufacturing plant in the start of 11th year Once plant is built (start of year 11) capacity determines both fixed and OPERATING costs - Fixed Operating costs ~ $1.50 per unit of capacity - Variable Operating costs are $5.00 per unit of sales - Corporate policy for discount rate for NPV = 10% GOAL = Determine Best size of plant Actual # of units sold will be limited by Demand and plant Capacity We have not done this yet but do a quick Google for "how to do a data table in Excel" We have not done this yet but do a quick Google for "how to do NPV in Excel"
Discover more documents: Sign up today!
Unlock a world of knowledge! Explore tailored content for a richer learning experience. Here's what you'll get:
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help