WPC-300_Assignment1

pdf

School

Arizona State University *

*We aren’t endorsed by this school

Course

300

Subject

Computer Science

Date

Dec 6, 2023

Type

pdf

Pages

Uploaded by utursuno

WPC-300 Assignment-1 Answer Key Before starting: Step 1: There are two files for this assignment. The pdf is the assignment instruction and the “.xlsx” format one is the data provided. You need to open the GraduateInnData.xlsx file in Microsoft Excel. Answers: 1. In column J, add the index name: Number of Days like the below image: Then we need to put the formula for the new column.Following is the formula for the first index of the new column. Formula: No. of days = Departure Date - Arrival Date Use the formula in column J3 of the excel document: (as shown below). Do not forget to change the number format by changing the format to general in the number section of the ribbon bar(menu bar). This is also shown in the image: So, this is for the J3 column. Then we can drag to get the result for the remaining ones as follows.

2. We have used the IF function to specify the condition if the number of guests is above or below 2. If it is above 2 then only we have added the additional cost using Vlookup. In column K, add the index name: Daily rates considering # of guests Use the following formula on column K3: =IF(H3<=2,I3,I3+(VLOOKUP(E3,$N$10:$O$13,2,)*(H3-2))) Here, I have inserted the Additional cost table for each type of room in the columns from N10:O13. You can insert the additional cost table anywhere in the excel, and change the formula while using VLOOKUP accordingly. “$” sign is inserted before the columns which are to be fixed during the execution of the formula. Once you’ve applied the formula to the column K3, drag to get the result for the remaining ones as shown in the image below:

3. Here we have again used the IF function to provide discounts if the total number of stay days is more or equal to 7. In column L, add the index name: Revenue collected Use the following formula on column L3: =IF(J3>=7, 0.9*(K3*J3),K3*J3)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

4. For calculating the grand total revenue: Use the following formula in cell L33: =SUM(L3:L32) Ans should come out to be: $23,128.95 5. Select the whole document and click on Insert on the ribbon menu. Select pivot table from it and it should ask you options to include in the table in the rand hand side column. Select “room type” for rows and “revenue collected” for the Summation in colum ns. It should produce a pivot table for you in a new excel sheet. It looks as follows:

6. In the same sheet as in Q5, select the same cells- A1:B4. Click on the pie chart button (2D) to create a pie chart categorized by Room Type. Refer to the below figure for the pie chart:

The pie chart looks as follows:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Related Documents

Lab 24 (IPv6 - Subnetting) Tesah Capers.doc

CSCO 220 Modules 3 & 6 Lab Packet-NAT.docx

Quiz 4 Attempt 1.docx

CybercrimePreventionGuide_SByrd - Copy.docx

Annotated Bibliography_Wk2_SByrd.docx

CS 100 Ethics in IT.docx

CS115_Fall_2023_Lab09_Description (2).docx

CS115_Fall_2023_Lab10_Description.docx

CP4P_CompressionBackup_Activity_Instructions.pdf

CSCO 220 Modules 9 and 10 Lab Packet-QoS and Network Management.docx

Lab 16 (Port Security) Tesah Capers.doc

RA 2 Counting Lines & Words (Fall 2023).pdf

Recommended textbooks for you

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE

Computer Science

ISBN:9780357392676

Author:FREUND, Steven

Publisher:CENGAGE L

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:9781337102124

Author:Diane Zak

Publisher:Cengage Learning

New Perspectives on HTML5, CSS3, and JavaScript

Computer Science

ISBN:9781305503922

Author:Patrick M. Carey

Publisher:Cengage Learning

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

CMPTR

Computer Science

ISBN:9781337681872

Author:PINARD

Publisher:Cengage

SEE MORE TEXTBOOKS

Recommended textbooks for you

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
New Perspectives on HTML5, CSS3, and JavaScript
Computer Science
ISBN:9781305503922
Author:Patrick M. Carey
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
CMPTR
Computer Science
ISBN:9781337681872
Author:PINARD
Publisher:Cengage