Hope Investments, founded by John Hope, is considering the formation of a new transport business. He believes a great market exists in medium to long range shipping. However, he wants you to do the analysis to see if this is a good investment and if he should purchase regular or refrigerated trucks. Here is the information you need to know.

$1.$ To begin, he needs to buy $10$ large tractor trailer trucks. Those trucks average $$130,000$ each for a dry truck. For a refrigerated truck $($reefer$),$ those trucks average $$155,000$ each. He will either do all dry or all reefers. He expects to put $15\%$ down. It will be a $10$ year loan, compounded monthly.

$2.$ He has investigated several banks. Depending on the market at the time and his credit rating, he expects the loan interest rate to be $4\mathrm{.}65\%,4\mathrm{.}80\%$ or $5\%($APR$).$

$3.$ Hope has gathered data from other trucking companies to estimate how much he can charge in this market. The data is shown after the problem. You should be able to build a linear regression model that shows how much to charge per mile and the adjustment to make if the cargo is refrigerated. $($To find the charges for the reefer truck, simply make the reefer variable equal to one if the truck is refrigerated, $0$ if it is dry. This is called an indicator variable.$)$

$4.$ Overhead $($including fuel$)$ and maintenance expenses will depend on the overall economy at the time. In a good market, they expect an overhead rate of $28\%.$ In a poor market, it will be $40\%.$ In a normal market, they expect a rate of $34\%.$ This overhead rate would be taken immediately from the revenue before other bills are paid. Reefers have an automatic $6\%$ extra in expenses regardless of the market.

$5.$ The effective tax rate would be $20\%$ of revenue. $($Do not worry how the tax rate affects the loans. Simply treat this as another expense.$)$

$6.$ Hope believes the mileage traveled by each truck depends on the market. In a good market, he would expect each truck to travel $8,000$ miles per month. In a poor market, it would be $3,000$ miles per month. For a normal market, expect $5,500$ miles per month.

Hope wants you to create an Excel spreadsheet model demonstrating how all of the options would affect his monthly profit or loss. He should be able to look at the first sheet and clearly see what would happen if each condition mentioned is in place and he should be able to change the inputs to immediately see what the impact would be$.($For example, if one of the interest rates were to change to $5\mathrm{.}25\%,$ what would happen?$)$ The calculations should be shown in another area but marked in a way that he can check them for accuracy if he so chooses. Create a graph showing the monthly profit of the various options.