Quote for Customized Gift Baskets
Customized Baskets
Basket 1
Basket 2
Basket 3
Description of Custom Basket
Attendees' Unwind and Renew
Wedding Party Pamper Yourself
Parents of the Bride and Groom Stress Free
# of Items per Basket
Basket 1
Basket 2
Basket 3
Lavender Candle
Chamomile Body Lotion
Item Price List
Retail Price
Price After Bulk Discount
Lavender Candle
Price Per Basket
Basket 1
Basket 2
Basket 3
Lavender Candle
Basket Orders
Basket 1
Basket 2
Basket 3
Baskets Requested
50
8
2
2
2
3
Bulk Discount
10%
Foot Renew Cream Soothing Bath Salts
1
1
2
2
2
2
Items Per Basket
0
2
4
Chamomile Body Lotion Foot Renew Cream Soothing Bath Salts
4.99
7.99
5.99
8.99
Chamomile Body Lotion
Foot Renew Cream Soothing Bath Salts
Per Basket Price
-
Basket Subtotals
$
$
$
*To customize for a different order only modify the blue shaded boxes.
-
Total Baskets in Order
10
Total Billing Amount
Prepared for: Keller - Ryan Wedding
Most Frequent Length of Stay
Average Amount Spent at Spa
Median Amount Spent at Spa
Number of Weddings
Spa Members Scheduling Weddings
Wedding Party Name
Arroyo
Proctor
Minor
Pearce
Shea
Galvan
Crowe
Meeks
Kendrick
Mayfield
Kendall
Archer
Holley
Boucher
Childs
Rankin
Lozano
Odell
Bland
Rouse
Haas
Swain
Oconnell
Dougherty
Andersen
Wang
Cowan
Elder
Shirley
Hartley
Felix
874.6993548
Number of
Days Spent
Whole Days Spent
1.52
2.54
4.32
4.52
2.50
4.00
2.31
2.45
3.65
4.45
3.11
3.15
4.19
4.10
1.63
4.50
2.55
4.42
2.72
1.65
4.40
1.16
2.64
3.50
3.58
1.91
1.28
2.00
2.59
3.60
4.57
Returned Merchandise
-37.34
41.12
-12.7
13.97
18.53
11.09
36.64
47.7
10.24
7.88
21.78
37.51
34.36
5.79
-2.47
-12.66
49.74
-18.66
-17.86
24.98
23.12
-7.23
1.11
-0.46
-19.03
-3.15
40.74
-16.6
31.05
25.91
30.66
Value of Returned
Merchandise
Amount Spent at Spa
$
1,215.63
$
720.60
$
455.78
$
1,309.51
$
1,345.57
$
1,046.69
$
1,125.53
$
1,068.90
$
604.95
$
299.60
$
353.74
$
923.56
$
1,285.26
$
1,157.07
$
206.77
$
781.30
$
1,070.70
$
867.45
$
267.16
$
1,330.38
$
899.77
$
1,479.14
$
953.20
$
556.17
$
644.50
$
606.30
$
955.27
$
760.12
$
870.85
$
1,146.79
$
807.42
Spa Member
1
1
1
Null
1
1
1
Null
Null
Null
1
Null
1
1
Null
Null
Null
1
1
Null
Null
1
Null
Null
1
Null
1
1
1
Null
Today's Date
Spa Service Requests
Guest Name
Olivia Stone
Mia Ramos
Isabella Hudson
Rhonda Griffith
Steven Lucas
Elizabeth Anderson
Jessica Montgomery
Ella West
Joseph Wallace
Joshua Pierce
Ryan Jennings
Judy Brown
Hannah Rowe
Jill Mcdonald
Glenn Barker
Mia Rivera
Jane Montgomery
Susan Burgess
Date Of
Birth
2/20/1978
11/11/1972
10/19/1975
1/2/1969
2/26/1970
4/14/1977
10/6/1969
5/26/1978
6/19/1986
5/12/1969
6/5/1971
11/8/1967
5/8/1974
6/9/1976
8/22/1974
3/9/1985
5/12/1986
6/14/1964
Age
First Name
Service Requested
Hair Treatment
Hair Color Treatment
Makeup Service
Women's Facial
Golf Massage
Hair Color Treatment
Women's Facial
Makeup Service
Golf Massage
Pedicure
Full Massage
Manicure
Hair Color Treatment
Full Massage
Pedicure
Pedicure
Makeup Service
Pedicure
City & State
Bernalilo, NM
Farmington, NM
Benson, AZ
Benson, AZ
Bisbee, AZ
Bernalilo, NM
Santa Fe, NM
Farmington, NM
Farmington, NM
Bernalilo, NM
Bisbee, AZ
Benson, AZ
Bisbee, AZ
Bernalilo, NM
Santa Fe, NM
Santa Fe, NM
Farmington, NM
Santa Fe, NM
City
State
Financing Calculator
Credit Score
Total Cost of Event
Down payment
Rate (Annualized)
Term (in Years)
Percentage Discount
Amount of Discount
Total amount borrowed
Monthly Payment
725
$ 37,000.00
$ 3,500.00
4.00%
2
$ 33,500.00
Does Party Wish to Include Golf Services?
No
Does Party Wish to Include Hotel Services?
Yes
All Inclusive Discount
Percent Discount
0
0.00%
20000
1.00%
30000
5.00%
40000
7.50%
50000
11.00%
Event Earnings Calculator
Cost of Event
Commission Percentage
Commission Earned
Employee Level
Base Event Pay
Total Earnings
$17,000.00
#N/A
#N/A
Manager
$
#N/A
750
Cost of Event
0
5000
10000
15000
20000
Commission
Percentage
0.00%
1.00%
3.00%
5.00%
8.00%
Level
Entry
Consultant
Senior Consultant
Manager
Senior Manager
Base Event Pay
$
500
$
750
$
1,000
$
1,250
$
1,500
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:
Points
Possible
Step
Instructions
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
6
7
In cell E14, change the price of Soothing Bath Salts to $11.99.
1.000
Create a named range of BasketSubtotals for the range C23:C25.
2.000
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
Updated: 09/08/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
10
Excel Project
Points
Possible
Instructions
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
In cell G5, use Flash Fill to separate the city from column F.
21
22
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.
In cell H5, use Flash Fill to separate the state from column F.
Updated: 09/08/2017
2
3.000
3.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
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
Updated: 09/08/2017
3
100.000
Current_Instruction.docx
Purchase answer to see full
attachment