Part 1: Pivot Tables in Excel

A Pivot Table is a way to present summarized or aggregated information in a report format. With a Pivot Table, you can quickly view different values and sets of valuable information from your data set. In this project, you are going to create a Pivot Table using an Inc. 5000 data set. With your Pivot Table, you will be able to make accurate interpretations and decisions.

1. Open the Inc5000CompanyList.xlsx data file. This data set includes company information for the fastest growing, privately held companies in 2014.

2. Create the Pivot Table shown in the screenshot below. Place your Pivot Table in a new worksheet and name the worksheet “PivotTable”.

A. The Pivot Table includes the average of revenue by industry and city, filtered by state. The state is set to Arizona. The number format of the output is currency with 0 decimal places.

3. In another new worksheet named “Responses”, answer the following questions. You will need to slightly modify the filtering or values in your Pivot Table to get the results you need.

A. Cell A1: Exclude the Health industry from the output, then answer this question. Without considering Health, which city in Arizona included the highest average revenue for Inc. 5000 companies?

B. Cell A2: When only considering the technology industries of Computer Hardware, IT Services, Software, and Telecommunications, which city in AZ had the highest average revenues for technology industries?

C. Cell A3: If you exclude “Travel and Hospitality” and “Health” from the output, which city in AZ was the most successful for fast growing, privately held companies (the city with highest average revenues)?

D. Cell A4: Remove city, state, and industry from the Pivot Table output. Change your Pivot Table to show average revenue for all states. Which state had the highest average Inc. 5000 companies revenue over all?

E. Cell A5: Keep your Pivot Table output so that information for all states is still showing. Modify your Pivot Table to answer the following question: Which state had the most companies on the 2014 Inc. 5000 company list?

Part 2: Cost Benefit Analysis

The next part of this project includes a cost benefit analysis. The purpose of a cost benefit analysis is to determine the long-term financial benefits of a proposed plan or investment. This analysis helps in making data-driven decisions. In this project, you will calculate the costs and benefits of a proposed new stadium for a city.

1. In your existing workbook, create a new spreadsheet named “CostBenefit”. Enter the information shown in the screenshot below. Be sure that the information is entered in exactly the same cells as shown. The information is the stream of costs and benefits (in millions) estimated for a proposed city baseball stadium. Year 0 represents the initial investment, while costs for years 1-10 are the maintenance costs incurred at the end of each year. The benefits are the revenues from sport team contracts and other revenues at the end of each year.

2. Make the following formatting changes:

A. Change the format of the cells in range B4:D14, F4:F16, and B21 to the Accounting number format with two decimal places.

B. Change format of cell B18 to be in the Percent format with two decimal places.

C. Format B22 to a percentage format with three decimal places.

3. In cells D4:D14, enter a formula to calculate the Total Benefits for each year of the project. The Total Benefits for each year are equal to the Benefits minus the Costs in each year.

4. Next we need to discount the future costs and benefits to put them into today’s value (i.e., find the present value). Enter 10% for the discount rate in cell B18.

5. We now need to calculate the discount factor for each year. The yearly discount factor will go in cells E4:E14. For cash flows in the future, the formula is 1/(1+i)^n, where i equals the discount rate and n equals how many years in the future you’ll receive the cash flow. In this scenario, the discount rate for a cash flow two years away is 1 divided by 1.10 squared, or about 83 percent of today’s value. To calculate the discount factor for year 0, use the following formula: E4: = 1/(1+$b$18)^a4. Copy this formula to the other cells in the discount factor range. (This formula is 1 divided by 1 plus the discount factor, raised to the power of the year number.)

6. In cells F4:F14, enter a formula to calculate the present value of the benefits for each year. This is simply the benefits multiplied by the discount factor for each year.

7. Next you need to use a formula to calculate the Net Present Value (NPV) in cell F16. The NPV is the sum of the Present Values over the life of the investment.

Using Excel functions: Next you’ll use built-in Excel financial functions to calculate similar information without the intermediate steps.

8. In cell B21, use the NPV function to calculate the NPV for the benefits for entire project. The value you get should match the NPV value you calculated in cell F16.

9. Answer the following questions:

A. Cell E18: With a 10% discount factor, what does the NPV of $ ( 8.11) tell you about the benefits of the project?

B. Cell E19: Would you approve the project under these conditions?

C. Change the discount factor to different percentages. In Cell E20, enter in the value of the discount factor that would make the stadium project financially beneficial or positive. (In other words, at what discount rate would the project be worth pursuing?)

15. Another helpful function for a cost-benefit analysis is the function that finds the discount rate that returns a net present value of $0. This is done by calculating the Internal Rate of Return with the IRR function. In cell B22, use the IRR function to determine the Internal Rate of Return (the discount rate that results in an NPV of $0).

16. In Cell E21, write your interpretation of what the value in Cell B22 indicates.

At this point, you can change the discount rate to see how that affects the Net Present Value of the stadium project. If your formulas and discount rate are set back to 10%, your completed spreadsheet should look similar to the one below.

Save your workbook with both parts of the project in a single Excel file. (Your workbook should for this project should have the Inc. 5000 worksheet, the Pivot worksheet, the Responses worksheet, and the CostBenefit worksheet.)