CapBud Assign Oshaw Final
xlsx
keyboard_arrow_up
School
Bellevue University *
*We aren’t endorsed by this school
Course
655
Subject
Finance
Date
Feb 20, 2024
Type
xlsx
Pages
1
Uploaded by BarristerCrown6225
2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats
3
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
4
Inputs
5 ATSV old @ t=0
$ 305,000.00 ATSV formula =
Names of all members who contributed to overall project:
Include an NPV Profile
6 Equipment
$ (2,000,000.00)
1 Orin Shaw
Note: Only 1 NPV Profile for base case
7 Tax Credit
$ 100,000.00 2
8 Depreciaton per year
$ 300,000.00 3
9 Sales period 1
$ 1,000,000.00 growth: g yrs 2-3 =
15% g yrs 4-6 =
5%
4
10 CoGS %of sales
45%
5
11 SG&A exp. %of sales
10%
6
12 ATSV new @ t=6
$ 260,000.00 13
14 Operating Life CFs
15 Time
0
1
2
3
4
5
6
16 Sales
$ 1,000,000.00 $1,150,000
$1,322,500
$1,388,625
$1,458,056
$1,530,959
17 - COGS
$ 450,000.00 517,500
595,125
624,881
656,125
688,932
18 - SG&A expenses
$ 100,000.00 115,000
132,250
138,863
145,806
153,096
19 - Depreciation
$ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 20 = EBIT
$ 150,000.00 $ 217,500.00 $ 295,125.00 $ 324,881.25 $ 356,125.31 $ 388,931.58 21 -Taxes (40%)
$ 60,000.00 $ 87,000.00 $ 118,050.00 $ 129,952.50 $ 142,450.13 $ 155,572.63 22 = Net Income
$ 90,000.00 $ 130,500.00 $ 177,075.00 $ 194,928.75 $ 213,675.19 $ 233,358.95 23 + Depreciation
$ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 24 = Operating CF
$ 390,000.00 $ 430,500.00 $ 477,075.00 $ 494,928.75 $ 513,675.19 $ 533,358.95 25
26 Time 0 Investments
NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS) Percent Ranges
27 Equipment
-2,000,000
28 ATSV old
305,000
CoGS ->
CoGSbase% -20%
CoGSbase% -10%
CoGS Base % CoGSbase% +10%
CoGSbase% +20%
29 Tax credit
100,000
CoGS ->
i.e base%*0.8
i.e base%*0.9
i.e base%*1.0
i.e base%*1.1
i.e base%*1.2
30 NWC
-200,000
CoGS ->
36%
40.50%
45%
49.50%
54%
31
Cost of Capital 4%
$1,399,451
$1,215,964
$1,032,476
$848,989
$665,501
32 Terminal Non-OCF:
6%
$1,175,471
$1,004,129
$832,786
$661,444
$490,101
33 ATSV new @ t=6
260,000
8%
$974,016
$813,650
$653,284
$492,918
$332,552
34 NWC
200,000
10%
$792,323
$641,904
$491,484
$341,065
$190,645
35 = Net Cash Flow
-$1,795,000
$390,000
$430,500
$477,075
$494,929
$513,675
$993,359
12%
$628,022
$486,638
$345,255
$203,871
$62,488
36 = Cummulative CF
-$1,795,000
-$1,405,000
-$974,500
-$497,425
-$2,496
$511,179
$1,504,538
37
38 Cost of Capital 8%
39 NPV
$653,284
40 IRR =
17.7%
NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges
41 PBP = 4.005
42 PI =
54%
Sales Yr.1 ->
43
Sales Yr.1 ->
i.e base%*0.8
i.e base%*0.9
i.e base%*1.0
i.e base%*1.1
i.e base%*1.2
44 NPV Data Table $NPV in Cells:
$800,000.00 $900,000.00 $ 1,000,000.00 $ 1,100,000.00 $ 1,200,000.00 45
NPV
Cost of Capital 4%
$665,501.12 $848,989
$1,032,476
$1,215,964
$1,399,451
46
0%
$1,504,537.88
6%
$490,101.06 $661,444
$832,786
$1,004,129
$1,175,471
47
2%
$1,255,257.95 8%
$332,551.70 $492,918
$653,284
$813,650
$974,016
48
4%
$1,032,476.12 10%
$190,645.21 $341,065
$491,484
$641,904
$792,323
49
6%
$832,786.13 12%
$62,487.55 $203,871
$345,255
$486,638
$628,022
50
8%
$653,283.73 51
10%
$491,484.19 52
12%
$345,254.63 Notes / Reminders:
53
14%
$212,758.39 NPV Profile
54
16%
$92,408.96 Use NPVs from table on left to populate grids for different scenarios
55
18%
($17,168.17)
56
20%
($117,167.24)
Sales Yr.1
-20%
Sales Yr.1
-10%
Sales Yr.1 Base
Sales Yr.1
+10%
Sales Yr.1
+20%
Rates (0-26% by 2 increments)
Some Tips for NPV Scenario / Risk Analysis = NPV of Full Project under different "What if?" Assumptions for CoGS% (Top Table) & SalesYr1 (Bott
1.
Complete the tables below to report NPVs of Projec'ts Net CFs as you change the column input variable (CoGS% or SalesYr1) in the origianl C
2.
Top Table => Change CoGS% in the base model (cell D10) to the scenario column's new "What if?" value & then copy/paste the new NPVs fro
scenario rates into the scenario table for the column. Do this 1 column (scenario) at a time . Center column (scenario) is the base case & thus 3.
Bottom Table => Change Yr1 Sales in the base model (cell E16) to the scenario column value & copy/paste the new NPVs from Column D (bel
the scenario table for the column. Do this 1 column (scenario) at a time. Again, center column (scenario) is the base case & should be identica
4. Remember to reset your Cash Flow Model to the Base Case Values
when done populating your scenario tables.
NPV Profile
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
ne
NPV, with rankings Botany Bay, Inc., a maker of casual clothing, is considering four projects shown in the following table,
Because of past financial difficulties,
ptions the company has a high cost of capital at 14.4%.
a. Calculate the NPV of each project, using a cost of capital of 14.4%.
b. Rank acceptable projects by NPV.
c. Calculate the IRR of each project and use it to determine the highest cost of capital at which all of the projects would be acceptable.
a. Calculate the NPV of each project, using a cost of capital of 14.4%.
The NPV of project A is $
(Round to the nearest cent.)
Is project A acceptable? (Select the best answer below.)
O A. No
O B. Yes
The NPV of project B is $
(Round to the nearest cent.)
la nreinnt Danontabl-2/0lest the hant an nr hale
Click to select your answer(s).
P Type here to search
arrow_forward
i need help with this question and it has 3 parts. it’s an accounting 2102 question
arrow_forward
of
stion
Given the following information about projects A and B:
Project A Project B
-10,000
-10,000
4,000
3,000
10,000
Time 0
Time 1
Time 2
Time 3
If alpha company uses IRR rule to choose projects, which of the projects (Project A or Project B)
will rank highest?
Select one:
O
O
O
5,000
4,000
3,000
a. Project A
b. Project B
c. Project A and Project B have the same ranking.
d. Cannot calculate a payback period without a discount rate.
arrow_forward
Can you please show me the NPV formulas that I need to input for each blue shaded area
arrow_forward
You wish to create a Data Table that calculates the NPV with various initial outlays in D1:E6. Which cell should be entered into the Data Table dialog box, and in which edit box?
arrow_forward
Help
Choosing between two projects with acceptable payback periods Shell Camping Gear, Inc., is considering two mutually exclusive projects. Each requires an initial
investment of $180,000. John Shell, president of the company, has set a maximum payback period of 4 years. The after-tax cash inflows associated with each project
ne
are shown in the following table:
a. Determine the payback period of each project.
b. Because they are mutually exclusive, Shell must choose one. Which should the company invest in?
nts
un
a. The payback period of project A is
years. (Round to two decimal places.)
neText
edia Librai
cial Calculat
ter Resource Enter your answer in the answer box and then click Check Answer.
Check Answer
mic Study
ules
parts
remaining
Clear All
nmunication Tools >
10:02 PNM
4/19/202
P Type here to search
insert
prt
fg
f1o
arrow_forward
Can you help me find the blanks
arrow_forward
It says the answer is in
arrow_forward
EX2-7 (Algo) The Custom Bike Company...
The Custom Bike Company has set up a weighted scoring matrix for evaluation of potential projects. Following are five projects under
consideration.
Criteria
Weight
Project 1
Project 2
Project 3
Project 4
Project 5
Strong sponsor
Project 1
Project 2
Project 3
Project 4
Project 5
2
4355 AN
4
2
Supports business
strategy
1
0
10
5
7
0
Urgency
3
52472
10% of sales from
new products
3
76770
Using the scoring matrix above, what score would you give to each project?
Competition Fill market gap
4
2
2
10
0
6
10
1
7
arrow_forward
engageNOWV2 | Assignme CengageNOWv2 |Unlın X
now.com/ilrm/takeAssignment/takeAssignmentMain.do?invoker%3D8takeAssignmentSessionLocator3&inprogress%3false
Calculator
Equipment with a cost of $220,000 has an estimated residual value of $30,000 and an estimated life of 10 years or 19,000 hours. It is to be depreciated
by the straight-line method. What is the anount of depreciation for the first full year, during which the equipment was used for 2,100 hours?
Oa. $21,000
Ob. $22,000
Oc. $19,000
Od. $30,000
Previous
Next
8:21 AM
5/4/2020
arrow_forward
Oo.160.
Subject:- Account
arrow_forward
help please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all working
arrow_forward
Use the information for the question(s) below.
Project A
- 10,000
Project B
Time 0
- 10,000
Time 1
5,000
4,000
Time 2
4,000
3,000
Time 3
3,000
10,000
If WiseGuy Inc. uses IRR rule to choose projects, which of the projects (Project A or Project B) will rank highest?
OA. Project A
OB. Project B
OC. Project A and Project B have the same ranking
OD. Cannot calculate a payback period without a discount rate.
arrow_forward
NPV and IRR Analysis
Cummings Products Company is considering two mutually exclusive investments whose expected net cash flows are as follows:
EXPECTED NET CASH FLOWS
Year
Project A
Project B
-$340
-$630
-528
210
-219
210
3
-150
210
1,100
210
820
210
990
210
-325
210
a. Construct NPV profiles for Projects A and B.
Select the correct graph.
A
B
VPVS)
1400
VPV(S)
VPV(S)
1400
1400-
1200
1200-
1200-
1000
1000
1000
800
Project B
800-
Project A
800
Project A
600
600-
600
400
400-
Project A
Project B
400
200
200
Project B
200
Cost of capiar5 20
-5
30
-5
5
+++
10
20
25 30
-5
15
Cost of cntal%
-200
Cost of capital %)
20
25
30
-200
-200
-400
-4001
-400I
D
VPVS) T
arrow_forward
- X
he best response.)
Data Table
(Click on the following icon in order to copy its contents intó a spreadsheet.)
Project R
$700,000
$700,000
5700,000
S700,000
$700,000
Project S
$1,100,000
$900,000
$700,000
$500,000
$300,000
18%
Cash Flow
Project Q
Year 1
$500,000
Year 2
$500,000
Year 3
$500,000
$500,000
$500,000
9%
Year 4
Year 5
Discount rate
11%
Print
Done
arrow_forward
please answer in text with introduction , concept ,explanation , computation , steps for each part and steps clearly and completely no AI no copy paste
arrow_forward
help please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all working!
arrow_forward
NEED the correct EXCEL INPUT, not just the number answer, please provide the excel code to get the answer from the image
arrow_forward
help please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all working
arrow_forward
help please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all working
arrow_forward
All techniques with NPV profile Mutually exclusive projects Projects A and B, of equal risk, are alternatives for expanding Rosa Company's capacity. The firm's cost of capital is 15%. The cash flows for each project are
shown in the following table
a. Calculate each project's payback period.
b. Calculate the net present value (NPV) for each project.
c. Calculate the internal rate of return (IRR) for each project
d. Indicate which project you would recommend
years (Round to two decimal places.)
a. The payback period of project A is
The payback period of project B is
years (Round to two decimal places.)
b. The NPV of project A is $
The NPV of project B is $
c. The IRR of project A is
The IRR of project B is 15.24%
(Round to the nearest cent.)
(Round to the nearest cent.)
15.92%. (Round to two decimal places.)
(Round to two decimal places.)
پہلے
arrow_forward
help please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all working!
arrow_forward
ework i
370253A%252+%252Flms.mheducation.com%252Fmghmiddleware%252Fmheproducts%252
Saved
Check my work mode: This shows what is correct or incorrect for the work you have completed so far. It does not
Required information
[The following information applies to the questions displayed below.]
Wardell Company purchased a minicomputer on January 1, 2022, at a cost of $56,000 The computer was depreciated
using the straight-line method over an estimated five-year life with an estimated residual value of $8,000. On January 1,
2024, the estimate of useful life was changed to a total of 10 years, and the estimate of residual value was changed to
$2,000.
2. Prepare the year-end journal entry for depreciation on December 31, 2024. Assume that the company uses the double-declining-
balance method instead of the straight-line method.
Note: If no entry is required for a transaction/event, select "No journal entry required" in the first account field. Do not round
intermediate calculations. Round…
arrow_forward
pject: Company Accour X
DEL. Project Guidelines and Rubric x
DEL 7-1 Problem Set: Module Sev X
CengageNOWv2 |Online tea x
com/ilm/takeAssignment/takeAssignmentMain.do?invoker3D&takeAssignmentSessionLocator3D&inprogress=false
eBook
4Show Me How
Return on Total Assets
A company reports the following income statement and balance sheet information for the current year:
Net income
$224,540
Interest expense
39,620
000'080
Determine the return on total assets. If required, round the answer to one decimal place.
Average total assets
Feedback
Check My Work
Divide the sum of net income and interest expense by average total assets.
Check My Work
( Previous
*AD
dy
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Related Questions
- ne NPV, with rankings Botany Bay, Inc., a maker of casual clothing, is considering four projects shown in the following table, Because of past financial difficulties, ptions the company has a high cost of capital at 14.4%. a. Calculate the NPV of each project, using a cost of capital of 14.4%. b. Rank acceptable projects by NPV. c. Calculate the IRR of each project and use it to determine the highest cost of capital at which all of the projects would be acceptable. a. Calculate the NPV of each project, using a cost of capital of 14.4%. The NPV of project A is $ (Round to the nearest cent.) Is project A acceptable? (Select the best answer below.) O A. No O B. Yes The NPV of project B is $ (Round to the nearest cent.) la nreinnt Danontabl-2/0lest the hant an nr hale Click to select your answer(s). P Type here to searcharrow_forwardi need help with this question and it has 3 parts. it’s an accounting 2102 questionarrow_forwardof stion Given the following information about projects A and B: Project A Project B -10,000 -10,000 4,000 3,000 10,000 Time 0 Time 1 Time 2 Time 3 If alpha company uses IRR rule to choose projects, which of the projects (Project A or Project B) will rank highest? Select one: O O O 5,000 4,000 3,000 a. Project A b. Project B c. Project A and Project B have the same ranking. d. Cannot calculate a payback period without a discount rate.arrow_forward
- Can you please show me the NPV formulas that I need to input for each blue shaded areaarrow_forwardYou wish to create a Data Table that calculates the NPV with various initial outlays in D1:E6. Which cell should be entered into the Data Table dialog box, and in which edit box?arrow_forwardHelp Choosing between two projects with acceptable payback periods Shell Camping Gear, Inc., is considering two mutually exclusive projects. Each requires an initial investment of $180,000. John Shell, president of the company, has set a maximum payback period of 4 years. The after-tax cash inflows associated with each project ne are shown in the following table: a. Determine the payback period of each project. b. Because they are mutually exclusive, Shell must choose one. Which should the company invest in? nts un a. The payback period of project A is years. (Round to two decimal places.) neText edia Librai cial Calculat ter Resource Enter your answer in the answer box and then click Check Answer. Check Answer mic Study ules parts remaining Clear All nmunication Tools > 10:02 PNM 4/19/202 P Type here to search insert prt fg f1oarrow_forward
- Can you help me find the blanksarrow_forwardIt says the answer is inarrow_forwardEX2-7 (Algo) The Custom Bike Company... The Custom Bike Company has set up a weighted scoring matrix for evaluation of potential projects. Following are five projects under consideration. Criteria Weight Project 1 Project 2 Project 3 Project 4 Project 5 Strong sponsor Project 1 Project 2 Project 3 Project 4 Project 5 2 4355 AN 4 2 Supports business strategy 1 0 10 5 7 0 Urgency 3 52472 10% of sales from new products 3 76770 Using the scoring matrix above, what score would you give to each project? Competition Fill market gap 4 2 2 10 0 6 10 1 7arrow_forward
- engageNOWV2 | Assignme CengageNOWv2 |Unlın X now.com/ilrm/takeAssignment/takeAssignmentMain.do?invoker%3D8takeAssignmentSessionLocator3&inprogress%3false Calculator Equipment with a cost of $220,000 has an estimated residual value of $30,000 and an estimated life of 10 years or 19,000 hours. It is to be depreciated by the straight-line method. What is the anount of depreciation for the first full year, during which the equipment was used for 2,100 hours? Oa. $21,000 Ob. $22,000 Oc. $19,000 Od. $30,000 Previous Next 8:21 AM 5/4/2020arrow_forwardOo.160. Subject:- Accountarrow_forwardhelp please answer in text form with proper workings and explanation for each and every part and steps with concept and introduction no AI no copy paste remember answer must be in proper format with all workingarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Principles of Accounting Volume 2AccountingISBN:9781947172609Author:OpenStaxPublisher:OpenStax College
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College