Use cells A6 to C17 from the given information to complete this questions. You must use the built-in excel function to answer this question. Don't worry about the numbers that are answered, they are all correct! Just help me figure out the remaining four please!
Use cells A6 to C17 from the given information to complete this questions. You must use the built-in excel function to answer this question. Don't worry about the numbers that are answered, they are all correct! Just help me figure out the remaining four please!
Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
Related questions
Question
Use cells A6 to C17 from the given information to complete this questions. You must use the built-in excel function to answer this question. Don't worry about the numbers that are answered, they are all correct! Just help me figure out the remaining four please!
![234567
8
9
10
B
C
D
E
A
We are evaluating a project that costs $845,000, has an eight-year life, and has no salvage value.
Assume that depreciation is straight-line to zero over the life of the project. Sales are projected at
51,000 units per year. Price per unit is $53, variable cost per unit is $27, and fixed costs are $950,000
per year. The tax rate is 22 percent, and we require a return of 10 percent on this project.
a. Calculate the accounting break-even point. What is the degree of operating leverage at the
accounting break-even point?
b. Calculate the base-case cash flow and NPV. What is the sensitivity of NPV to changes in the quantity
sold? Explain what your answer tells you about a 500-unit decrease in the quantity sold.
c. What is the sensitivity of OCF to changes in the variable cost figure? Explain what your answer tells
you about a $1 decrease in estimated variable costs.
Input area:
Initial cost
Project life
Units sales
Price/unit
Variable cost/unit
Fixed costs
Tax rate
Required return
b. New quantity for calculation
11
12
13
14
15
16
17
18
19
20
21
22
23
24 a. Depreciation per year
25
Accounting break-even
26
Projected sales change
c. New VC for calculation
Projected VC change
Output area:
DOL
27 b. Base OCF
28
Base NPV
29
OCF at new quantity
30
NPV at new quantity
31
DNPV/DQ
32
Change in NPV for given quantity change
33 c. OCF
34
35
$845,000.00
DOCF/DVC
Change in NPV for given VC change
8
51,000
$53.00
$27.00
$950,000.00
22%
10%
52,000
(500)
$28.00
($1.00)
(Use cells A6 to C17 from the given information to complete this question. You must use the built-in Excel function to
answer this question. The OCF must be calculated using the depreciation tax shield approach.)
$105,625
40,600.96
9.994
$316,517.50
$843,597.50
$336,797.50
$951,789.81
F
$276,737.50
($39,780.00)
G
H
I](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F67c8dfa2-b702-4ef7-a40f-9f34dfda5707%2F5f93fca5-1e40-4a7d-a79b-20853d7ddf9e%2Fkp2bbkt5_processed.png&w=3840&q=75)
Transcribed Image Text:234567
8
9
10
B
C
D
E
A
We are evaluating a project that costs $845,000, has an eight-year life, and has no salvage value.
Assume that depreciation is straight-line to zero over the life of the project. Sales are projected at
51,000 units per year. Price per unit is $53, variable cost per unit is $27, and fixed costs are $950,000
per year. The tax rate is 22 percent, and we require a return of 10 percent on this project.
a. Calculate the accounting break-even point. What is the degree of operating leverage at the
accounting break-even point?
b. Calculate the base-case cash flow and NPV. What is the sensitivity of NPV to changes in the quantity
sold? Explain what your answer tells you about a 500-unit decrease in the quantity sold.
c. What is the sensitivity of OCF to changes in the variable cost figure? Explain what your answer tells
you about a $1 decrease in estimated variable costs.
Input area:
Initial cost
Project life
Units sales
Price/unit
Variable cost/unit
Fixed costs
Tax rate
Required return
b. New quantity for calculation
11
12
13
14
15
16
17
18
19
20
21
22
23
24 a. Depreciation per year
25
Accounting break-even
26
Projected sales change
c. New VC for calculation
Projected VC change
Output area:
DOL
27 b. Base OCF
28
Base NPV
29
OCF at new quantity
30
NPV at new quantity
31
DNPV/DQ
32
Change in NPV for given quantity change
33 c. OCF
34
35
$845,000.00
DOCF/DVC
Change in NPV for given VC change
8
51,000
$53.00
$27.00
$950,000.00
22%
10%
52,000
(500)
$28.00
($1.00)
(Use cells A6 to C17 from the given information to complete this question. You must use the built-in Excel function to
answer this question. The OCF must be calculated using the depreciation tax shield approach.)
$105,625
40,600.96
9.994
$316,517.50
$843,597.50
$336,797.50
$951,789.81
F
$276,737.50
($39,780.00)
G
H
I
Expert Solution
![](/static/compass_v2/shared-icons/check-mark.png)
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.Recommended textbooks for you
![Essentials Of Investments](https://compass-isbn-assets.s3.amazonaws.com/isbn_cover_images/9781260013924/9781260013924_smallCoverImage.jpg)
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
![FUNDAMENTALS OF CORPORATE FINANCE](https://www.bartleby.com/isbn_cover_images/9781260013962/9781260013962_smallCoverImage.gif)
![Financial Management: Theory & Practice](https://www.bartleby.com/isbn_cover_images/9781337909730/9781337909730_smallCoverImage.gif)
![Essentials Of Investments](https://compass-isbn-assets.s3.amazonaws.com/isbn_cover_images/9781260013924/9781260013924_smallCoverImage.jpg)
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
![FUNDAMENTALS OF CORPORATE FINANCE](https://www.bartleby.com/isbn_cover_images/9781260013962/9781260013962_smallCoverImage.gif)
![Financial Management: Theory & Practice](https://www.bartleby.com/isbn_cover_images/9781337909730/9781337909730_smallCoverImage.gif)
![Foundations Of Finance](https://www.bartleby.com/isbn_cover_images/9780134897264/9780134897264_smallCoverImage.gif)
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
![Fundamentals of Financial Management (MindTap Cou…](https://www.bartleby.com/isbn_cover_images/9781337395250/9781337395250_smallCoverImage.gif)
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…](https://www.bartleby.com/isbn_cover_images/9780077861759/9780077861759_smallCoverImage.gif)
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