100 points Due: Thursday, July 27 at 11:59 pm

The Final Project integrates the database, spreadsheet, presentation, and word processing skills that you have learned this semester. The purpose of the project is to use these skills to a real world problem. Please read through the entire assignment before proceeding. It is recommended that once you read the problem you consider how to best organize the data. A little time now spent in planning may save you a great deal of time later, when executing a solution.

Many countries and athletes are looking forward to participating in the 2016Olympic Games in Rio de Janeiro, Brazil but both planning and fund raising will be necessary in order for the event to go smoothly. The Olympic Planning Committee has started an initial database to keep track of a some of the different teams expected to be sent to the Olympics and the accommodations that will be available for them, including hotels, meals, and transportation.

You have been hired by the Olympic Committee to utilize the data that has been collected in order to formulate financial estimates for country delegation costs. In turn, you will present these estimates to the planning committee. The Olympic committee database (Olympics SP 16 Final Project.acc) contains the following tables:

Teams - This table contains a list of all participating sports teams. A country delegation can have multiple teams – one for each Olympic sport. The data contained in the table includes a unique team id, the country the team is representing, the sport, the number of athletes, the number of support personnel and the name of the team coach who also serves as the contact person for the team.

Hotels- This table lists the names of all participating hotels that will be housing teams for the Olympic event. These hotels will be the only places where teams are authorized to stay. The table includes the contractual agreements these hotels have with the Olympic Committee regarding room rates for the various different types of accommodations (Deluxe, Superior and Standard). Each hotel is identified by a unique hotel identification code.

Teams_Hotels - This table contains a list of accommodation assignments by team. Each team, identified by their TeamID, is assigned to a hotel, identified by the corresponding HotelID. While each team is only assigned to a single hotel, hotels will able to house multiple teams.

CountryData – This table lists the name of each country with Olympic delegations, a delegation contact, also included are the following:

  • Country currency symbol abbreviation
  • Exchange rates toBRL(Brazil currency unit Brazilian Real)from the specified country based on the current exchange rates. For example, the current rate from USD to BRL dollars is 3.83 indicating that 1 USD is worth 3.55 BRL. (To calculate the value of 1 BRLin US dollars – divide the number of BRL by this exchange rate 1BRL/3.55.281 USD). Keep your calculations in BRL until you are ask to convert in Part 1 section E.

During the first part of your analysis you will compile the data provided to you by the Olympic Committee to calculate the costs each delegation will be responsible for once they arrive at the Olympics. These include the cost of food, accommodations, and transportation. After that, you will analyze the compiled data and evaluate several possible scenarios under the request of your boss. Finally, you will create a presentation for the Olympic Committee and a form letter to be sent out to various sports delegations informing them about the projected costs.

Please read over the entire project before proceeding with any of the specific tasks, so that you can best plan how you will carry out your project. The following the specific directions given.

PART 1–COST ANALYSIS

A. Determining Food Costs per Day

The Olympic committee has decided that all participants will be using meal passes throughout the Olympic event. Each meal pass will be valid for one person for a single day and will allow the holder to eat at any of the major Olympic venues and hotels for any/all meals - including breakfast, lunch and dinner.

To estimate food costs for each country’s delegations, first summarize by country the number of Olympic participants by category (athletes, support staff and coaches). Then in a separate step you will summarize the total number of participants by country and the associated food costs.

  1. Summarize the Number Of Participants By Country.

Summarize the number of athletes, support personnel and coaches by country. Organize the data in the following format. The ‘Total members’ column should contain the total number of athletes, support staff and coaches for all teams for the corresponding country. Sort this list alphabeticallyby country. Name your final queryParticipantSummary.(Hint: it may be simpler to complete this in 2steps, though this is not required.)

Country / # of athletes / # of support personnel / # of coaches / Total members

Copy the results of your analysis to an Excel workbook named Olympics.xlsx and name the worksheet Participants.

  1. Calculate Daily Food Cost For Each Country.

Assuming that daily meal passes have a flat cost of 40BRLper person per day, summarize the daily food cost for each country.

B. Determining Hotel Accommodation Costs per Day

In this next section you will calculate the cost of hotel accommodations by country. Remember that each team is assigned to a specific hotel, but since each country’s delegation may consist of multiple teams,totaling costs will involve summarizing costs by hotel first and then by country. You will also need to take into account what types of rooms will be assigned to the different personnel (athlete, support personnel or coach) and whether those rooms are shared.

To complete these calculations you will need the following additional information based on the guidelines provided by the Olympic Committee:

  • Only coaches will stay in Deluxe Rooms - one person per room
  • Support Personnel will be housed in Superior accommodations assuming three people per room.
  • Standard roomswill be used to house athletes assuming two persons per room.

Because of security concerns, the Olympic Committee has prohibited room sharing among athletes of different countries or participants of the same country but belonging to different categories (i.e. mixing between athletes, coaches or support personnel). For example:At Hotel X, if country Y has 2 coaches, 10 support personnel and 15 athletes, it’s going to need 2 deluxe rooms, 4 superior rooms (10/3 3.33 rooms resulting in 4 rooms needed), and 8 standard rooms at that hotel. For an accurate count, the number of rooms needed should be rounded up to the nearest whole number per the details below.

  1. Compile data by Hotel by Country

Create a list of Hotelsand countries containing the total of each type of participant (athlete, support personnel, coach) from a given country and the price of each room type (Standard, Superior and Deluxe) for that hotel. Organize the data as seen in the table below. Save the data to a query named HotelSummarybyCountry.

Hotel ID / Hotel Name / Country / Total of Athletes / Total of Support personnel / Total of Coaches / Price per night for Standard Room / Price per night for Superior Room / Price per night for Deluxe Room
  1. Calculate the Total Cost per Night by Hotel by Country

Use the hotel summary data to calculate the number of each type of room that will be required and the room costs by country for a given hotel as follows:

  • Copy the results of your HotelSummarybyCountry query into an Excel worksheet named HotelCosts in the Olympics.xlsx workbook.
  • Calculate the number of rooms needed for each type of participant for each country at a specific hotel. To round up the number of rooms – use the help feature if needed to use the roundup function.
  • Once the number of rooms for each type of participant has been determined, calculate the total nightly costs for this country at this hotel for each category. Then calculate the total nightly cost of all hotel rooms.
  1. Calculate The Total Number Of Hotel Rooms For That Country In That Hotel

Calculate the total number of hotel rooms needed for the corresponding country and hotel. Label the column Total Number of Rooms.

  1. Summarize Hotel Costs

On a separate worksheet named HotelSummarysummarize total hotel costs and number of rooms by country. Each country should be listed only once.Your final table should look similar to the structure given below.

Country / Total Room Costs / Number of Rooms
  1. Determine Final Hotel Costs per Day After Discounts

The Olympic Committee is willing to give discounts to some country delegations based on the size of their delegations and if they agree to handle all of the administration such as assigning hotel rooms to each participant and keeping track of keys. The committee has given you category guidelines for these discounts and asked you to calculate costs based on these guidelines. To complete this task you will do the following:

  • On the HotelSummary worksheet in an adjacent column, determine the delegation size. A delegation is considered to be ‘Large‘ if it will need 50 or more rooms, ‘Medium‘if using at least 20 rooms but less than 50 rooms, and ‘Small‘‘ if using less than 20 rooms.
  • Determine the discounted hotel costs by country based on the following criteria:
  • If the country has a large delegation then the discount is 16%.
  • If the country has a medium delegation then the discount is 9%
  • If the country has a small delegation then the discount is 0%
  • Create a table with this information and use a lookup function to calculate the discounted hotel costs.

D. Determining Transportation Costs per Day

As an agreement between the Olympic Committee and the participating hotels, Transportation cost for all athletes is 1.5 BRL per athleteper day. However both support personnel and coaches will be required to pay a daily usage fee of 5BRL.

On a new worksheet named TransCosts in the Olympics.xls workbook, list the country name, the number of participants of each type, and calculate the cost of transportation for that country – sorted alphabetically by country. Be sure to explicitly list the costper participant type on your workbook and use cell reference in your formula.(Hint: you already have information about the number of participants of each type on worksheet Participants).

E. Summarizing Delegation Costs per Day

  1. Create a new worksheet in the Olympics.xlsx workbook named Summary. List each country in alphabetical order and create columns in your worksheet to list the following data, referencing the appropriate cellsyou’ve previously calculated:
  • Country
  • Total number of participants (athletes, support staff and coaches)
  • Food Costs per day in BRL
  • Total discounted costs of hotel rooms per day in BRL
  • Total Transportation Costs in BRL per day
  1. In an adjacent column Calculate the total cost per day by country in BRL
  2. In an adjacent column, copy the list of exchange rates by country from the Access database. Then in a second adjacent column calculate the total cost per day in the corresponding country’s currency (ie: for the US calculate the equivalent number of dollars (USD), for Russia the number of rubles (RUB) etc).
  3. Determine if this acceptable to each country. It is acceptable to each country if the total food costs per day are less than 3000 BRL or the total transportation cost is less than 250 BRL.
  4. Determine if this acceptable to the Olympic Committee for each country. It is acceptable to the Olympic Committee if it is acceptable to the country and the total costs per day are less than 21,000 BRL.
  5. Using an Excel function determine the number of countries that are not acceptable to the Olympic Committee. (based on the criteria of number 5 above)

F. Summarizing Delegation Costs for the entire Olympic Stay

  1. On the Summary worksheet in an adjacent column to the total costs per day in the corresponding country currency, estimate the costs for the entire 3-week Olympic period foreach countryin the country’s currency.Assume the all participants stay the entire 3 weeks.
  2. In a section below your data complete the following calculations.
  • Determine, for all countries combined,the total number of participants, total costs for each category (food, hotel etc) and total for all categories combined in BRL.
  • Similarly, determine average number of participants per country delegation, the average cost of each item and average total cost in BRL.

G. Create Charts

1. Create a chart showing the total dailycosts in BRL by country. Insert this chart as a new worksheet in the Olympics.xlsx workbook named Chart1. Include a title.

2. Create a second chart showing the relative percentages of daily costs for all countries combinedby cost category (food, hotel accommodations,transportation). Place this chart on a new worksheet in the Olympics.xlsx workbook named Chart2. Include a title, legend, and data labels with percentages.

H. Format your worksheets

Format your worksheets so they are clear and easy to read. Modify headings so they are easy to read. Be sure to use cell references where needed.

PART 2: FUNDING Options

After reviewing the list of teams registered at the different hotels, it appears that there may be insufficient space. The committee is considering renovating existing buildings in order to create a small Olympic Village which can accommodate additionalcountry delegations. The cost of the renovation is estimated to be about 12,000,000 BRL. You will be considering some financing options for renovating this space. Create a new worksheet named Financials in the Olympic workbook to place your calculations. Be sure to label each option clearly and explicitly list your data inputs.

-Option 1: The private bank London Stars has agreed to offer you a loan for the full cost of renovation. There is no downpayment needed. The monthly payments will be 375,000 BRL. The loan duration is 3 years.Calculate the annual interest rate for this option. Highlight your answer in bright green.

-Option 2: Because Olympic Village is owned by the government, 10% of therenovation cost will be paid by the government and thenthe rest of the money can be borrowed from the Bank of Brazil. The Bank of Brazil has offered the Olympic Committee a rate of 5% compounded quarterly. The committee has indicated that they will be able to make paymentsof 650,000 BRL per quarter. How many years will it take to pay off this loan? Highlight your answer in bright green.

PART 3: ALTERNATE SCENARIO

The Committee is still concerned about budget overruns and is considering imposing a higher transportation fee for athletes. If the total revenues raised from transportation fees (total country costs for transportation per day) weredoubled, what would the charge per athlete per day have to be assuming the charge for non-athletes remains at 5BRL per day? Complete the following steps:

  • Create a copy your Olympics.xlsx file named AltOlympics.xlsx.
  • Complete your analysis to calculate this value in the new file.
  • Insert a worksheet at the beginning of the AltOlympics.xlsx file named AltScenariowith a tab color of red to explain what charge is needed and how you completed this analysis. Highlight the new charge in bright green.

PART 4: PRESENTATION

Now it’s time to present your work to the chair of the Olympic Committee. You have decided to use a PowerPoint presentation to summarize your results. Your presentation should also include at least one slide transition and one animation effect, a title page with your name, and a uniform design format. Name your presentation file OLYMPIC.PPT.

Create a professional presentation that includes, at a minimum, the following:

  • A list of major participating countries (per our write-up) including the number of members of their delegation & the number of rooms required. Show charts for total daily costs by country and by total costs for all countries by cost category.
  • A discussion of the Olympic Village renovation financing options.
  • A discussion of the effects of the alternate scenario.

PART 6: MAIL MERGE

The committee has asked you to prepare a letter to send to all the Olympic delegation heads to inform them about the expected accommodation, food and communication costs associated with delegation. You have decided to use mail merge to accomplish this task. Complete the following steps:

  • Preparing data for mail merge.

Based on the original Olympic case, create a separate worksheet named Mail Merge Data with the following format and information: