Need help finding the formulas for these.
The manager at the Graduates' Inn has asked you to do some analysis of December's transactions. The December Guest worksheet provides a transaction list of customers, rooms they occupy, arrival and departure dates, number of occupants, and daily rate data for the month of December. Guests have a choice of rooms based on availability and room rates. Room rates are the same for one or two guests; however, additional guests must pay an additional fee which is per person, per day for meals and other miscellaneous expenses. Guests staying for seven days or more receive a 10% discount which is applied to the room total before add-ons and taxes. The manager also gave you a worksheet with the January Bookings. He would like you to do calculations that he can use to help him schedule the cleaning staff for the next month. The Quincy Adams and Truman rooms are the least popular rooms reserved at the Inn. Because of this the manager has decided to remodel the rooms. The manager has asked you to complete the "Remodel" worksheet to help him schedule employees for the remodel. Calculate the number of nights for the first transaction. Calculate the number of nights for the first transaction. - Copy and paste the formula down the column to calculate the days for the other transactions. Calculate the "Room Total" for the first transaction. - Use an IF function to calculate a 10% discount for any stay greater than or equal to 7 . - Copy and paste the function down the column to calculate the room total for the other transactions. Calculate the "Add-On Total" for the first transaction. - Use an IF function with a nested VLOOKUP to calculate the total based on the number of guests and the room type. - Use the data in P4:Q6 to lookup the cost based on the room type. - The add-on fee is per person per night. - If there is no add-on fee display a zero (0) in the cell. - Copy and paste the function down the column to calculate the add-on total for the other transactions. The manager would like some details on revenue and taxes. - Calculate the "Total With Tax" for the first transaction. - Copy and paste the formula down the column to calculate the total with tax for the other The manager would like some details on revenue and taxes. - Calculate the "Total With Tax" for the first transaction. - Copy and paste the formula down the column to calculate the total with tax for the other transactions. - In cell Q12, calculate the "Total Revenue Before Tax". - In cell Q13, calculate the "Total Tax Collected." - Use the "Tax Rate" in cell QS and the "Total Revenue Before Tax" you calculated in Q12 to calculate the total tax collected. In cell Q14, use a SUMIF function to calculate the "Total Revenue 3 or 4 Guests." - Reference the data in columns J and N in your function. In cell Q14, use a SUMIF function to calculate the "Total Revenue 3 or 4 Guests." - Reference the data in columns J and N in your function. The manager would like a quick way to see a guests room total. Use the IFERROR, INDEX and MATCH functions to display the room total for the guest with the last name vazquez. - If the function does not retum a room cost display the text "No Match" as the results. - Copy and paste down the column to find the room cost for Hall and Tilson. The manager would like to start creating a "Guest ID" that can be used by the cleaning staff. In cell B4, create a guest ID with the pattern: Guest Last Name Room: Room \# of Guests. For example if there are two guests with the last name of West staying in the Coolidge room the guest id would be "West Room: Coolidge 2"withoutthequotation marks. - Use the CONCAT (or CONCATENATE) function. - Reference the Guests Last Name, Room and \# of Guests in your function. - Pay close attention to the spaces in the Guest ID. - Copy and paste the function down the column to create the "Guest ID" for the other transactions. On the January Bookings worksheet, in cell C35 use a COUNTIF function to count the days that have a reservation in the Adams guest room. - Use the wildeard character * to find cells with text. - Copy and paste across the row to calculate the number of days with bookings for the other rooms. In cell C36, use a COUNTBLANK function to count the number of days that do not have a reservation. - Copy and paste across the row to calculate the number of day that do not have bookings for the other rooms. On the Spring Remodel worksheet enter calculations to complete the table. - In cell EA, use the WORKDAY function to calculate the end date for the Demolition task. - Reference the "Holiday Schedule" in your function. - Copy and paste down to E10 to calculate the end date for the other tasks. - Note the calculation will show dates for 1900 until you complete the next calculation. - In cell D5, reference the date you calculated in cell E4 as the start date for the next remodel task. - Copy and paste down to cell D10 to calculate the start dates for the remaining tasks. - In cell F4, use the NETWORKDAY function to calculate the number of days each task will take to complete. - Reference the "Holiday Schedule" in your function. - Copy and paste the function down to cell F10 to calculate the number of days for the other tasks. - In cell C11, use a SUM function to calculate the total number of days needed to complete each of the tasks. - Copy and paste the function to cell F11
