discussion

profilecanada2016
WRDFinMan14e_PR11-6B1.xlsx
Home>Business & Finance homework help>Accounting homework help>discussion

Pr. 25(11)-6B

Problem 25(11)-6B
Name: 0
Section: # N-box Incorrects due to blanks COUNTIF(B15:AT24," ")
134
Score: 0% # N-box +B-box corrects COUNTIF(B15:AT24," ")
0
Key Code: [Key code here] Total SUM(AD13:AD15)
Instructions 134
Answers are entered in the cells with gray backgrounds. Percentage =(AD16-AD13-AD14)/AD16
Cells with non-gray backgrounds are protected and cannot be edited. 0%
An asterisk (*) will appear to the right, immediately above, or immediately below an incorrect entry. Notes:
The essay answer will not be graded. If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " "
Enter a zero in cells you would otherwise leave blank.
If number-entry or blank-entry box is incorrect, returns "*"
1. Payback Period If number-entry or blank-entry box is correct, returns single space, " "
Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct.
Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.
Proposal A: -year, -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1
Year 2
Year 3
Year 4
Proposal B: -year, -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1
Year 2
Year 3
Year 4
Proposal C: -year, -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1
Year 2
Year 3
Year 4
Proposal D: -year, -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1
Year 2
Year 3
Year 4
2. Average Rate of Return
Proposal A:

Peggy Hussey: Income from operations 		¸ =
Highland Community College: An answer will appear here when all numbers are entered in the formula.

Peggy Hussey: Investment required, including residual value 		¸
Proposal B:
¸ =
Highland Community College: An answer will appear here when all numbers are entered in the formula.
¸
Proposal C:
¸ =
Highland Community College: An answer will appear here when all numbers are entered in the formula.
¸
Proposal D:
¸ =
Highland Community College: An answer will appear here when all numbers are entered in the formula.
¸
3.
Cash Payback Period Average Rate Accept for
Proposal Years Months of Return Further Analysis?
A
B
C
D
4., 5. Net Present Value and Present Value Index
Present Present
Value of Net Cash Value of Net
$1 at 12% Flow = Cash Flow
Year 1
Year 2
Year 3
Year 4
Year 5
Total
Amount to be invested
Net present value
Present value index
Present Present
Value of Net Cash Value of Net
$1 at 12% Flow = Cash Flow
Year 1
Year 2
Year 3
Year 4
Year 5
Total
Amount to be invested
Mark Sears: Enter as a negative
Peggy Hussey: Life of investment
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.
Highland Community College: An answer will appear here when all numbers are entered in the formula.
Peggy Hussey: Income from operations
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.
Highland Community College: An answer will appear here when all numbers are entered in the formula.
Peggy Hussey: Income from operations
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.
Highland Community College: An answer will appear here when all numbers are entered in the formula.
Peggy Hussey: Income from operations
cpence: Enter number of years for pay-back.
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment
cpence: Enter number of additional months needed for pay-back.
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns.
Highland Community College: An answer will appear here when all numbers are entered in the formula.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
cpence: Enter number of years for pay-back.
cpence: Enter number of additional months needed for pay-back.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns.
Mark Sears: Select proposals accepted for further analysis in Part 3 in alphabetical order.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
cpence: Enter number of years for pay-back.
cpence: Enter number of additional months needed for pay-back.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
cpence: Enter number of years for pay-back.
Mark Sears: Select proposals accepted for further analysis in Part 3 in alphabetical order.
Mark Sears: Enter as a negative
cpence: Enter number of additional months needed for pay-back.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
Net present value
Present value index
6., 7.
Based on net present value calculated in Part 4, the proposals should be ranked as follows:
Rank 1
Rank 2
Based on present value index calculated in Part 5, the proposals should be ranked as follows:
Rank 1
Rank 2
8.
[Key essay answer here]

Sol

Problem 25(11)-6B
Name: Solution
Section:
Score: ON
Instructions
Answers are entered in the cells with gray backgrounds.
Cells with non-gray backgrounds are protected and cannot be edited.
An asterisk (*) will appear to the right, immediately above, or immediately below an incorrect entry.
The essay answer will not be graded.
Enter a zero in cells you would otherwise leave blank.
1. Payback Period
Proposal A: 4
cpence: Enter number of years for pay-back.		 -year, 0
cpence: Enter number of additional months needed for pay-back.		 -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1 full year
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns. 		$ 120,000
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback. 		$ 120,000
Mark Sears: Stop at payback point.
Year 2 full year 120,000 240,000
Year 3 full year 110,000 350,000
Year 4 full year 100,000 450,000
Proposal B: 2
cpence: Enter number of years for pay-back.		 -year, 4
cpence: Enter number of additional months needed for pay-back.		 -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1 full year
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns. 		$ 100,000
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback. 		$ 100,000
Mark Sears: Stop at payback point.
Year 2 full year 80,000 180,000
Year 3 4 months 20,000 200,000
Year 4 N/A - -
Proposal C: 3
cpence: Enter number of years for pay-back.		 -year, 6
cpence: Enter number of additional months needed for pay-back.		 -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1 full year
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns. 		$ 100,000
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback. 		$ 100,000
Mark Sears: Stop at payback point.
Year 2 full year 90,000 190,000
Year 3 full year 90,000 280,000
Year 4 6 months 40,000 320,000
Proposal D: 3
cpence: Enter number of years for pay-back.		 -year, 0
cpence: Enter number of additional months needed for pay-back.		 -month cash payback period
Net Cash Cumulative
Year and Months Flow Net Cash Flows
Year 1 full year
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns. 		$ 200,000 $ 200,000
Year 2 full year 180,000 380,000
Year 3 full year 160,000 540,000
Year 4 N/A - -
2. Average Rate of Return
Proposal A:
$60,000
Peggy Hussey: Income from operations 		¸ 5
Peggy Hussey: Life of investment 		= 5.3%
Highland Community College: An answer will appear here when all numbers are entered in the formula.
$450,000
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment 		¸ 2
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.

Highland Community College: An answer will appear here when all numbers are entered in the formula.		 Proposal B:
$90,000
Peggy Hussey: Income from operations 		¸ 5
Peggy Hussey: Life of investment 		= 18.0%
Highland Community College: An answer will appear here when all numbers are entered in the formula.
$200,000
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment 		¸ 2
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.

Highland Community College: An answer will appear here when all numbers are entered in the formula.		 Proposal C:
$120,000
Peggy Hussey: Income from operations 		¸ 5
Peggy Hussey: Life of investment 		= 15.0%
Highland Community College: An answer will appear here when all numbers are entered in the formula.
$320,000
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment 		¸ 2
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.

Highland Community College: An answer will appear here when all numbers are entered in the formula.		 Proposal D:
$220,000
Peggy Hussey: Income from operations
cpence: Enter number of years for pay-back.		 ¸ 5
Peggy Hussey: Life of investment 		= 16.3%
Highland Community College: An answer will appear here when all numbers are entered in the formula.
$540,000
Peggy Hussey: Investment required, including residual value
Peggy Hussey: Life of investment
cpence: Enter number of additional months needed for pay-back.		 ¸ 2
Peggy Hussey: Hint: When straight-line depreciation with no residual value is used, the average investment is 1/2 of the original cost.

Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns.
Highland Community College: An answer will appear here when all numbers are entered in the formula.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
cpence: Enter number of years for pay-back.
cpence: Enter number of additional months needed for pay-back.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns. 		3.
Cash Payback Period Average Rate Accept for
Proposal Years Months of Return Further Analysis?
A 4 0 5.3% Reject
B 2 4 18.0% Accept
C 3 6 15.0% Reject
D 3 0 16.3% Accept
4., 5. Net Present Value and Present Value Index
Proposal B:
Mark Sears: Select proposals accepted for further analysis in Part 3 in alphabetical order.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
cpence: Enter number of years for pay-back.
cpence: Enter number of additional months needed for pay-back.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns.
Mark Sears: In the year payback is achieved, enter only the amount needed to achieve payback.
Mark Sears: Stop at payback point.
cpence: Enter number of years for pay-back.		 Present Present
Value of Net Cash Value of Net
$1 at 12% Flow = Cash Flow
Year 1 0.893 $ 100,000 $ 89,300
Year 2 0.797 80,000 63,760
Year 3 0.712 60,000 42,720
Year 4 0.636 30,000 19,080
Year 5 0.567 20,000 11,340
Total $ 290,000 $ 226,200
Amount to be invested (200,000)
Net present value $ 26,200
Present value index 1.13
Proposal D:
Mark Sears: Select proposals accepted for further analysis in Part 3 in alphabetical order.
cpence: Enter number of additional months needed for pay-back.
Mark Sears: For each year, select "full year" or select the number of months required in that year. If the year is past the payback point, select N/A and enter a zero in the amount columns. 		Present Present
Value of Net Cash Value of Net
$1 at 12% Flow = Cash Flow
Year 1 0.893 $ 200,000 $ 178,600
Year 2 0.797 180,000 143,460
Year 3 0.712 160,000 113,920
Year 4 0.636 120,000 76,320
Year 5 0.567 100,000 56,700
Total $ 760,000 $ 569,000
Amount to be invested (540,000)
Net present value $ 29,000
Present value index 1.05
6., 7.
Based on net present value calculated in Part 4, the proposals should be ranked as follows:
Rank 1 Proposal D
Rank 2 Proposal B
Based on present value index calculated in Part 5, the proposals should be ranked as follows:
Rank 1 Proposal B
Rank 2 Proposal D
8.
The present value indexes indicate that although Proposal D has the larger net present value, it is not as attractive as Proposal B in terms of the amount of present value per dollar invested. Proposal D requires the larger investment. Thus, management should use investment resources for Proposal B before investing in Proposal D, absent any other qualitative considerations that may impact the decision.