COMPASS MINERALS IS CONSIDERING MAKING A BID TO SUPPLY THE HIGHWAY DEPARTMENT WITH ROCK SALT TO DROP ON THE ROADS IN PORTLAND,ORE. DURING WINTER. COMPASS MANAGEMENT BELIEVES THAT THE ACTUAL QUANTITY WILL AVERAGE 40,000 TONS PER YEAR. THE FIRM WILL NEED AN INITIAL INVESTMENT OF $2,300,000 IN NEW PROCESSING EQUIPMENT. THE ACCOUNTING DEPARTMENT HAS ESTIMATED THAT ANNUAL FIXED COSTS WILL BE $500,000 AND VARIABLE COST WILL BE $94 PER TON OF SALT SOLD. THE NEW EQUIPMENT WILL BE DEPRECIATED USING MACRS WITH AN EXPECTED LIFE OF 5 YEARS: THAT IS: YEAR 1 = 20% YEAR 2 = 32% YEAR 3 = 19.2% YEAR 4 = 11.52% YEAR 5 = 11.52% AT THE END OF THE PROJECT, YEAR 5, IT IS ESTIMATED THAT THE EQUIPMENT CAN BE SOLD FOR $150,000 COMPASS BELIEVES THAT THE STATE WILL CONTRACT AT A SELLING PRICE OF $125 PER TON, ALTHOUGH THE STATE MAY RECEIVE SOME LOWER BIDS. COMPASS ENGINEERING DEPARTMENT ESTIMATES THAT THE PROJECT WILL REQUIRE : AN INITIAL NETWORKING CAPITAL INVESTMENT OF $115,000 IS USED (RECALL PIGNATARO: OPERATING WORKING CAPITAL) THE FIRM'S WACC (COST OF CAPITAL) IS 12% AND ITS MARGINAL TAX RATE IS 35%. QUESTIONS: A) SET UP A WORKSHEET CONTAINING ALL RELEVANT INFORMATION SHOWING BEFORE TAX AND AFTER TAX CASH FLOWS OVER THE 5 YEAR PROJECTED PERIOD. WORKSHEET SHOULD SHOW ANNUAL CASH FLOWS FOR EACH YEAR, INCLUDING INITIAL OUTLAY, AND FINAL YEAR EQUIPMENT DISPOSAL. B) COMPUTE THE NPV, IRR OF THIS PROJECT: IS THE PROJECT ACCEPTABLE FOR COMPASS MINERALS ? C) THE STATE DECIDES TO OPEN THE PROJECT TO COMPETITIVE BIDDING, WHAT IS THE LOWEST BID PRICE PER TON OF SALT THAT COMPASS CAN ACCEPT FOR ITS SHAREHOLDERS? D) DEVELOP THREE ONE-WAY DATATABLES; PRICE /TON, UNITS SOLD, VAR COST/TON TO DETERMINE IMPACT OF EACH ON THE PROJECT IRR. USE PERCENT CHANGE OF:-9%, -6%, -3%, 0%, +3%, +6%, +9% FROM THEIR RESPECTIVE BASE VALUE IN THE SPREADSHEET: YEAR 1 WHAT ARE YOUR OBSERVATIONS ? (GRAPH VERY HELPFUL) E) CHOOSE THE TWO MOST CRITICAL VARIABLES OF THE THREE ANALYZED IN D) AND DEVELOP A TWO-WAY DATATABLE WHAT ARE YOUR OBSERVATIONS ? (CONDITIONAL FORMAT WHEREIN IRR