Blue Marlin Gas (2020)

Module 9

Yasmin Arizmendi is the business manager for Blue Marlin Gas, a gas station and convenience store in Hartford, Michigan. Yasmin is thinking about making some capital improvements, including adding a drive through car wash to the station. She wants to perform an investment analysis to determine whether the capital improvement will pay off for the company. Yasmin has asked for your help in creating the workbook to determine what kind of return she can expect on her investment. Read all the questions before starting so you understand what youre doing. Complete the following:

1. Open the NP_EX 9 Blue Marlin Excel spreadsheet.
2. The Blue Marlin worksheet is partially set up for you.
3. The initial cost (in year 0) to build a drive-through car wash is $350,000. Yasmin determines that the useful life of the equipment is 15 years with a salvage value of $50,000. Enter this information into the Assumptions section. Then create links to complete the Investment & Salvage section of the spreadsheet (rows 29-30).
4. Complete the Operating Cash Flow section to project the financial performance of the car wash from year 1 through Year 15. For each year, enter the revenue, operating expenses, depreciation, taxable income, the taxes due, and the net income from the car wash.
   1. Yasmin expects the car wash to generate $70,000 in Year 1 and $110,000 in Year 15. Calculate the revenue values in Year 2 through Year 14 assuming a linear trend.
   2. Set the expense of operating the car wash to $0 in Year 0, $25,000 in Year 1 and $50,000 in Year 15. Calculate the Year 2 through Year 14 values assuming a linear trend.
   3. Calculate the initial Operating Profit (also called EBITDA (earnings before interest, taxes, depreciation and amortization)) for each year.
   4. Calculate the amount of depreciation using the Declining Balance depreciation (=DB) method. Use links to the Assumptions section to complete the formula.
   5. Calculate the EBT (Earnings before taxes) for the car wash in the next row.
   6. Enter a 37% income tax rate in the Assumptions section. Then calculate the amount of taxes owed for each year of the car washs operation.
   7. Calculate the Net Income from the car wash in Year 1 through Year 15.

5. Save your workbook. Go to question 6.

In the Forecast Cash Flow Section (Row 33) you will calculate the annual cash flow from constructing and running the car wash.

6. In Row 35 calculate the Operating Cash Inflow from the new car wash. The cash inflow is equal to the depreciation expense plus the Earnings before Tax.
7. In Row 36, calculate the Operating Cash Outflow which are the taxes paid as calculated in step 6 f).
8. Next, enter the investing cash flows the purchase cost (Year 0) and the salvage values (Year 15) for the car wash. Make sure that you enter numbers that show the cash outflow and inflow from investment and salvage.
9. Calculate the net cash flow for year 0 to Year 15.
10. Using the yearly net cash flow figures calculated in step 9, calculate the net present value (NPV) of the investment in the new car wash. Yasmin wants the car wash to show at least a 7% rate of return.
11. Calculate the internal rate of return (IRR) for the investment.
12. At the bottom of the worksheet write your recommendation: Yasmin should / should not build the car wash.
13. Save and close the workbook.