After analysing the financial data of Q-Constructions, you notice that they are trending in the right direction. A new 12-month construction proposal has come to the company worth $1,000,000 and an important question is whether it will be financially viable. They want you to analyse the proposal, in particular, the recommended cash flow schedule and to understand the key financial points during the construction project. The following cash flow schedule is summarised below.
To ensure that all upfront and on-going outlay costs are covered in advance, Q-Constructions incur an initial start-up cost of $200,000. The proposal states that they will receive a deposit from the client of 10% of the total project price at the beginning. They then receive four equal instalment payments of 20% of the total project price associated to project milestones from the client at the end of the 2nd, 6th, 8th and 10th month. Finally, they receive the last 10% project milestone on lock-up which occurs at the end of the 12th month. Q-Constructions has ongoing project costs of $20,000 to pay salaries and services at the end of each month. In additional, there are material costs of $100,000 associated for each of the project milestones at the end of the 2nd, 6th, 8th and 10th month. The current cost of capital for company is 8% per annum compounded monthly. You have been tasked with the important objective to determine whether this future project is financially viable. In addition, they want you to determine which milestone is needed to be completed in the project proposal such that it will be financially viable. It’s time to show your Quants knowledge and expertise with Excel to determine the financial viability of this project.
A. Set up a
- The full spreadsheet with all completed entries. Show how you entered cash inflow and
cash outflow amounts at the beginning, 1st, 2nd, 3rd You can type this in Word. - The NPV calculation (showing the calculation via the Excel function NPV and Excel cell references is OK). You can show this either in the spreadsheet or type it in Word.
B. The company wants to know at which milestone in the project proposal would be financially viable if the contract has terminated was early. Determine the milestone in the construction proposal for which the project would be financially viable.
C. Q-Constructions would like you to create a visualisation of the completed NPV spreadsheet from part (A). Include the graph here and in the infographic as indicated.
Step by stepSolved in 5 steps with 3 images
- Having difficulty with IRR calculations...attached the homework question. Thanks!arrow_forwardWhich of the following should be considered when a company estimates the cash flows used to analyze a proposed project? Group of answer choices Since the firm's director of capital budgeting spent some of her time last year to evaluate the new project, a portion of her salary for that year should be charged to the project's initial cost. The company spent and expensed $10 million on a marketing study before its current analysis regarding whether to accept or reject the project. The firm would borrow all the money used to finance the new project, and the interest on this debt would be $1.5 million per year. The new project is expected to reduce sales of one of the company's existing products by 5%. The company has spent and expensed $1 million on R&D associated with the new project.arrow_forwardUsing the Campbell Soup files given below, develop NOPAT, OCF, and FCF for the last two years. In your opinion and using these measures, is the firm healthy or not? Why or why not? [Note: you will need to use the cash flow statement to find data for fixed asset and working capital investments. For fixed asset investment, consider only the “Purchases of plant assets” in your analysis from the cash flow statement. For working capital investment, only consider Depreciation and Amortization, accounts receivable, inventory, accounts payable, accruals from the cash flow statement. Remember that each of these cash flows already represent changes in cash flow period-over-period.] Please use supporting data exhibits. Consolidated Balance Sheets July 28, 2019 July 29, 2018 Current assets Cash and cash equivalents $ 31 $ 49 Accounts receivable, net 574 563 Inventories 863 887 Other current assets 71 71…arrow_forward
- Your firm is evaluating a capital budgeting project. The estimated cash flows appear below. The board of directors wants to know the expected impact on shareholder wealth. Knowing that the estimated impact on shareholder wealth equates to net present value (NPV), you use your handy calculator to compute the value. What is the project's NPV? Assume that the cash flows occur at the end of each year. The discount rate (i.e., required rate of return, hurdle rate) is 15.6%. (Round to nearest penny) Year 0 cash flow Year 1 cash flow Year 2 cash flow Year 3 cash flow Year 4 cash flow Year 5 cash flow. -99,000 48,000 33,000 43,000 42,000 20,000arrow_forwardUse the format in the figure below to perform a financial analysis. Create a spreadsheet to perform the analysis and show the NPV, ROI, and year in which payback occurs. + Perform a financial analysis for a project using the format provided in Figure 4-5. Assume that the projected costs and benefits for this project are spread over four years as fol- lows: Estimated costs are $200,000 in Year 1 and $30,000 each year in Years 2, 3, and 4. Estimated benefits are $0 in Year 1 and $100,000 each year in Years 2, 3, and 4. Use a 9 percent discount rate, and round the discount factors to two decimal places. Create a spreadsheet or use the business case financials template on the companion website to cal- culate and clearly display the NPV, ROI, and year in which payback occurs. In addition, write a paragraph explaining whether you would recommend investing in this project, based on your financial analysis. Discount rate 8% Assume the project is completed in Year 0 0 Costs Discount factor…arrow_forwardIn Management Accounting, there are some investment appraisal methods to analyse the performance of investment projects. The following table lists out the financial data of two projects for London Technology Ltd: Projects/Methods A B Payback Period 2 year and 4 months 2 year and 9 months Accounting Rate of Return 27.6% 15.44% Net Present Value £23,040 £21,798 Required: Which project should company accept? In the discussion, please explain which method can lead to better decision.arrow_forward
- Consider how Root Valley River Park Lodge could use capital budgeting to decide whether the $13,000,000 River Park Lodge expansion would be a good investment. Assume Root Valley's managers developed the following estimates concerning the expansion: (Click the icon to view the estimates.) (Click the icon to view additional information.) The internal rate of return (IRR) of the expansion is Data table More info example 10-12% Number of additional skiers per day Average number of days per year that weather conditions allow skiing at Root Valley Useful life of expansion (in years) Average cash spent by each skier per day Average variable cost of serving each skier per day Cost of expansion Discount rate Get more help. 12-14% Print 15-16% 16-18% S Done 122 skiers 149 days Assume that Root Valley uses the straight-line depreciation method and expects the lodge expansion to have no residual value at the end of its nine-year life. The project is expected to have an average annual net cash…arrow_forwardSubject: accountingarrow_forwardIf the appropriate interest rate to use when evaluating potential project for the firm is 10%, what is the present value of these cash flows?arrow_forward
- Your firm is evaluating a capital budgeting project. The estimated cash flows appear below. The board of directors wants to know the expected impact on shareholder wealth. Knowing that the estimated impact on shareholder wealth equates to net present value (NPV), you use your handy calculator to compute the value. What is the project's NPV? Assume that the cash flows occur at the end of each year. The discount rate (i.e., required rate of return, hurdle rate) is 17.4%. (Round to nearest penny) Year 0 cash flow -132,000 Year 1 cash flow 52,000 Year 2 cash flow 31,000 Year 3 cash flow 42,000 Year 4 cash flow 39,000 Year 5 cash flow 18,000arrow_forwardXYZ, Co. is considering the purchase of a new building that will allow them to expand their market. They plan to pay for the investment with a bank loan. The interest payments on the loan will be $140,000 per year. How will this be treated in the cash flows for capital budgeting?arrow_forwardYour firm is evaluating a capital budgeting project. The estimated cash flows appear below. The board of directors wants to know the expected impact on shareholder wealth. Knowing that the estimated impact on shareholder wealth equates to net present value (NPV), you use your handy calculator to compute the value. What is the project's NPV? Assume that the cash flows occur at the end of each year. The discount rate (i.e., required rate of return, hurdle rate) is 15.4%. (Round to nearest penny) Year O cash flow Year 1 cash flow Year 2 cash flow Year 3 cash flow Year 4 cash flow Year 5 cash flow Answer: -113,000 45,000 34,000 58,000 33,000 35,000arrow_forward
- Essentials Of InvestmentsFinanceISBN:9781260013924Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.Publisher:Mcgraw-hill Education,
- Foundations Of FinanceFinanceISBN:9780134897264Author:KEOWN, Arthur J., Martin, John D., PETTY, J. WilliamPublisher:Pearson,Fundamentals of Financial Management (MindTap Cou...FinanceISBN:9781337395250Author:Eugene F. Brigham, Joel F. HoustonPublisher:Cengage LearningCorporate Finance (The Mcgraw-hill/Irwin Series i...FinanceISBN:9780077861759Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan ProfessorPublisher:McGraw-Hill Education