Information Tools For Business/EXCEL

YO16XLCH03GRADERPCHW_-_Wedding_14_Instructions x
Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH03_GRADER_PC_HW – Wedding 1.4

Project Description:
Clint Keller and Addison Ryan have just booked a wedding at Painted Paradise Resort & Spa. When requested by a happy couple, the Turquoise Oasis Spa coordinates a variety of events including spa visits, golf massages, and gift baskets made up of various spa products. Given the frequency of wedding events at the Turquoise Oasis Spa, Meda Rodate has asked for your assistance in modifying an Excel workbook that can be used and reused to plan these events in the future.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the file named e02ch03_grader_pc_Wedding.xlsx. Save the file with the name e02ch03_grader_pc_Wedding_LastFirst, replacing LastFirst with your last and first name.
0.000
2 On the GiftBaskets worksheet, in cell F9, use AutoSum to calculate the total items in basket 1. AutoFill the function down to cell F11.
3.000
3 In cell B15, enter a formula that will calculate the price of a lavender candle after the bulk discount in cell E3 is applied. AutoFill the formula over to E15.
5.000
4 In cell B18, enter a formula that will calculate the total price for the number of lavender candles in basket 1 using the price after bulk discount. AutoFill the formula over to E18 and then down to E20.
4.000
5 In cell E14, change the price of Soothing Bath Salts to $11.99.
1.000
6 Create a named range of BasketSubtotals for the range C23:C25.
2.000
7 Edit the BasketsRequested named range so that it refers to cells B23:B25.
2.000
8 In cell F24, use the SUM function to add up all the values in the BasketSubtotals named range.
3.000
9 Use the range A18:E20 to create named ranges using the values in the left column as the names.
2.000
10 Apply the named ranges just created to the formulas in cells F18:F20.
2.000
11 On the WeddingSummary worksheet, in cell C9 use the INT function to calculate the number of whole days the wedding party spent at the resort. Decrease the number of decimal places to 0. AutoFill the function down to C39.
2.000
12 In cell E9, use the appropriate function to calculate the absolute value of the returned merchandise. AutoFill the function down to E39.
2.000
13 In cell C2, edit the function to round the result to the nearest penny.
3.000
14 In cell C1, use the MODE.SNGL function to determine the most frequent length of stay using whole days.
5.000
15 In cell C3, use a function to calculate the median amount spent at the spa by the wedding parties.
5.000
16 In cell C4, use a function that will calculate the number of wedding parties served by counting the wedding party names.
5.000
17 In cell C5, use a function to calculate the number of spa members scheduling weddings by counting the numbers in column G.
5.000
18 On the SpaServices worksheet, in cell B1, use a function to return the current date.
3.000
19 In cell C5, use the function =DATEDIF(B5,$B$1,”Y”) to calculate the age of the guest, using her date of birth and today’s date in cell B1. AutoFill the function down to C22.
3.000
20 In cell D5 use the TRIM, LEFT, and FIND text functions to extract the guest’s first name from cell A5. AutoFill the function down to D22.
10.000
21 In cell G5, use Flash Fill to separate the city from column F.
Note, Mac users, select the range F5:F22, and on the Data tab, click Text to Columns. Select Comma as the delimiter and cell G5 as the destination cell.
3.000
22 In cell H5, use Flash Fill to separate the state from column F.
3.000
23 On the WeddingFinancing worksheet, in cell B7, use a VLOOKUP function to retrieve appropriate percentage discount based on the total cost of the event in cell B3.
6.000
24 In cell B8, use a formula to calculate the amount of the discount by multiplying the total cost of the event by the percentage discount.
2.000
25 In cell B10, use the PMT function to calculate the equal monthly payment amount necessary to pay off the amount borrowed in cell B9, at the end of the term in cell B6, using the APR in cell B5. Be sure the result of the PMT function is positive.
5.000
26 In cell C10, use an appropriate function to return Sufficient Credit Score if the credit score is greater than or equal to 650 and Insufficient Credit Score if not.
5.000
27 In cell A18, use the appropriate functions to return 500 as the all-inclusive discount if the values in cells A13 and A15 are both Yes and return 0 if not. Change the value in cell A13 to Yes.
5.000
28 On the Commission worksheet, in cell B4, troubleshoot and edit the function so that it retrieves the correct value of 5.00%.
2.000
29 In cell B8, troubleshoot and edit the function so that it retrieves the correct value of $1,250 for a Manager.
2.000
30 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0.000
Total Points 100.000

Updated: 09/08/2017 1 Current_Instruction x

Nyarko_e02ch03_grader_pc_Wedding.xlsx

GiftBaskets

Quote for Customized Gift Baskets

Customized Baskets
Description of Custom Basket

Bulk Discount
10%

Basket 1
Attendees’ Unwind and Renew

Basket 2
Wedding Party Pamper Yourself

Basket 3
Parents of the Bride and Groom Stress Free

# of Items per Basket
Lavender Candle
Chamomile Body Lotion
Foot Renew Cream
Soothing Bath Salts
Items Per Basket

Basket 1
2
1
1
0

Basket 2
2
2
2
2

Basket 3
3
2
2
4

Item Price List
Lavender Candle
Chamomile Body Lotion
Foot Renew Cream
Soothing Bath Salts

Retail Price
4.99
7.99
5.99
8.99

Price After Bulk Discount

Price Per Basket
Lavender Candle
Chamomile Body Lotion
Foot Renew Cream
Soothing Bath Salts
Per Basket Price

Basket 1

– 0

Basket 2

– 0

Basket 3

– 0

Basket Orders
Baskets Requested
Basket Subtotals

Basket 1
50
$ – 0

Total Baskets in Order
10

Basket 2
8
$ – 0

Total Billing Amount

Basket 3
2
$ – 0

Prepared for:
Keller – Ryan Wedding

*To customize for a different order only modify the blue shaded boxes.

WeddingSummary

Most Frequent Length of Stay

Average Amount Spent at Spa

874.6993548387

Median Amount Spent at Spa

Number of Weddings

Spa Members Scheduling Weddings

Wedding Party Name
Number of Days Spent
Whole Days Spent
Returned Merchandise
Value of Returned Merchandise
Amount Spent at Spa
Spa Member

Arroyo
1.52

-37.34

$ 1,215.63
1

Proctor
2.54

41.12

$ 720.60
1

Minor
4.32

-12.7

$ 455.78
1

Pearce
4.52

13.97

$ 1,309.51
Null

Shea
2.50

18.53

$ 1,345.57
1

Galvan
4.00

11.09

$ 1,046.69

Crowe
2.31

36.64

$ 1,125.53
1

Meeks
2.45

47.7

$ 1,068.90
1

Kendrick
3.65

10.24

$ 604.95
Null

Mayfield
4.45

7.88

$ 299.60
Null

Kendall
3.11

21.78

$ 353.74
Null

Archer
3.15

37.51

$ 923.56
1

Holley
4.19

34.36

$ 1,285.26
Null

Boucher
4.10

5.79

$ 1,157.07
1

Childs
1.63

-2.47

$ 206.77
1

Rankin
4.50

-12.66

$ 781.30
Null

Lozano
2.55

49.74

$ 1,070.70
Null

Odell
4.42

-18.66

$ 867.45
Null

Bland
2.72

-17.86

$ 267.16
1

Rouse
1.65

24.98

$ 1,330.38
1

Haas
4.40

23.12

$ 899.77
Null

Swain
1.16

-7.23

$ 1,479.14
Null

Oconnell
2.64

1.11

$ 953.20
1

Dougherty
3.50

-0.46

$ 556.17
Null

Andersen
3.58

-19.03

$ 644.50
Null

Wang
1.91

-3.15

$ 606.30
1

Cowan
1.28

40.74

$ 955.27
Null

Elder
2.00

-16.6

$ 760.12
1

Shirley
2.59

31.05

$ 870.85
1

Hartley
3.60

25.91

$ 1,146.79
1

Felix
4.57

30.66

$ 807.42
Null

SpaServices

Today’s Date

Spa Service Requests

Guest Name
Date Of Birth
Age
First Name
Service Requested
City & State
City
State

Olivia Stone
2/20/78

Hair Treatment
Bernalilo, NM

Mia Ramos
11/11/72

Hair Color Treatment
Farmington, NM

Isabella Hudson
10/19/75

Makeup Service
Benson, AZ

Rhonda Griffith
1/2/69

Women’s Facial
Benson, AZ

Steven Lucas
2/26/70

Golf Massage
Bisbee, AZ

Elizabeth Anderson
4/14/77

Hair Color Treatment
Bernalilo, NM

Jessica Montgomery
10/6/69

Women’s Facial
Santa Fe, NM

Ella West
5/26/78

Makeup Service
Farmington, NM

Joseph Wallace
6/19/86

Golf Massage
Farmington, NM

Joshua Pierce
5/12/69

Pedicure
Bernalilo, NM

Ryan Jennings
6/5/71

Full Massage
Bisbee, AZ

Judy Brown
11/8/67

Manicure
Benson, AZ

Hannah Rowe
5/8/74

Hair Color Treatment
Bisbee, AZ

Jill Mcdonald
6/9/76

Full Massage
Bernalilo, NM

Glenn Barker
8/22/74

Pedicure
Santa Fe, NM

Mia Rivera
3/9/85

Pedicure
Santa Fe, NM

Jane Montgomery
5/12/86

Makeup Service
Farmington, NM

Susan Burgess
6/14/64

Pedicure
Santa Fe, NM

WeddingFinancing

Financing Calculator

Credit Score
725

Total Cost of Event
$ 37,000.00

Percent Discount

Down payment
$ 3,500.00

0
0.00%

Rate (Annualized)
4.00%

20000
1.00%

Term (in Years)
2

30000
5.00%

Percentage Discount

40000
7.50%

Amount of Discount

50000
11.00%

Total amount borrowed
$ 33,500.00

Monthly Payment

Does Party Wish to Include Golf Services?

Does Party Wish to Include Hotel Services?

Yes

All Inclusive Discount

Commission

Event Earnings Calculator

Cost of Event
$17,000.00

Cost of Event
Commission Percentage

Commission Percentage
ERROR:#N/A

0
0.00%

Commission Earned
ERROR:#N/A

5000
1.00%

10000
3.00%

Employee Level
Manager

15000
5.00%

Base Event Pay
$ 750

20000
8.00%

Total Earnings
ERROR:#N/A

Level
Base Event Pay

Entry
$ 500

Consultant
$ 750

Senior Consultant
$ 1,000

Manager
$ 1,250

Senior Manager
$ 1,500

YO16XLCH03GRADERPS2AS_-_Car_Rental_12_Instructions x
Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH03_GRADER_PS2_AS – Car Rental 1.2

Project Description:
Jason Easton is a member of the support/decision team for the San Diego branch of Express Car Rental. He created a worksheet to keep track of weekly rentals in an attempt to identify trends in choices of rental vehicles, length of rental, and payment method. This spreadsheet is designed only for Jason and his supervisor to try and find weekly trends and possibly use this information when marketing and forecasting the type of cars needed on site. The data for the dates of rental, daily rates, payment method, and gas option have already been entered.

Adjust column width of columns B and C if necessary.
5.000
12 Insert the File Name in the left footer section of all worksheets in the workbook.
4.000
13 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0.000
Total Points 100.000

Updated: 05/01/2017 1 Current_Instruction x

Nyarko_e02ch03_grader_as_CarRental.xlsx

RentalData

Express Car Rental

Rentals Initiated in Week Starting 4/5/2017

Created by Jason Easton

Auto Type
Auto Id
Date Rented
Date Returned/or Expected Return
#Days Rented
Payment Method
Daily Rate
Amount of Discount
Gas Option
Cost of Gas
Total Cost

Green Collection
988
4/5/17
4/8/17

Credit Card

Compact/Midsize
275
4/5/17
4/10/17

COD

Compact/Midsize
277
4/9/17
4/10/17

Express Miles

Green Collection
990
4/5/17
4/6/17

Credit Card

Fullsize/Standard
350
4/5/17
4/8/17

Credit Card

SUV/Minivan
550
4/5/17
4/9/17

Rewards

SUV/Minivan
551
4/7/17
4/10/17

Credit Card

Fullsize/Standard
352
4/7/17
4/9/17

Credit Card

Green Collection
989
4/6/17
4/9/17

Credit Card

Compact/Midsize
275
4/11/17
4/18/17

COD

Fullsize/Standard
355
4/9/17
4/16/17

Credit Card

Fullsize/Standard
356
4/6/17
4/8/17

Credit Card

Fullsize/Standard
350
4/9/17
4/13/17

Rewards

SUV/Minivan
553
4/8/17
4/14/17

Express Miles

SUV/Minivan
550
4/11/17
4/17/17

COD

Green Collection
988
4/9/17
4/15/17

Credit Card

Green Collection
989
4/10/17
4/16/17

Express Miles

Green Collection
990
4/7/17
4/10/17

Credit Card

Fullsize/Standard
356
4/9/17
4/10/17

Rewards

SUV/Minivan
551
4/11/17
4/15/17

Express Miles

SUV/Minivan
554
4/7/17
4/9/17

Express Miles

Fullsize/Standard
352
4/10/17
4/11/17

Credit Card

Fullsize/Standard
356
4/11/17
4/17/17

Express Miles

Compact/Midsize
276
4/11/17
4/14/17

Credit Card

Compact/Midsize
277
4/11/17
4/15/17

Rewards

SUV/Minivan
554
4/10/17
4/12/17

COD

Green Collection
990
4/11/17
4/18/17

Express Miles

Rental Rates

Green Collection
$49.99

Compact/Midsize
$76.49

Fullsize/Standard
$84.99

SUV/Minivan
$104.99

Discount for Express Miles or Rewards
20%

Cost to Fill Tank
$65.00

Median Rental Length

Number of Rentals

ClientData

Client Data
FirstName
LastName

Dawn Schalow

Laurel Kallio

Steven Thao

Ian Falu

Suzette Karren

Jeron Jacobson

Andrea Ramirez

Keith Wreath

George Linser

Connor Ching

Kelsea VanBuren

Eli Zimmerman

Jamie Mickelson

Diane Kopiski

Ryan Kelley

Joe Kruger

Maria Maldonado

Aaron Atkinson

Suzette Kerr

Michael Stanowicz

Ramona Unger

Ty Ny

Elijah Reynolds

Sally Johnson

Brad Halstad

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Information Tools For Business/EXCEL ”

Get high-quality paper

NEW! AI matching with writer

Still stressed with your coursework?

Get quality coursework help from an expert!

Order now