Painted Paradise Golf Resort & Spa Banquet Room Renovations Created by Lesa Martin The Muisca Room The Eldorado Room The Pueblo Room Technology room 1 Technology room 2 Renovations Length of Renovations Started Renovations Completed in Days 1/6/2018 5/10/2018 3/15/2018 7/15/2018 9/10/2018 11/30/2018 8/1/2018 10/1/2018 2/15/2018 4/15/2018 Increase in Capacity Increase in Revenue Median Renovation Length in Days Loan Information Amount of Loan Annualized Rate Term (in Years) Monthly Payment Room Classification 0 Bronze 250,000 Silver 500,000 Gold 750,000 Platinum 30% 40% 125,000 6% 5 Original Capacity 500 100 25 45 25 New Capacity Quarterly Revenue Prior to Renovations 290,000 575,000 225,000 150,000 75,000 Projected Quarterly Revenue after Renovations Renovation Projection Schedule Projected Room Classification 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 Massage Item A9 A10 A11 A12 Item A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 A15 A16 A17 A18 A19 A20 Description Healthy Trail Mix Resort Polo Healthy Energy Bar Spirits Lifted Through Yoga Golf Tees Golf Item A16 A17 A18 A19 Projection 4 5 6 6 Max_On_Hand 45 35 25 15 50 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx WholeSell Price $ 15.99 $ 29.48 $ 3.90 $ 6.90 $ 1.30 Sale Price $ 21.99 $ 45.34 $ 9.95 $ 14.95 $ 2.59 Projection 5 5 5 5 Category Food Clothing Food Reading Golf Food Item A1 A3 A20 Projection 4 4 4 Delivery Time Current Inventory ReOrder_Point RushReOrderPoint 10 23 20 10 14 15 17 10 4 5 15 10 7 5 15 10 11 27 20 10 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx Novelty Item A13 A14 A15 Discontinue N N N Y N Reading Item Projection A5 A6 A7 A8 Projection 5 6 7 ReorderStatus Clothing Item A2 A4 Vendor Vendor 1 Vendor 2 Vendor 1 Vendor 4 Vendor 5 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx Projection Massage Category Category Item 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Projection Actual Categories Massage Golf Food Novelty Reading Clothing Mon Tues Wed Thur Fri Item Beginning inventory Total out Total in Ending Actual A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 35 33 48 10 52 10 22 15 20 30 22 10 23 25 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx A15 A16 A17 A18 A19 A20 12 14 16 33 27 15 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx Sat Sun Max Stock Inventory Discrepancy On Target On Target On Target On Target On Target On Target On Target On Target On Target On Target On Target On Target On Target On Target 25 20 15 10 30 30 30 30 30 30 30 30 30 30 Turnover 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Description Healthy Trail Mix Resort Polo Healthy Energy Bar Spirits Lifted Through Yoga Golf Tees 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx 30 30 30 30 30 30 On Target On Target On Target On Target On Target On Target 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx Transaction Trans_Item Trans_Qty Trans_Category WeekDay Trans_Sold Trans_Category Food Food Golf Food Massage Food Food Clothing Clothing Golf Food Massage Food Clothing Clothing Food Food Clothing Golf Novelty Reading Golf Golf Clothing Novelty Golf Novelty Novelty Reading Clothing WeekDay Mon Mon Wed Thur Fri Fri Fri Fri Sat Sun Thur Mon Wed Thur Fri Fri Sat Sat Mon Mon Mon Tues Wed Wed Thur Thur Thur Fri Fri Sat Trans_Sold Trans_Sold Sum Average Transaction 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Trans_Item A1 A3 A5 A3 A6 A3 A1 A2 A7 A19 A1 A10 A1 A8 A2 A1 A3 A15 A13 A14 A4 A9 A5 A15 A17 A9 A14 A17 A18 A7 Trans_Qty -14 5 -7 -14 -15 -16 -7 -10 -4 -2 15 -18 -6 -6 -13 -2 -17 -8 5 -11 -4 0 -18 -6 -4 -18 -13 -15 -14 -9 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx Trans_Delivered Trans_Delivered 20180212022217copy_of_johnson_iii_e03_grader_h1_inventory.xlsx Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_BU03_GRADER_PS1_HW - Inventory 1.6 Project Description: The gift shop staff wants to track inventory so it can reduce out-of-stock inventory items and reorder inventory more efficiently. The staff needs a template designed that will hold all the product information and then analyze daily transactions at the end of every month. You have been asked to help the gift shop employees develop a workbook that will help them manage the store’s inventory. Because the staff currently does not collect this data, the gift shop manager has given you a template with a small amount of fictitious data so you can create the needed formulas and worksheets. The worksheet also contains named ranges that you will use to create formulas in the template. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step 1 Points Possible Instructions Open the downloaded file e03_grader_h1_Inventory.xlsx. Save the file as e03_grader_h1_Inventory_LastFirst using your last and first name. 0.000 On the Inventory worksheet, in cell L19, enter nested IF and AND functions to determine the reorder status. For the logical test, determine if the current inventory in cell H19 is blank and the reorder point in I19 is blank. If the value is true, return a blank. 2 3 But if the value is false, nest a second nested IF and AND function in which the logical test determines if the Discontinue in K19 is equal to "N" and the Current Inventory is less than or equal to the rush reorder point in J19. If the value is true, "Rush" should be returned. If the value is false, then nest a third IF and AND function in which the logical test determines if Discontinue (K19) is equal to "N" and Current Inventory (H19) is less than the Reorder Point (I19). If the value is true, return "Reorder". If the value is false, return "Ok". Copy the formula down through cell L38. On the DailyTransactions worksheet, in cell F11, the data in the Trans_Sold column is the number of items sold. (Negative Trans_Qty numbers are the quantity that was sold or outgoing. Positive Trans_Qty numbers are the total number received from the supplier or incoming). 6.000 6.000 Enter an IF function to determine if the Trans_Qty in C11 is less than zero. If the Trans_Qty is less than zero, then return the absolute value of the Trans_Qty, or else the cell is left blank. Copy the formula through cell F40. 4 On the DailyTransactions worksheet in cell G11, enter an IF function to determine if there were transactions delivered. If the value in the Trans_Qty is greater than zero, then the result is the value, or else the cell is left blank. Copy the formula through cell G40. 6.000 5 On the DailyTransactions worksheet, in cell D2, type Food and in cell E2, type Fri 2.000 On the DailyTransactions worksheet, select the range A10:G40. Name the range InventoryTable 6 In cell C6, enter a DSUM function to sum the date in the InventoryTable of the field name in C5 and the criteria range established in D1:E2. Updated: 10/02/2017 1 6.000 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step 7 Excel Project Points Possible Instructions In cell C7, enter a DAVERAGE function to determine the average of the field in cell C5 from the table range data in the InventoryTable and the criteria range established in D1:E2. 6.000 Format cell C7 with no decimal places. On the InventoryAudit worksheet, the cells in range A3:D17 need to be filled in based on the category that is listed in cell B1, "Massage". The table will then contain the information about each product within the listed category. Using named ranges already created in the Inventory worksheet, complete the following: 8 9 In cell B3, create a formula to retrieve the item within each category in cell B1. Enter an INDIRECT function to return the item numbers by category. The function will reference cell $B$1 (absolute) and index the value of the intersection of row 3 (A3) and column 1. Nest the function in an IFERROR function to return a blank cell if no items exist within the category. On the InventoryAudit worksheet in cell C3, create a formula to retrieve the projected sales for each item within the category in cell B1. Enter an INDIRECT function to return the item numbers by category. The function will reference cell $B$1 (absolute) and index the value of the intersection of row 3 (A3) and column 2. Nest the function in an IFERROR function to return a blank cell if no items exist within the category. 6.000 6.000 On the InventoryAudit worksheet in cell D3, create a formula to determine the total quantity sold within the category in cell B1. Enter a SUMIF function nested in an IF function to determine if B3 has a value. If B3 has a value, then you will sum the Trans_Sold if the Trans_Item meets the criteria in B3. If B3 does not have a value, then the cell is left blank. 10 11 12 13 14 Select range B3:D3, and then double-click the Fill handle to copy the three formulas down through cell range B4:D17. Rows 7 through 17 will be blank because the Indirect function is referring to the named range for the value in cell B1, "Massage". The Massage named range only contains the four items. To test your formula, type Golf in cell B1, and then view the results. Type Massage in cell B1. In cell C21, using a SUMIFS function, calculate the total quantity sold (Trans_Sold) per category (Trans_Category) per day (Weekday) in range B20:I26 based on two sets of criteria, C20 and B21. Use an appropriate mixed reference on the criteria cell references to be able to copy the formula through cell I26. Copy the formula through cell range D21:I26. Calculate the total quantity sold per category in range D32:D51 based on the item in column B. Once this has been calculated, the total quantity delivered and ending inventory can be calculated. In cell D32, create a formula to sum the range Trans_Sold from the DailyTransactions worksheet if the criteria in B32 is found in the range Trans_Item from the DailyTransactions worksheet. On the InventoryAudit worksheet, in cell E32, create a formula to sum the range Trans_Delivered if the criteria in B32 is found in the range Trans_Item. In cell F32, create a formula that subtracts the Total out (D32) from the Beginning inventory (C32) and then adds the Total in (E32). In cell G32, create a VLOOKUP formula for the value in cell B32 from the range A19:M38 on the Inventory worksheet in the seventh column with an exact match. 6.000 6.000 6.000 12.000 6.000 Select range D32:G32, and then copy the formulas through cell range D33:G51. Updated: 10/02/2017 2 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 15 There is an error in a table array in the formulas in K33:K51. In cell K32, review the formula, and make any necessary corrections to fix the formula. Copy the corrected formula in cell K32 through cell range K33:K51. 4.000 16 17 18 Make a copy of the DailyTransactions worksheet, and place it to the right of the DailyTransactions worksheet. Name the new sheet DailyTransactionsFilter. In cell D2, enter Clothing. Format A10:G40 as a Table with headers. Create an advanced filter. Verify the criteria range is A1:G2. Click cell A11 of the DailyTransactions worksheet. Create a PivotTable, and place it on a new worksheet named PivotTableAnalysis. Add the Trans_Category, and Trans_Item fields as row fields (in that order). Add Transaction as the value field. Sort the transaction categories in Descending order. Count the number of transaction items. Change the text in A3 to Items Sold, and the text in cell B3 to # of Transactions. Apply Pivot Style Dark 2 to the PivotTable. Note, depending on the version of Office you are using, the style may be Dark Blue, Pivot Style Dark 2. Filter the PivotTable for Golf, Food, and Clothing. Save the workbook, exit Excel, and then submit your file as directed by your instructor. 3 10.000 0.000 Total Points Updated: 10/02/2017 6.000 100.000 Current_Instruction.docx 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