Dream Vacation Assignment (35 pts) Step 1: Plan Your Vacation You are on a mission to plan your dream vacation. You are given $5,000 and can take as many (or as few) people for as many days as you’d like – as long as it fits in your budget! Using Open Office Spreadsheet, you will show how you are calculating the cost of your dream vacation. You can use the internet and other travel sites (expedia or Travelocity) to gather information for your trip (transportation, lodging, activities and food costs). Step 2: Create a Spreadsheet Set up your page so that it is landscaped. You can do this by clicking File -> Page Preview from the menu and select -> Format Page. Next set up four columns and include the following data: Transportation – how much, what type, etc. Lodging – where you will stay and how much it will cost you per day Food – Estimate how much you will spend per meal/per person. You will need one restaurant local to the area for each day spent. Saying you eat all you meals at McD’s is not an option. Activities – Plan at least one activity per day. Include how much this will cost. Here’s the catch: You MUST spend exactly $5,000 on your vacation, and you can not return with any leftover money. Step 3: Show Your Calculations** In addition to the expenditures per day, you will have at least 11 formulas. You MUST show calculations/formulas for the following: 1. TOTAL Transportation - Cost for the group Formula: =sum(cells) 2. TOTAL Lodging - Cost for the vacation for the group Formula: =sum(cells) 3. TOTAL Food - Cost per person for the length of the vacation and the cost for the group for the entire trip Formula: =sum(cells) 4. TOTAL Activities Cost - Cost of activities for everyone Formula: =sum(cells) 5. TOTAL Activities spent per person - Place this formula below your activities column Formula: =Total Activities/# of people 6. AVERAGE Activities Cost - A cell that calculates the average of the prices for all the activities; place this formula below your activities column Formula: =AVERAGE(activity cells) 7. TOTAL Cost - entire vacation for everyone participating; be sure your grand total is $5,000 exactly Formula: =__+__+__+__ Think of Excel as a calculator, and let the program do the calculations for you with simple formulas. Excel can also help you with simple operations, such as adding (use = and the +), subtracting, (use = and the -) multiplying (use = and the *) and dividing (use = and the /). All formulas must begin with an = sign. When using the SUM formula, you do not need to use + signs or commas. Simply drag your mouse down the column between parentheses. Please be sure to view the “Formula Example” located in the tutorials. NOTE: Do not place the numerical data in the same cell as your wording details or titles. This will cause your formulas to not work properly. Be sure to check out the Excel Troubleshooting Tutorial for more assistance. Step 4: Add Formatting Enhancements Additionally you'll need to include the following formatting features: 1. 4 background colors 2. A title which uses merged cells (Between 3-8 cells, depending on the width of your data) 3. 2 different font sizes 4. Gridlines appropriately placed on your spreadsheet 5. 2-4 appropriate pictures Step 5: Add a Breakdown of Costs Table Once you have completed your 4 categories, you will l need to create a "Breakdown of Costs" table, which is a summary of your TOTALS from each category. Make sure you use formulas (i.e. =total cell) when creating this table so that when changes are made above, they are automatically reflected in your Breakdown of Costs table. Refer to the student examples located in the tutorial if you need assistance. IMPORTANT: When creating your Breakdown of Costs table, be sure to NOT have empty cells between your titles and numerical data cells. This will cause your pie chart to be incorrect. Place your totals in the cell directly adjacent to your titles. Step 6: Add a Pie Chart Include a pie-chart that shows the expenditure totals for your transportation, lodging, food and activities. To do this, highlight the data from your Breakdown of Costs table. (Do not include the “Breakdown of Costs” title or the grand total equaling $5,000). You will also need to meet the following requirements: Give your pie chart a title. Make sure the legend is correctly labeled “transportation, lodging, food, and activities.” Do not label it numerically. Place the percentage values by each section of the pie chart. Insert your chart into the current spreadsheet, and resize it to fit appropriately. Enhance it (optional) by double-clicking on your pie chart to change the colors, font, font size, etc. Check Your Work Did you…? Give specific details to your four categories? (8 pts / 2 pts each) Category column width and heights are appropriate (4 pts /1 pt each) Set your paper up in Landscape format? (1 pt) Format your numerical cells to "currency?" (1 pts) Calculate totals using correct formulas? (4 pts / 1 pt each) Use four background colors? (1 pts) Merge your title cells? (1 pts) Use two font sizes? (1 pts) Include an activity average and cost per person formula? (2 points each) Include a "Breakdown of Costs" table (5 points) with the correct formulas? (5 pts/ 1 pt each) Create a pie chart that shows the totals from your spreadsheet? (2 pts) Title your pie chart, label it correctly, and use percentage values? (3 points) Is the overall spreadsheet easy to follow and read, and well-organized? (2 points) **Formulas ARE REQUIRED in order to successfully show mastery of this assignment. You will be deducted 20 points for not using all required formulas, and you may be asked to resubmit your assignment. Example Assignment: Submit Your Assignment Please upload your excel spreadsheet to the Dream Vacation dropbox.