Use the following excel (info) to create an excel model that computes the Termination Value (TV)...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Use the following excel (info) to create an excel model that computes the Termination Value (TV) of the project at the end of the project and answer the questions below. This is a 10-year project. All long-term assets are purchased at year 0 and sold in the first days of year 11; however, the proceeds (and any taxes associated with them) are only received at the end of year 11 (considered the end of the project for easiness of NPV calculations and modeling in excel). The excel model must follow the format in CoCoMix. The excel spreadsheet must have 3 areas: Input area; working area; and results area. The excel file provided here is already set up that way. You just have to create the formula in the respective cells. Everything in the results area must be cell reference (no inputs by hand but only by referencing cells in the input or working areas). Make sure you fill out the cells in the shaded area (working area) using formulas and referencing to data in the input area. The results area of the model is the last part of the excel. Click here for the excel file for this problem. Round to nearest dollar; do not enter decimals; do not enter the $ symbol. For example, if your result is $237.85 then enter 238 What is the remaining book value of the building in year 10? A 2. What is the remaining book value of equipment at the end of year 10? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 3. What is the tax effect of selling the equipment at the end of the project? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 4. What is the tax effect of selling the building at the end of project? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not @se the + sign). For example, if your result is $4,562.85 then enter 4,563 A 5. What is the recovery of the - investments in working capital (net proceeds from selling the working capital) at end of prioject? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 6. What is the $4,562.85 then enter 4,563 A 5. What is the recovery of the investments in working capital (net proceeds from selling the working capital) at end of prioject? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 6. What is the - termination value of the project at time 11? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 - 63-year MACRS class 5-year MACRS class 7-year MACRS class 9 15-year MACRS class 10 11 INPUTS 12 13 MACRS method for new investments 14 Variable costs-CGS 15 Discount rate 16 Tax rate 17 ITC 18 start ap cest 19 20 21 22 Sales growth 23 Cash as % of sales 24 Accounts Receivable 25 Investeries as % of sales 26 Curvet liab as % of sales 27 sed part of CGS 28 sed part of Selling & Admen esp 29 Varsible part of S&A expenses 30 New investment 31 32 33 34 35 Sales for Best year 36 Overhead expenditures 37 dass 38 Equipment (7 year clan) Computers/IT 3-year dass) Building (15 year class) Special devices for beverage 0 yr class) S 60% 12% 20% 20 Percentage for depreciation under MACRS from table 74 (in study.net package) 1 2 3 5 0.3333 0.4445 0.1481 0.3200 0.1920 0,2000 0.1428 0.050 Year 0 Casti 400 200 600 50 85 Year 1 3% 7% 8% 4% 500 1,000 0 3.000 6.00 3% 0.2449 0.1749 0.1250 0.0892 0.0892 0.0892 0.0448 0.095 0.0855 Year 2 5% 2% 6% 6% 7% 540 1,349 6.00 35 Year 3 10% 4% 3% 8% 7% 500 1,000 8% 0.0741 0.1152 0.1152 0.0576 *.** 3% 0.0770 0.0693 0.0623 0.0590 0.0590 0.0591 Year Year Year 4 5 10% 3% 5% 6% 676 6 10% 3% 6% 6% 4% 6% 500 600 600 1,000 1,000 1,000 1,000 25% 25% 25% 25% 5% 2% 3% 5% 6% 500 0.00 3% 9.20 3% Year Year Year 7 5% 3% 6% 5% 0.00 3% 6.00 3% 25 2% 3% 3% 6% 6% 5% 5% 65 65 600 600 1,000 1,000 25% 25% 9.00 3% 9 8.00 3% 0.0590 Year 10 4% 3% 6% 3% 6% 600 1,000 25% 10 0.00 11-16 0.0295 Year 10 Selling Price 200 30 150 Work Space/Area: Estimated gross sales Erosion of sales Erosion of sales Sales net of erosion effects Working capital Chg. in Net Working Capital 8 9 50 RESULTS/FINAL REPORT AREA: 51 52 CASH FLOWS/YEAR 53 54 Initial Outlay: 65 66 67 68 69 Working Capital 70 71 1. Cash flows from Lovestments 12377 74 75 10 10 10 10 Use the following excel (info) to create an excel model that computes the Termination Value (TV) of the project at the end of the project and answer the questions below. This is a 10-year project. All long-term assets are purchased at year 0 and sold in the first days of year 11; however, the proceeds (and any taxes associated with them) are only received at the end of year 11 (considered the end of the project for easiness of NPV calculations and modeling in excel). The excel model must follow the format in CoCoMix. The excel spreadsheet must have 3 areas: Input area; working area; and results area. The excel file provided here is already set up that way. You just have to create the formula in the respective cells. Everything in the results area must be cell reference (no inputs by hand but only by referencing cells in the input or working areas). Make sure you fill out the cells in the shaded area (working area) using formulas and referencing to data in the input area. The results area of the model is the last part of the excel. Click here for the excel file for this problem. Round to nearest dollar; do not enter decimals; do not enter the $ symbol. For example, if your result is $237.85 then enter 238 What is the remaining book value of the building in year 10? A 2. What is the remaining book value of equipment at the end of year 10? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 3. What is the tax effect of selling the equipment at the end of the project? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 4. What is the tax effect of selling the building at the end of project? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not @se the + sign). For example, if your result is $4,562.85 then enter 4,563 A 5. What is the recovery of the - investments in working capital (net proceeds from selling the working capital) at end of prioject? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 6. What is the $4,562.85 then enter 4,563 A 5. What is the recovery of the investments in working capital (net proceeds from selling the working capital) at end of prioject? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 A 6. What is the - termination value of the project at time 11? Do not enter the $ sign; round to zero decimals; if result is a cash outflow, enter the sign in front of first digit (no space in between). If the result is a cash inflow, simply enter the answer (do not use the + sign). For example, if your result is $4,562.85 then enter 4,563 - 63-year MACRS class 5-year MACRS class 7-year MACRS class 9 15-year MACRS class 10 11 INPUTS 12 13 MACRS method for new investments 14 Variable costs-CGS 15 Discount rate 16 Tax rate 17 ITC 18 start ap cest 19 20 21 22 Sales growth 23 Cash as % of sales 24 Accounts Receivable 25 Investeries as % of sales 26 Curvet liab as % of sales 27 sed part of CGS 28 sed part of Selling & Admen esp 29 Varsible part of S&A expenses 30 New investment 31 32 33 34 35 Sales for Best year 36 Overhead expenditures 37 dass 38 Equipment (7 year clan) Computers/IT 3-year dass) Building (15 year class) Special devices for beverage 0 yr class) S 60% 12% 20% 20 Percentage for depreciation under MACRS from table 74 (in study.net package) 1 2 3 5 0.3333 0.4445 0.1481 0.3200 0.1920 0,2000 0.1428 0.050 Year 0 Casti 400 200 600 50 85 Year 1 3% 7% 8% 4% 500 1,000 0 3.000 6.00 3% 0.2449 0.1749 0.1250 0.0892 0.0892 0.0892 0.0448 0.095 0.0855 Year 2 5% 2% 6% 6% 7% 540 1,349 6.00 35 Year 3 10% 4% 3% 8% 7% 500 1,000 8% 0.0741 0.1152 0.1152 0.0576 *.** 3% 0.0770 0.0693 0.0623 0.0590 0.0590 0.0591 Year Year Year 4 5 10% 3% 5% 6% 676 6 10% 3% 6% 6% 4% 6% 500 600 600 1,000 1,000 1,000 1,000 25% 25% 25% 25% 5% 2% 3% 5% 6% 500 0.00 3% 9.20 3% Year Year Year 7 5% 3% 6% 5% 0.00 3% 6.00 3% 25 2% 3% 3% 6% 6% 5% 5% 65 65 600 600 1,000 1,000 25% 25% 9.00 3% 9 8.00 3% 0.0590 Year 10 4% 3% 6% 3% 6% 600 1,000 25% 10 0.00 11-16 0.0295 Year 10 Selling Price 200 30 150 Work Space/Area: Estimated gross sales Erosion of sales Erosion of sales Sales net of erosion effects Working capital Chg. in Net Working Capital 8 9 50 RESULTS/FINAL REPORT AREA: 51 52 CASH FLOWS/YEAR 53 54 Initial Outlay: 65 66 67 68 69 Working Capital 70 71 1. Cash flows from Lovestments 12377 74 75 10 10 10 10
Expert Answer:
Answer rating: 100% (QA)
Here is the excel model that computes the termination value TV of the project at the end of the proj... View the full answer
Related Book For
Intermediate accounting
ISBN: 978-0077647094
7th edition
Authors: J. David Spiceland, James Sepe, Mark Nelson
Posted Date:
Students also viewed these accounting questions
-
Q.1 Read the situation well and answer the questions below it: Consider that you have recently joined a multinational organization. You spend the initial few weeks observing the culture of the...
-
Answer the following questions using the data from the Excel file cafeteria. xlsx described in Problem 16.34. From Exercise a. Forecast the food services sales for each day next week using seasonal...
-
Answer the following questions using the data from the Excel file Deloitte. xlsx from Problem 16.36. From Problem a. Using exponential smoothing with = 0.6, forecast the percentage of holiday...
-
Solve the given equation over the real numbers by completing the square. If there are more than one solution, separate them by a comma. If there is no real solution, enter DNE (short for does not...
-
The top management team at Olympia, Inc., wishes to have real-time access to the general ledger. Currently the general ledger is updated nightly via a batch processing system, similar to Figure in...
-
The ?All About You? feature shows the importance of good record-keeping for small businesses and students. You are a first-year university student and very excited about moving away from home to go...
-
A gray gas with an absorption coefficient of \(\kappa\) is contained between two plates separated by a distance \(L\). Using the diffusion approximation for radiation, derive the relation...
-
Hovington, CPA, knows that while audit objectives relating to inventories may be stated in terms of the assertions as presented in this chapter, they may also be subdivided and stated more...
-
Consider a 1.0 m guitar string. At what distances from the end should you pluck the string if you want to enhance the n = 4 mode? Is n = 4 the only mode that is excited? Explain.
-
Determining the sensitivity of a banks profits to changing interest rates is an example of which type of financial analytics? a. Prescriptive analytics b. Descriptive analytics c. Diagnostic...
-
5) A company's balance sheet showed the following amounts as of December 31: Payables Accruals Notes Payable Long-term Debt Common Stock Retained Earnings Total Liab & Eq. Cash Receivables Inventory...
-
Which of the following would normally be included in other operating revenues of a government hospital? a. Unrestricted interest income from an endowment. b. An unrestricted gift. c. Donated...
-
Which of the following is an accurate description of major fund reporting concepts? a. Fiduciary funds must be reported by fund type. b. Proprietary funds are subject to major fund reporting. c....
-
A nongovernment voluntary health and welfare organization (VHWO) received an unconditional pledge in 20X5 from a donor specifying that the amount pledged be used in 20X7. The donor paid the pledge in...
-
Securities donated to a voluntary health and welfare organization (VHWO) should be recorded at the a. donors recorded amount. b. fair market value at the date of the gift. c. fair market value at the...
-
A government college or university's Statement of Cash Flows would potentially report all of the following categories of cash flows except a. cash flows from operating activities. b. cash flows from...
-
A wrench is used to apply a force of 50 Newtons at a distance of 0.3 meters from the center of rotation. What is the torque produced by the wrench?
-
If someone's Z-score for a variable was 0.67. Their score is a significant extreme score. Their score is not significant. O Their score is slightly above average. O Their score is an outlier.
-
Culinary Creations leased kitchen equipment under a five-year lease with an option to renew for three years at the end of five years and an option to renew for an additional three years at the end of...
-
The Horstmeyer Corporation commenced operations early in 2013. A number of expenditures were made during 2013 that were debited to one account called intangible asset. A recap of the $644,000 balance...
-
Assume Estate Construction is constructing a building for CyberB, an online retailing company. Under the construction agreement, if for some reason Estate could not complete construction, CyberB...
-
Calculate the reduction ratio \(r_{k}=\frac{f\left(X_{k} ight)-f\left(X_{k}+s_{k} ight)}{m_{k}\left(X_{k} ight)-m_{k}\left(X_{k}+s_{k} ight)}\) for the function given in problem 1 .
-
Discuss the effect of initial trust region radius (ITRR) on the performance of TR algorithm.
-
Find the maximum of the function using Trust region method \(f=15 x_{1}+8 x_{1} x_{2}+5 x_{2}\) subject to \(x_{1}+x_{2} \leq 10\)
Study smarter with the SolutionInn App