COMPASS MINERALS IS CONSIDERING MAKINGA 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\mathrm{.}2\%$ YEAR $4=11\mathrm{.}52\%$ YEAR $5=11\mathrm{.}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 SELUNG PRICE OF $$125$ PER TON, ALTHOUGH THE STATE MAY RECEIVE SOME LOWER BIDS.

COMPASS ENGINEERING DEPARTMENT ESTIMATES THAT THE PROJECT WILL REQUIRE :

AN INITIAL NET WORKING CAPITAL INVESTMENT OF $$115,000$ IS USED $($RECALL PIGNATARO: OPERATING WORKING CAPITAL$).$ THIS NWC WILL BE RECOVERED AT END OF PROJECT.

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 VERY HELPFUL$)$

F$)$ SCENARIO ANALYSIS: ASSUME COMPASS MANAGEMENT PROVIDE THE FOLLOWING ADDITIONAL INFORMATION: YEAR $1$

OPEN A NEW WORKSHEET: USE THE EXCEL SCENARIO MANAGER TO COMPUTE NPV AND IRR FOR EACH CASE.

THEN COMPUTE THE PROBABILTY WEIGHTED NPV AND IRR ; AND THE PROBABILTY WEIGHTED STANDARD DEVIATION NPV AND IRR.

FINALLY, ASSUMING A NORMAL DISTRIBUTION, WHAT IS THE PROBABILITY $($ NPV AND PROBABILITY $($IRR I.E$.,$ PROJECT ACCEPTABLE $?$