Forecasting4
xlsx
keyboard_arrow_up
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
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
Related Documents
Related Questions
6.
%24
iz/attempt.php?attempt3D216068&cmid3D185308&page=D1
The manufacturing capacity of Jordan Company's facilities is 30,000 units a year. A summary of operating
results for last year follows:
Sales (18,000 units @
000'008
Variable costs
000'066
Contribution margin
000'018
Fixed costs
Net operating income
$315,000
A foreign distributor has offered to buy 12,000 units at $95 per unit next year. Jordan expects its regular
sales next year to be 20,000 units at $100 per unit. If Jordan accepts this offer for 12,000 units and rejects
some business from regular customers so as not to exceed capacity, what is the expected total net
operating income for next year? (Assume that the total fixed costs would be the same no matter how many
units are produced and sold.)
Select one:
$795,000
$735,000
Clear my choice
pe here to search
81
15
f4
f3
#
arrow_forward
5- X +
o.mheducation.com e
05 i
Saved
Help
Required information
[The following information applies to the questions displayed below.]
Data for Hermann Corporation are shown below:
Percent of
Sales
Per Unit
$ 125
100%
Selling price
Variable expenses
80
64
Contribution margin
$45
36%
Fixed expenses are $85,000 per month and the company is selling 2,700 units per month.
Required:
1-a. How much will net operating income increase (decrease) per month if the monthly advertising budget increases by $9,000, the
monthly sales volume increases by 100 units, and the total monthly sales increase by $12,500?
1-b. Should the advertising budget be increased?
Complete this question by entering your answers in the tabs below.
...
00
arrow_forward
HEFIVD853 Corp. sells a single product.
$15
$2.00
Selling price per unit
Variable expense per unit
(ID#52285)
Current Sales in units
14,000
HEFIVD853's current net operating income is equal to 0.11 of current sales.
Q: What is HEFIVD853's fixed cost?
A: $
< Prev
14 of 25
N-
MacBook Pro
arrow_forward
2.3
REQUIRED
Calculate the annual economic order quantity from the information provided below.
INFORMATION
The following details have been supplied by Yolo Limited for one of its products:
Monthly sales
6 000 units
Carrying costs as a percentage of the unit purchase price
10%
Purchase price per unit
R50
Cost of placing an order
R20
arrow_forward
(Related to Checkpoint 13.3) (Scenario analysis) Family Security is considering introducing tiny
GPS trackers that can be inserted in the sole of a child's shoe, which would then allow for the tracking of
that child if he or she was ever lost or abducted. The estimates, that might be off by 9 percent (either
above or below), associated with this new product are shown here: E. Since this is a new product
line, you are not confident in your estimates and would like to know how well you will fare if your
estimates on the items listed above are 9 percent higher or 9 percent lower than expected. Assume that
this new product line will require an initial outlay of $1.08 million, with no working capital investment, and
will last for 10 years, being depreciated down to zero using straight-line depreciation. In addition, the
firm's required rate of return or cost of capital is 9.7 percent, and the firm's marginal tax rate is 34
percent. Calculate the project's NPV under the "best-case scenario"…
arrow_forward
(Related to Checkpoint 13.3) (Scenario analysis) Family Security is considering introducing tiny GPS trackers that can be inserted in the sole of a child's shoe, which would then allow for the tracking of that child if he or she was ever lost or
abducted. The estimates, that might be off by 11 percent (either above or below), associated with this new product are shown here: . Since this is a new product line, you are not confident in your estimates and would like to know how well
you will fare if your estimates on the items listed above are 11 percent higher or 11 percent lower than expected. Assume that this new product line will require an initial outlay of $1.09 million, with no working capital investment, and will last for
10 years, being depreciated down to zero using straight-line depreciation. In addition, the firm's required rate of return or cost of capital is 10.3 percent, and the firm's marginal tax rate is 34 percent. Calculate the project's NPV under the
"best-case…
arrow_forward
(Related to Checkpoint 13.3) (Scenario analysis) Family Security is considering introducing tiny GPS trackers that can be inserted in the sole of a child's shoe,
which would then allow for the tracking of that child if he or she was ever lost or abducted. The estimates, that might be off by 11 percent (either above or below),
associated with this new product are shown here: E. Since this is a new product line, you are not confident in your estimates and would like to know how well you
will fare if your estimates on the items listed above are 11 percent higher or 11 percent lower than expected. Assume that this new product line will require an initial
outlay of $1.09 million, with no working capital investment, and will last for 10 years, being depreciated down to zero using straight-line depreciation. In addition, the
firm's required rate of return or cost of capital is 10.3 percent, and the firm's marginal tax rate is 34 percent. Calculate the project's NPV under the "best-case…
arrow_forward
amm. 255.
arrow_forward
Accouting
arrow_forward
CVP Analysis, *What IT?" AnalysisKevin Co. projected contribution-format income statement for the upcoming month is shownBelow Sales (500 units) $10000Variable expenses. 4000Contributions margin. 6000Fixed expenses. 1000Net operating income. 5000Required:a.) Compute the breakeven point in units.b) Compute the breakeven paint in dollars.c.) If the company wishes to earn a monthly target profit of $10,000, how many units must be sold each month?d.) Compute the company's margin of safety. State your answer in both dollar and percentage terms,e.) The company's manager thinks that adding a salaried sales staff member at a cost of 52,000 per month will increase sales by $4,000 per month. If he is correct, what will be the net dollar advantage or disadvantage of making this change?t.) Refer to the original data, the company's manager believes that a new production process will improve profitability. He plans to add new machinery that will cut variable expenses…
arrow_forward
Cost Planning for Product Life Cycle... 229
RM800
Revenue Costs
Research and development
Prototypes
Marketing
Distribution
Year 1
P 900,000
1,150,000
550,000
124,000
170,000
85,000
-0-
P(1,179,000)
Year 2
Year 3
P1,800,000
P2,000,000
-0-
-0-
30,000
200,000
300,000
600,000
20,000
P 650,000
10,000
260,000
410,000
700,000
10,000
P 610,000
Manufacturing
Customer service
Income
Required:
1. How would a product life-cycle income statement differ from this calendar-
year income statement?
2. Prepare a three-year life-cycle income statement for both products. Which
product appears to be more profitable?
3. Prepare a schedule showing each cost category as a percentage of total
annual costs. Pay particular attention to the research and development and
customer service categories. What do you think this indicates about the
profitability of each product over the three-year life cycle?
arrow_forward
Provide correct answer financial accounting
arrow_forward
Par
arrow_forward
Search or type a command
gement Accounting for KUBIM)
O $1.00
49. If the National Division of American Products Company had a turnover ratio of 4.2 and a margin
of 0.10, the return on investment would be *
(1 Point)
O 42.0%.
O 23.8%.
O 238.0%.
O 420.0%.
50. Seaside Company produces picture frames. During the year 190,000 picture frames were
produced. Materials and labor standards for producing the picture frames are as follows:
Direct materials (2 pieces of wood @ $2.25) $4.50
Direct labor (2 hours @ $10)
Seaside purchased and used 400,000 pieces of wood at $2.00 each and its actual labor hours
were 360,000 hours at a wage rate of $10.50. What is the materials usage variance? *
(1 Point)
$20.00
O $45,000 U
O $112,500 U
O $112,500 F
O $45,000 F
51. Connolly Company produces two types of lamps, classic and fancy, with unit contribution
margins of $13 and $21, respectively. Each lamp must spend time on a special machine. The
firm owns four machines that together provide 18,000 hours of…
arrow_forward
RDWMNE769 Corp. sells a single product.
Selling price per unit $12 Variable expense per unit $2.00 Current Sales in units 16,000 RDWMNE769's current net operating income is equal to 0.31 of current sales.
Q. What is RDWMNE769's fixed cost?
A. $
arrow_forward
_ units?
arrow_forward
nkt.3
arrow_forward
The following information pertains to Springfield Inc:
Selling price per unit
$50
Variable costs per unit
$30
Total fixed costs
$212,500
Tax rate
40%
The pretax break-even point in sales dollars is:
Select one:
O
O
A. $700,000
B. $600,000
OC. $531,250
O D. $427,000
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you

Essentials Of Investments
Finance
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Mcgraw-hill Education,



Foundations Of Finance
Finance
ISBN:9780134897264
Author:KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:Pearson,

Fundamentals of Financial Management (MindTap Cou...
Finance
ISBN:9781337395250
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning

Corporate Finance (The Mcgraw-hill/Irwin Series i...
Finance
ISBN:9780077861759
Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:McGraw-Hill Education
Related Questions
- 6. %24 iz/attempt.php?attempt3D216068&cmid3D185308&page=D1 The manufacturing capacity of Jordan Company's facilities is 30,000 units a year. A summary of operating results for last year follows: Sales (18,000 units @ 000'008 Variable costs 000'066 Contribution margin 000'018 Fixed costs Net operating income $315,000 A foreign distributor has offered to buy 12,000 units at $95 per unit next year. Jordan expects its regular sales next year to be 20,000 units at $100 per unit. If Jordan accepts this offer for 12,000 units and rejects some business from regular customers so as not to exceed capacity, what is the expected total net operating income for next year? (Assume that the total fixed costs would be the same no matter how many units are produced and sold.) Select one: $795,000 $735,000 Clear my choice pe here to search 81 15 f4 f3 #arrow_forward5- X + o.mheducation.com e 05 i Saved Help Required information [The following information applies to the questions displayed below.] Data for Hermann Corporation are shown below: Percent of Sales Per Unit $ 125 100% Selling price Variable expenses 80 64 Contribution margin $45 36% Fixed expenses are $85,000 per month and the company is selling 2,700 units per month. Required: 1-a. How much will net operating income increase (decrease) per month if the monthly advertising budget increases by $9,000, the monthly sales volume increases by 100 units, and the total monthly sales increase by $12,500? 1-b. Should the advertising budget be increased? Complete this question by entering your answers in the tabs below. ... 00arrow_forwardHEFIVD853 Corp. sells a single product. $15 $2.00 Selling price per unit Variable expense per unit (ID#52285) Current Sales in units 14,000 HEFIVD853's current net operating income is equal to 0.11 of current sales. Q: What is HEFIVD853's fixed cost? A: $ < Prev 14 of 25 N- MacBook Proarrow_forward
- 2.3 REQUIRED Calculate the annual economic order quantity from the information provided below. INFORMATION The following details have been supplied by Yolo Limited for one of its products: Monthly sales 6 000 units Carrying costs as a percentage of the unit purchase price 10% Purchase price per unit R50 Cost of placing an order R20arrow_forward(Related to Checkpoint 13.3) (Scenario analysis) Family Security is considering introducing tiny GPS trackers that can be inserted in the sole of a child's shoe, which would then allow for the tracking of that child if he or she was ever lost or abducted. The estimates, that might be off by 9 percent (either above or below), associated with this new product are shown here: E. Since this is a new product line, you are not confident in your estimates and would like to know how well you will fare if your estimates on the items listed above are 9 percent higher or 9 percent lower than expected. Assume that this new product line will require an initial outlay of $1.08 million, with no working capital investment, and will last for 10 years, being depreciated down to zero using straight-line depreciation. In addition, the firm's required rate of return or cost of capital is 9.7 percent, and the firm's marginal tax rate is 34 percent. Calculate the project's NPV under the "best-case scenario"…arrow_forward(Related to Checkpoint 13.3) (Scenario analysis) Family Security is considering introducing tiny GPS trackers that can be inserted in the sole of a child's shoe, which would then allow for the tracking of that child if he or she was ever lost or abducted. The estimates, that might be off by 11 percent (either above or below), associated with this new product are shown here: . Since this is a new product line, you are not confident in your estimates and would like to know how well you will fare if your estimates on the items listed above are 11 percent higher or 11 percent lower than expected. Assume that this new product line will require an initial outlay of $1.09 million, with no working capital investment, and will last for 10 years, being depreciated down to zero using straight-line depreciation. In addition, the firm's required rate of return or cost of capital is 10.3 percent, and the firm's marginal tax rate is 34 percent. Calculate the project's NPV under the "best-case…arrow_forward
- (Related to Checkpoint 13.3) (Scenario analysis) Family Security is considering introducing tiny GPS trackers that can be inserted in the sole of a child's shoe, which would then allow for the tracking of that child if he or she was ever lost or abducted. The estimates, that might be off by 11 percent (either above or below), associated with this new product are shown here: E. Since this is a new product line, you are not confident in your estimates and would like to know how well you will fare if your estimates on the items listed above are 11 percent higher or 11 percent lower than expected. Assume that this new product line will require an initial outlay of $1.09 million, with no working capital investment, and will last for 10 years, being depreciated down to zero using straight-line depreciation. In addition, the firm's required rate of return or cost of capital is 10.3 percent, and the firm's marginal tax rate is 34 percent. Calculate the project's NPV under the "best-case…arrow_forwardamm. 255.arrow_forwardAccoutingarrow_forward
- CVP Analysis, *What IT?" AnalysisKevin Co. projected contribution-format income statement for the upcoming month is shownBelow Sales (500 units) $10000Variable expenses. 4000Contributions margin. 6000Fixed expenses. 1000Net operating income. 5000Required:a.) Compute the breakeven point in units.b) Compute the breakeven paint in dollars.c.) If the company wishes to earn a monthly target profit of $10,000, how many units must be sold each month?d.) Compute the company's margin of safety. State your answer in both dollar and percentage terms,e.) The company's manager thinks that adding a salaried sales staff member at a cost of 52,000 per month will increase sales by $4,000 per month. If he is correct, what will be the net dollar advantage or disadvantage of making this change?t.) Refer to the original data, the company's manager believes that a new production process will improve profitability. He plans to add new machinery that will cut variable expenses…arrow_forwardCost Planning for Product Life Cycle... 229 RM800 Revenue Costs Research and development Prototypes Marketing Distribution Year 1 P 900,000 1,150,000 550,000 124,000 170,000 85,000 -0- P(1,179,000) Year 2 Year 3 P1,800,000 P2,000,000 -0- -0- 30,000 200,000 300,000 600,000 20,000 P 650,000 10,000 260,000 410,000 700,000 10,000 P 610,000 Manufacturing Customer service Income Required: 1. How would a product life-cycle income statement differ from this calendar- year income statement? 2. Prepare a three-year life-cycle income statement for both products. Which product appears to be more profitable? 3. Prepare a schedule showing each cost category as a percentage of total annual costs. Pay particular attention to the research and development and customer service categories. What do you think this indicates about the profitability of each product over the three-year life cycle?arrow_forwardProvide correct answer financial accountingarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Essentials Of InvestmentsFinanceISBN:9781260013924Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.Publisher:Mcgraw-hill Education,
- Foundations Of FinanceFinanceISBN:9780134897264Author:KEOWN, Arthur J., Martin, John D., PETTY, J. WilliamPublisher:Pearson,Fundamentals of Financial Management (MindTap Cou...FinanceISBN:9781337395250Author:Eugene F. Brigham, Joel F. HoustonPublisher:Cengage LearningCorporate Finance (The Mcgraw-hill/Irwin Series i...FinanceISBN:9780077861759Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan ProfessorPublisher:McGraw-Hill Education

Essentials Of Investments
Finance
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Mcgraw-hill Education,



Foundations Of Finance
Finance
ISBN:9780134897264
Author:KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:Pearson,

Fundamentals of Financial Management (MindTap Cou...
Finance
ISBN:9781337395250
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning

Corporate Finance (The Mcgraw-hill/Irwin Series i...
Finance
ISBN:9780077861759
Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:McGraw-Hill Education