Please break down in excel sheet and check answer for me. Please help me make su
ID: 2492089 • Letter: P
Question
Please break down in excel sheet and check answer for me. Please help me make sure the answer is correct. Here is the question below.
Sentinel Company is considering an investment in technology to improve its operations. The investment will require an initial outlay of $249,000 and will yield the following expected cash flows. Management requires investments to have a payback period of 2 years, and it requires a 10% return on investments. (PV of $1, FV of $1, PVA of $1, and FVA of $1) (Use appropriate factor(s) from the table provided.) Period Cash Flow 1 $48,200 52,000 76,100 94,900 125,200 2 4 Required 1. Determine the payback period for this investment. (Enter cash outflows with a minus sign. Round your Payback Period answer to 1 decimal place.) Cash inflow (outflow) Cumulative Net Cash Inflow (outflow) Year $(249,000) $ 48,200 42,000 76,100 94,900 125,000 137,200 (249,000) (200,800) (158,800) (82,700) 12,200 137,200 4 Calculate the payback period Payback occurs between year: Calculate the portion of the year: and year: 82 700 94,900 Numerator for partial year 0.9 years Denominator for partial year $ Payback period 3.9 yearsExplanation / Answer
1) Payback period Year Cash flow Cummulative CF 0 -249000 -249000 1 48200 -200800 2 52000 -148800 3 76100 -72700 4 94900 22200 5 125200 147400 Payback period is between year 3 and 4 Partial year is 72700/94900 = 0.766069547 Hence payback period = 3+0.77 = 3.77 years 2) Break even time for investment Year Cash flow Table factor PV of cash flow Cummulative of PV of cash flow 0 -249000 1 -249000 -249000 1 48200 0.909 43813.8 -205186 2 52000 0.826 42952 -162234 3 76100 0.751 57151.1 -105083 4 94900 0.683 64816.7 -40266.4 5 125200 0.621 77749.2 37482.8 Break even time is between year 4 and 5 Partial year is 40266/77749 = 0.52 Break even time is 4 + 0.52 = 4.52 3) NPV = 43813.8 + 42952+ 57171.1 + 64816 .7 + 77749.2 - 249000 = 37482.8