{"id":108187,"date":"2022-12-05T20:16:13","date_gmt":"2022-12-05T20:16:13","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/yo19_excel_capstone_intro_smart_rental-computer-science-homework-help\/"},"modified":"2022-12-05T20:16:13","modified_gmt":"2022-12-05T20:16:13","slug":"yo19_excel_capstone_intro_smart_rental-computer-science-homework-help","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/yo19_excel_capstone_intro_smart_rental-computer-science-homework-help\/","title":{"rendered":"Yo19_excel_capstone_intro_smart_rental | Computer Science homework help"},"content":{"rendered":"
\n

\u00a0YO19_Excel_Capstone_Intro_Smart_Rental<\/strong><\/p>\n

\u00a0\u00a0<\/p>\n

Project Description:<\/strong><\/h2>\n

Virginia Garrero owns a small business in which she rents high end handbags, accessories, dresses and evening gowns. Virginia has created a workbook to keep track of weekly rentals. She uses this workbook to keep track of rentals and also to identify trends in length of rental, and payment method. Virginia wants to use this information when forecasting which new products to purchase for her rental business. To purchase new products, she will need to take out a loan. Therefore, she also wants to know what her monthly payment would be if new products were purchased. You have been asked to help finish the workbook so that Virginia can use it on a weekly basis.<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n

Start \u00a0\u00a0Excel. Download and open the file named \u00a0\u00a0Excel_Capstone_Intro_SmartRental.xlsx<\/em>. Grader has automatically added your last name to \u00a0\u00a0the beginning of the file name. Save the file to a location where you are \u00a0\u00a0storing your files.<\/p>\n

\u00a0<\/p>\n

To improve the appearance of the \u00a0\u00a0RentalData worksheet, wrap the text of cell range A5:I5.<\/p>\n

\u00a0<\/p>\n

Merge and Center cell range \u00a0\u00a0K1:P1.
\u00a0\u00a0Copy the format of cell K5 and paste the format to cell range N5:P5.<\/p>\n

\u00a0<\/p>\n

Delete Sheet4 and Sheet5 from \u00a0\u00a0the workbook.<\/p>\n

\u00a0<\/p>\n

On the RentalData worksheet, in \u00a0\u00a0cell E6, enter a DATEDIF (or DAYS) formula to determine the length of rental \u00a0\u00a0in days based on the date rented and the date returned. Copy this formula \u00a0\u00a0through cell range E7:E31.<\/p>\n

\u00a0<\/p>\n

On the RentalData worksheet, \u00a0\u00a0assign a named range Rates <\/strong>to cell range A34:B37.<\/p>\n

\u00a0<\/p>\n

In cell G6, use an appropriate \u00a0\u00a0VLOOKUP function to retrieve the daily rate from the named range Rates for \u00a0\u00a0the product in column A. The function should look for an exact match.
\u00a0\u00a0Copy the formula through cell G31.
\u00a0\u00a0Format cell range G6:G31 with the Accounting Number Format.<\/p>\n

\u00a0<\/p>\n

Rewards customers are allowed a \u00a0\u00a0discount on their rentals. In cell H6, enter a formula to determine if a \u00a0\u00a0discount should be applied. If the payment method in column F was Rewards<\/strong>, the customer should receive \u00a0\u00a0the discount shown in cell B39. Otherwise the formula should return a zero. \u00a0\u00a0Use absolute referencing where appropriate and then copy the formula through \u00a0\u00a0cell range H7:H31.
\u00a0\u00a0Apply the Percent style with zero decimal places to cell range H6:H31.<\/p>\n

\u00a0<\/p>\n

Virginia considers customers who \u00a0\u00a0spend over $200 a week to be valued customers and wants to draw attention to \u00a0\u00a0their sales totals.
\u00a0\u00a0Apply a Conditional Format to cell range I6:I31 that will highlight the \u00a0\u00a0values greater than 200 <\/strong>with Green Fill and Dark Green Text.\u00a0<\/p>\n

\u00a0<\/p>\n

In cell I32, use a function to \u00a0\u00a0add the Total Sales column.
\u00a0\u00a0If necessary, in cell I32, remove the Conditional formatting.
\u00a0\u00a0In cell I32, apply the Total cell style, bold formatting and change the font \u00a0\u00a0to size 16.<\/p>\n

\u00a0<\/p>\n

Adjust the width of columns O \u00a0\u00a0and P to 13<\/strong>
\u00a0\u00a0Hide rows 33:39.<\/p>\n

\u00a0<\/p>\n

Virginia is considering offering \u00a0\u00a0specials to clients based on their payment method. Therefore, she wants you \u00a0\u00a0to help her analyze the week’s payment information so she can determine her \u00a0\u00a0clients’ preferred method of payment.
\u00a0<\/p>\n

\u00a0\u00a0In cell L6, using a Statistical function, count the number of times the \u00a0\u00a0criteria in cell K6 is used. Use absolute referencing where necessary and \u00a0\u00a0then copy the formula through cell L9.<\/p>\n

\u00a0<\/p>\n

In cell O6, using a Statistical \u00a0\u00a0function, determine the average total sales based on the payment method type \u00a0\u00a0in cell N6. Use absolute referencing where necessary and then copy the \u00a0\u00a0formula through cells O9.<\/p>\n

\u00a0<\/p>\n

In cell P6, using a Math & \u00a0\u00a0Trig function, determine the total sale per payment method based on the \u00a0\u00a0payment method in cell N6. Use absolute referencing and then copy the formula \u00a0\u00a0through cell P9.
\u00a0\u00a0Apply a Gradient Fill Orange Data Bar to P6:P9.\u00a0<\/p>\n

\u00a0<\/p>\n

Virginia wants to determine if \u00a0\u00a0the sales goal for the week was met. The sales goal for the week is met if \u00a0\u00a0the total sales is greater than or equal to $3,000.
\u00a0\u00a0In cell L13, enter a logical function to determine if the sales goal for the \u00a0\u00a0week was met. Return a value of Yes<\/strong> if the sales goal was met, or No<\/strong> if the sales goal was not met.\u00a0<\/p>\n

\u00a0<\/p>\n

When Virginia started gathering \u00a0\u00a0client data, she entered her clients’ names in all capital letters and in \u00a0\u00a0column A. She has asked you to change the names of each client to proper \u00a0\u00a0case.
\u00a0<\/p>\n

\u00a0\u00a0On the Clients worksheet, in cell B2, use a text function to change the text \u00a0\u00a0in cell A2 to proper case. Copy the formula through cell B26.\u00a0<\/p>\n

\u00a0<\/p>\n

Virginia is considering taking \u00a0\u00a0out a loan to purchase more products to rent to customers. In order to \u00a0\u00a0purchase the products she needs, she has determined that she needs to borrow \u00a0\u00a0$10,000. She would like you to help her determine her monthly payment based \u00a0\u00a0on the loan amount, an interest rate of 5.5% with a repayment of the loan \u00a0\u00a0after 5 years.
\u00a0<\/p>\n

\u00a0\u00a0On the LoanData worksheet, in cell B6, determine the monthly payment.
\u00a0In cell B6, edit the formula to return \u00a0\u00a0an absolute value.<\/p>\n

\u00a0<\/p>\n

Virginia is curious about her \u00a0\u00a0handbag line of rentals. She has asked you to create a combination chart that \u00a0\u00a0shows the days rented and the total sales.
\u00a0<\/p>\n

\u00a0\u00a0Using cell ranges A5:A10, E5:E10, and I5:I10 on the RentalData worksheet, \u00a0\u00a0create a combination chart with the Total Sales as the secondary axis. The \u00a0\u00a0secondary axis should be a line chart.
\u00a0\u00a0Move the chart to a new chart sheet named HandbagsChart <\/strong>
\u00a0\u00a0Move the Chart Sheet to the last position (far right).
\u00a0\u00a0Enter the chart title Handbag Rental<\/strong>
\u00a0\u00a0Apply Chart Style 5.
\u00a0\u00a0Add a primary vertical axis label Days Rented<\/strong>
\u00a0\u00a0Add a secondary vertical axis label Total Sales<\/strong>
\u00a0<\/p>\n

\u00a0<\/p>\n

Save and close Excel_Capstone_Intro_SmartRental.xlsx<\/em>. \u00a0\u00a0Exit Excel. Submit the file as directed.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u00a0YO19_Excel_Capstone_Intro_Smart_Rental \u00a0\u00a0 Project Description: Virginia Garrero owns a small business in which she rents high end handbags, accessories, dresses and evening gowns. Virginia has created a workbook to keep track of weekly rentals. She uses this workbook to keep track of rentals and also to identify trends in length of rental, and payment method. Virginia […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-108187","post","type-post","status-publish","format-standard","hentry","category-education"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/108187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=108187"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/108187\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=108187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=108187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=108187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}