Fluent Essays

MTH 154: Quantitative Reasoning Project – Splitting Vacation Costs Project Overview: In this project, you will plan a 4-day weekend trip (Friday through Monday) for you and 5 of your friends/family. You will research the cost of the group vacation, assuming that you will pay for all costs up-front with everyone paying you back at the end of the trip. You will use Excel to track these shared costs and then calculate how much money each traveler owes you. This project will have more meaning to you the more it reflects your real life, however, I am not requiring that you provide me with your personal details. You can use this opportunity to research a realistic upcoming trip, a fun dream vacation, or a fictitious scenario entirely. I will be assessing your work only on if it was thoughtfully researched, accurately represented, and appropriately interpreted. Project Directions: 1. Choose your vacation and research some of its costs (20 pts) (a) Choose a geographic location for your vacation. Make sure you are specific, as you will put this information into your discussion paragraph (see instruction 3. below), and you will use it to research the following costs. (b) Research lodging expenses for the weekend. i. It is your choice what type of lodging you choose for this vacation. (Hotel, inn, bed and breakfast, luxury suite, rental condo, etc.) But your lodging must meet the following restrictions: • It must cost money for all 3 nights of the trip. (No crashing on a friend’s couch for free.) • It must have enough bedding for all 6 travelers. (No cramming everyone into 1 tiny hotel room.) Hint: look for key terms like “occupancy”, “number of guests”, or “sleeps x people” when you search for lodging. This information must be clearly displayed in your screenshot. • It must be geographically realistic based on your vacation’s location. ii. You must provide price justification for your lodging selection: • Include the url (or other citation) referencing where you found the information. • Include a screenshot of clear, readable quality which shows: price, location, and occupancy. CAUTION: Do NOT take a photo of your computer screen with your cellphone and hand it in as a screenshot! (c) Research two activities for your group – one for Saturday and one for Sunday. i. It is your choice what types of activities you choose. (For instance: museum, concert, amusement park, sporting event, theater show, boat rental, ski lift tickets, guided tour, surfing lessons, etc. Have fun with this!) Your activities must meet the following restrictions: • They must cost money. (Free museums, parks, monuments, walking tours, etc don’t count.) • All 6 travelers must take part in both activities. • They must be geographically realistic based on your vacation’s location. ii. You must provide price justification for your activities: • Include the url (or other citation) referencing where you found the information. • Include a screenshot of clear, readable quality which shows: price and location. CAUTION: Do NOT take a photo of your computer screen with your cellphone and hand it in as a screenshot! 1 (d) Come up with three shared costs for your group for each day of the trip, in addition to lodging and your activities. You do not have to provide any research or price justification for these costs, but they are there to make the project more realistic. Ideas for these include: breakfast, lunch, dinner, snacks, transportation, parking fees, shared souvenirs, or additional activities beyond the two that you researched. These costs can be the same every day (e.g. breakfast, lunch, dinner), or they can differ; but you must have 3 additional shared expenses each day. 2. Create an Excel workbook that will assist you in splitting the cost of your vacation. (60 pts) Your workbook will have 5 sheets: 1 for each day of the trip, and 1 summary worksheet, described below. Be sure to label the sheet tabs with appropriate descriptions for each sheet. Sheets 1-4 : Worksheets for each day of the trip (Friday - Monday) (a) On each worksheet, you will track the costs for each day, and who participated in each of the costs. To do this: write each person’s name, a description of the cost items, the amounts for each cost item, and then put an indicator (such as an “x”) to denote whether each person participated in each cost item. (b) To make the project more realistic, you should assume the following: • 2 of the travelers will show up late, on Saturday—they will be excluded from all Friday costs. • 1 traveler will leave early, on Sunday – that person will be excluded from all Sunday night costs and all Monday costs. (c) Use Excel to calculate the number of people to split each cost between, based on the number of travelers who participated in the cost item. Then calculate the per-person cost of each expense. Note: • You must input formulas that link to other cells in the sheet. Do not do these calculations manually. • Excel’s COUNTIF function will be very helpful. You can find additional information on it through Microsoft’s support pages for Excel, here: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34 (d) Use Excel to calculate the daily subtotal each person owes towards the vacation, based on whether or not they participated in each cost item. Note: • You must input a formula that links to other cells in the sheet. Do not do this calculation manually. • Excel’s SUMIF function will be very helpful. You can find additional information on it through Microsoft’s support pages for Excel, here: https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b (e) Helpful hints: • All your calculations must be setup so that if anything changes (e.g. the cost amounts or the x’s indicating who participated), then the “number to split between,” “per person cost,” and “person’s daily subtotal” are automatically updated by Excel. I will check this to make sure it works! • Make sure that all cells have the correct formatting and number of decimal places to appro- priately represent the information in your sheet (e.g. currency). 2 https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34 https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b • Based on the information from instruction 1., your expenses for each day are as follows: Friday – lodging and 3 shared costs Saturday, Sunday – lodging, 1 activity, and 3 shared costs Monday – 3 shared costs • If you are unsure how to organize your daily sheets, below is an example layout for Sunday: Category Lodging Activity [Shared Cost 1] [Shared Cost 2] [Shared Cost 3] Cost $ – $ – $ – $ – $ – Person Participation Person’s Daily Subtotal [Person 1] x x x x x $ – [Person 2] x x x x x $ – [Person 3] x x x x x $ – [Person 4] x x x x x $ – [Person 5] x x x $ – [Person 6] x x x x x $ – Number to split between - - - - - Per person cost $ – $ – $ – $ – $ – Sheet 5 : Summary sheet On this sheet, you will combine how much each person owes for each day, and then determine how much everyone owes for the trip. (a) Create a column for each day, which gives the amount each person owes for that day. Input this information by linking to the other worksheets, not by manually entering numbers. (If the numbers change in one of the previous worksheets, they should automatically change here, too.) (b) Create a column which sums up the total amount each person owes for the trip. (c) Create a single cell which sums up the grand total that you will be paid back for the trip. (d) Helpful hints: • For the “Total Owed” and “Grand Total” cells, you must input a formula that links to other cells in the sheet. Do not do these calculations manually. • Make sure that all cells have the correct formatting and number of decimal places to appro- priately represent the information in your sheet (e.g. currency). • If you are unsure how to organize your summary sheet, below is an example layout: Amount Owed Person Friday Saturday Sunday Monday Total Owed [Person 1] $ – $ – $ – $ – $ – [Person 2] $ – $ – $ – $ – $ – [Person 3] $ – $ – $ – $ – $ – [Person 4] $ – $ – $ – $ – $ – [Person 5] $ – $ – $ – $ – $ – [Person 6] $ – $ – $ – $ – $ – Grand Total You will be Paid Back: $ – 3 3. Write a short (1-paragraph each) discussion and reflection of the project, addressing the following. (20pts) (a) Discussion: i. Give the (basic) story behind your vacation. What geographic location did you choose, and what will you do on your vacation? What factors went into choosing your lodging? Is this a “real” vacation you anticipate taking, a future dream vacation, or something fictitious entirely? Who showed up late, and who left early? ii. Consider what would happen if you didn’t pay for everything yourself. (For example: you paid for lodging, another traveler paid for the activities, another traveler paid for the meals, etc.) How might you go about figuring out who owes how much money to whom? Note: you do not have calculate anything or change your spreadsheets. Just think through and describe how the problem becomes more complicated, and where you might add in some additional calculations. (b) Reflection: i. What did you learn from this process of researching vacation costs, and inputting it into a spreadsheet? ii. What were some of your struggles and successes while doing this project? iii. In what other situations in your life would it be useful to create another sheet to split costs between friends/family? Turning in the project: You will turn in this project via Canvas. You should upload two different files: 1. A single document containing your “Price Justification, Discussion and Reflection” – This single document will include your cost research for lodging and activities (urls and screenshots showing the required informoation), your discussion, and your reflection. This document must be a file of the type .doc, .docx, or .pdf. 2. An Excel workbook containing all 5 required sheets for this project. Your Excel workbook must be a file of the type .xls or .xlsx. Tips and Pointers: The goal of this project is to apply what we have learned about logic, reasoning, problem-solving, and research skills to a real-world scenario that has meaning to you. While you should not go overboard on researching a vacation, you should put in enough effort so that your spreadsheet is meaningful. You should start with a blank Excel sheet and create all of the information from scratch, so that you understand how Excel works. Make sure your formatting and presentation of the information in Excel is organized, clear, and consistent, and ensure that everything is labeled appropriately and the sheets are setup so that data entry is easy. Make sure to have Excel do the calculations for you — enter formulas instead of manually calculating things by hand. When you’re done, play around with some of the numbers and participation x’s to make sure that the rest of the spreadsheet updates automatically. Be careful and do not “format as table” when formatting your sheets for visual appeal. Although Excel tables look great, using them is more advanced than we have covered in this class. The formulas you enter won’t look as you’d expect, and it’s easy to get confused with tables unless you’re already an Excel expert. 4