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
please answer correct this time with all working
arrow_forward
Oo.160.
Subject:- Account
arrow_forward
MYUSF
* My Horne
2 CengageNOWv2 |Online teachin X
gagenow.com/ilrn/takeAssignment/takeAssignmentMain.do?invoker=&takeAssignmentSessionLocator=&inpro...
to
伯
Vanessa Company is evaluating a project requiring a capital expenditure of $480,000. The project has an estimated life of 4 years and no salvage value.
The estimated net income and net cash flow from the project are as followS:
Year
Net Income
Net Cash Flow
$90,000
$210,000
80,000
200,000
3.
40,000
160,000
4
30,000
150,000
$240,000
$720,000
The company's minimum desired rate of return for net present value analysis is 15%. The factors for the present value of $1 at compound interest of
15% for 1, 2, 3, and 4 years are 0.870, 0.756, 0.658, and 0.572, respectively.
Determine (a) the average rate of return on investment and (b) the net present value for the project.
a. Average rate of return on investment
b. Net present value
Next,
(Previous
10:00 PM
5/8/2021
%24
1.
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
| MYUSF
x My Home
CengageNOWv2 | Online teachin X
engagenow.com/ilm/takeAssignment/takeAssignmentMain.do?invoker%3D&takeAssignmentSessionLocator3D&inpro... to
全 临
Dickerson Co. is evaluating a project requiring a capital expenditure of $810,000. The project has an estimated life of 4 years and no salvage value. The
estimated net income and net cash flow from the project are as follows:
Year
Net Income
Net Cash Flow
$75,000
$285,000
2
100,000
290,000
3.
109,000
190,000
4
36,000
125,000
$320,000
$890,000
The company's minimum desired rate of return is 12%. The present value of $1 at compound interest of 12% for , 2, 3, and 4 years is 0.893, 0.797,
0.712, and 0.636, respectively.
Required:
Determine the average rate of return on investment. Round your answer to one decimal place.
Previous
Next
9:40 PM
5/8/2021
2.
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
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
Can I please have assistance with the answers that are incorrect in red? Thank you
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
Chapter 12: Applying Excel: Exercise (Part 2 of 2) (Algo)
2. The company is considering a project involving the purchase of new equipment. Change the data area of your worksheet to match
the following: Use Exhibit 128-1 and Exhibit 128-2. (Use appropriate factor(s) from the tables provided.)
A
B.
Chapter 12: Applying Excel
2
Data
4
Example E
Cost of equipment needed
430,000
45,000
35,000
20,000
6.
Working capital needed
Overhaul of equipment in four years
8.
Salvage value of the equipment in five years
$4
9.
Annual revenues and costs:
435,000
10
Sales revenues
11
Cost of goods sold
245,000
Out-of-pocket operating costs
%24
50,000
12
13
Discount rate
17 %
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
Please do not give solution in image format thanku
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
7-2 Project: Company Accour x
121. Project Guidelines and Rubric x
121. 7-1 Problem Set: Module Sev X
CengageNOWv2 | Online tea
now.com/ilrn/takeAssignment/takeAssignmentMain.do?invoker3&takeAssignmentSessionLocator3D&inprogress3false
eBook
Show 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
Average total assets
Determine the return on total assets. If required, round the answer to one decimal place.
1.8
Check My Work
Divide the sum of net income and interest expense by average total assets.
Previa
Check My Work
ADE
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_forwardplease answer correct this time with all workingarrow_forwardOo.160. Subject:- Accountarrow_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