Illustrated Excel 2019 | Modules 9-12: SAM Capstone Project 1a | Flamingo Guitars #illustratedexcel

Illustrated Excel 2019 | Modules 9-12: SAM Capstone Project 1a | Flamingo Guitars #illustratedexcel

If you directly want to get the project from us then contact us on our Whatsapp. Link is given here,

Whatsapp Contact Link:
https://api.whatsapp.com/message/4B6NMKKBKUFYN1?autoload=1&app_absent=0

Whatsapp Number:
+919116641093
+918005564456

Gmail Id:
singhal.agrawal.bharati@gmail.com

We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English.

Project Steps:
1) Isabella Mendoza is a sales manager at Flamingo Guitars, a company headquartered in White Plains, New York, that manufactures guitars and offers online lessons for a monthly subscription. She is using an Excel workbook to analyze data for lesson subscriptions and guitar sales. She asks for your help in completing the analysis and automating the workbook.
Go to the Subscription Sales worksheet, which lists Quarter 1 subscription sales for each sales rep. Isabella wants to use a button to insert totals in the range E3:E12, and has already created a macro named Totals that performs this action. Set up a button to run the macro as follows:
a. Assign the Totals macro to the Totals button in rows 13 and 14.
b. Use the Totals button to run the Totals macro.
2) Isabella also wants to identify the two sales reps with the highest number of subscription sales in Quarter 1. She plans to use the Top Sellers button to identify them. Identify the top two subscription sales by recording a new macro as follows:
a. Use TopSellers as the macro name.
b. Store the macro in This Workbook.
c. With the macro recording, select the range E3:E12.
d. Apply the Top 10 Items conditional formatting rule to the selected range.
e. Use 2 as the number of top values to highlight.
f. Highlight the cells with Light Red Fill with Dark Red Text.
g. Select cell G1, and then stop recording the macro.
3) Assign the TopSellers macro to the Top Sellers button in the range B13:B14 so that Isabella can identify the sales reps with the two highest subscription sales by clicking the button.
4) In the range G2:H6, Isabella wants to display information about bonuses for the sales reps. Selling 170 subscriptions or more makes a sales rep eligible for a $2500 bonus and sets the same goal of 170 subscriptions for the next quarter. Otherwise, subscription sales must be 5 percent more than the current total for a sales rep to earn a bonus. Add and format a check box control to make these calculations as follows:
a. In cell G9, insert a Check Box (Form Control).
b. Edit the check box text to use Set goal to replace the placeholder text.
c. Format the new check box to link it to cell $H$1.
d. Use 3-D shading to match the other check box on the worksheet.
e. Set the height of the control to 0.3" and the width to 0.8".
f. Move the Set goal check box so that it is positioned at the bottom of cell G9 and its box is left-aligned with the check box above it.
5) Isabella created a macro named Bonus to make the bonus calculations in the range H5:H6. She asks you to add the code that enters the value 0 in cell H5 if the sales rep is not eligible for a bonus. Modify the Bonus macro as follows:
a. Open the Bonus macro in the Visual Basic Editor.
b. Find the first comment in the macro ('Replace with first line of code), and then replace it with the following text to select cell H5: Range("H5").Select
c. Replace the second comment in the macro ('Replace with second line of code) with the following text to enter the value 0 in the cell: ActiveCell.Formula = 0
d. Save and close the Bonus macro in the Visual Basic Editor.
e. Assign the Bonus macro to the Check bonus amount check box.
6) Isabella also created a macro named Goal to perform the calculation associated with the Set goal check box. Assign the Goal macro to the Set goal check box.
7) Test the controls on the Subscription Sales worksheet as follows:
a. In cell H2, type Byrne and press ENTER to look up and display the number of subscriptions the sales rep sold.
b. Click the Check bonus amount check box to run the Bonus macro.
c. Click the Set goal check box to run the Goal macro.
8) Go to the Group Sales worksheet, which contains data about guitar sales to stores, schools, and other organizations. Isabella asked Arjun Gulati, an accounts manager, to review and comment on the data. She also wants to note that an overdue invoice has been paid. Show all comments in the workbook, and then add and respond to the comments as follows:
a. In cell F7, change the data as Arjun recommends, using Unpaid as the entry.
b. Delete the comment in cell F7.
c. In cell C10, reply to the comment by including the text Yes as the response, and then post your reply.
d. In cell F12, insert and post a new comment that contains the following text: This invoice has been paid.

Видео Illustrated Excel 2019 | Modules 9-12: SAM Capstone Project 1a | Flamingo Guitars #illustratedexcel канала Commerce Classes