Question

You have been asked by the CEO of the company to lead a project evaluation team of company specialists and consultants to develop estimates of the likely revenues and costs associated with building and operating a new production and fabricating plant in Brisbane for ten years, and to provide a recommendation on the project. The CEO plans to take your recommendation to the November 2019 meeting of the company's Board of Directors. You have been provided with a budget of $300,000 to undertake this evaluation. The company had, fortuitously, purchased land in Brisbane three years ago for $3.9 million as a result of a decision to invest surplus cash arising from the sale of its original factory site in Brompton, an inner suburb of Adelaide, in real estate in Brisbane and Sydney. The Brisbane land would be an ideal site for a new production and fabricating plant. The company recently received an offer of $4.4 million for the land. It is anticipated that the land will increase in value at the rate of 5% per annum for the foreseeable future. Your team has estimated that a new plant will cost $27 million to construct including specialist equipment purchased from a supplier in Germany, all of which can be depreciated on a straight-line basis over eight years for tax. In addition, at the end of its 10 year life, the plant and equipment could be scrapped for $5.1 million, and the land sold. There will also be a need to invest $1,000,000 in initial net working capital. Thereafter, the investment in net working capital will average around 10% of annual sales. If approved, construction of the plant would commence in January 2020 and be completed by the end of the year. Based on research undertaken by the company's marketing team, it is estimated that the company would undertake 250 apartment fitouts a year without having to increase the size of the planned plant, at an average price of $60,000 per fitout. The team estimates that fixed costs of the new plant will be $4,000,000 per year, and variable costs at $20,000 per fitout. In addition, selling costs will be 5% of annual sales, and the project would be allocated costs of $1,500,000 a year being its share of head office costs and amortization of past research and development costs. Calculate NPV of the project if the Discount rate will be 14% and Tax rate are 30%

Please do it in excel format

Answer 1

CQSol. - Microsoft Excel Page Layout Formulas Data Review View - Σ AutoSum" Α 11 - A A General Home Insert * Cut La Copy Paste Format Painter Clipboard L13 A B Calibri BI U Dr EF Wrap Text Merge & Center - A EE E $ - % , . Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter - Select Editing Font Alignment Number Cells C F G H I MN Discount Rate Initial Investment USD 14% 28,000,000.00 10 Year Cashflows Sales Variable Cost Contribution Fixed cost Selling Cost Overhead Profit before Dep. Dep. Note) Profit before Tax Tax(30%) Profit after Tax Dep.( Note) Cash Flow 15000000 5000000 10000000 4000000 750000 1500000 3750000 2862500 887500 266250 621250 2862500 3,483,750.00 1,983,750.00 1,983,750.00 1,983,750.00 1,983,750.00 1,983,750.00 1,983,750.00 1,983,750.00 1,983,750.00 7,083,750.00 PV of Cash Flows $13,038,952.32 NPV ($14,961,047.68) Sheet1 Ke Ready + BOU 100% ? OH! @ ONE 0 1:26 AM 126 AM 11/9/2019

Note
Dep. New Plant
Cost		28000000	USD
Salvage Value		5100000	USD
life		8	yrs
Dep.		2862500