Please show all work - including formulas.

A16 fx SPREADSHEET: DETERMINATION OF COSTS AND ANNUAL CASH FLOWS; AFTER TAX NPV AND IRR A B D E F G H T J K 1 5 6 7 THE NESTLE CORPORATION IS CONSIDERING A PROPOSAL FOR AN INVESTMENT IN NEW MACHINERY. THE PROJECT REQUIRES AN IMMEDIATE INVESTMENT (IN THOUSANDS): 1) MACHINERY COST = $8,000 2) COMMITMENT OF ADDED NET WORKING CAPITAL (NWC) = $2,000 3) NESTLE EXPECTS THAT SALES WILL INCREASE BY $15,000 PER YEAR FOR 5 YEARS (I.E., YEAR 1 =$15,000, YEAR 2= $15,000,... NOT YEAR 2 = $30,000) 4) AT THE END OF 5TH YEAR THE MACHINES WILL BE WORN OUT AND PROJECT WILL END 5) PRODUCTION OF NEW OUTPUT WILL REQUIRE OPERATING COST OF 75% OF SALES 6) AT THE END OF 5TH YEAR NESTLE EXPECTS NET WORKING CAPITAL (NWC) WILL BE RECOVERED 7) SALVAGE VALUE OF MACHINERY IS EXPECTED TO GENERATE $4,000 AFTER TAX 8) THE MACHINERY WILL HAVE DEPRECIATED UISNG THE STRAIGHT LINE METHOD OVER EACH OF THE 5 YEARS 9) NESTLE'S TAX RATE = 40% AND ITS COST OF CAPITAL = 9.6% A) EVALUATE THIS PROJECT OPPORTUNITY: PROJECT ACCEPTABLE: NPV >O? IRR > COST OF CAPITAL ? B) NESTLE EXPECTS SALES REVENUE INCREASE TO = $15,000 / YR; HOW LOW CAN REVENUE ACTUALLY GO AND THE PROJECT IS ACCEPTABLE? 8 9 10 11 12 13 14 15 ANSWER: 16 SPREADSHEET: DETERMINATION OF COSTS AND ANNUAL CASH FLOWS; AFTER TAX NPV AND IRR 17 18 19