Illustrated Excel 2019 | Modules 9-12: SAM Capstone Project 1a Flamingo Guitars ANALYZE DATA AND AUTOMATE WORKBOOKS GETTING STARTED Open the file IL_EX19_CS9-12a_FirstLastName_1

Subject:MS ExcelPrice:20.99 Bought3

Share With

Illustrated Excel 2019 | Modules 9-12: SAM Capstone Project 1a

Flamingo Guitars

ANALYZE DATA AND AUTOMATE WORKBOOKS

GETTING STARTED

Open the file IL_EX19_CS9-12a_FirstLastName_1.xlsm, available for download from the SAM website.

• Save the file as IL_EX19_CS9-12a_FirstLastName_2.xlsm by changing the “1” to a “2”.
  • If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_CS9-12a_Advanced.xlsx
  • Support_EX19_CS9-12a_April.xml
  • Support_EX19_CS9-12a_April.xsd
  • Support_EX19_CS9-12a_Group.html
  • Support_EX19_CS9-12a_May.xml
  • Support_EX19_CS9-12a_Stores.accdb
• With the file IL_EX19_CS9-12a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• Click enable content at the top, if required.
• When downloading Access support files, your browser may display a warning message similar to "This type of file can harm your computer." SAM Project files are always considered safe, so you can disregard this message.

• 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:
   1. Assign the Totals macro to the Totals button in rows 13 and 14.
   2. 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:
   1. Use TopSellers as the macro name.
   2. Store the macro in This Workbook.
   3. With the macro recording, select the range E3:E12.
   4. Apply the Top 10 Items conditional formatting rule to the selected range.
   5. Use 2 as the number of top values to highlight.
   6. Highlight the cells with Light Red Fill with Dark Red Text.
   7. 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:
   1. In cell G9, insert a Check Box (Form Control).
   2. Edit the check box text to use Set goal to replace the placeholder text.
   3. Format the new check box to link it to cell $H$1.
   4. Use 3-D shading to match the other check box on the worksheet.
   5. Set the height of the control to 0.3" and the width to 0.8".
   6. 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:
   1. Open the Bonus macro in the Visual Basic Editor.
   2. 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
   3. 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
   4. Save and close the Bonus macro in the Visual Basic Editor.
   5. 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:
   1. In cell H2, type Byrne and press ENTER to look up and display the number of subscriptions the sales rep sold.
   2. Click the Check bonus amount check box to run the Bonus macro.
   3. 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:
   1. In cell F7, change the data as Arjun recommends, using Unpaid as the entry.
   2. Delete the comment in cell F7.
   3. In cell C10, reply to the comment by including the text Yes as the response, and then post your reply.
   4. In cell F12, insert and post a new comment that contains the following text: This invoice has been paid.
9. Isabella needs to import group sales data collected on the company website. Import the data from a webpage as follows:
   1. Open the file Support_EX19_CS9-12a_Group.html in your browser.
   2. Press CTRL+A to select all four rows of data on the webpage, and then copy the selection.
   3. In the Group Sales worksheet, select the range A17:F20, paste the copied data to match the destination formatting, and then close the webpage.
10. Isabella is also tracking prospective group sales customers in separate XML files. She asks you to import them as follows:
    1. Open the XML Source pane and add the XML schema Support_EX19_CS9-12a_April.xsd to the workbook.
    2. Map the XML elements as follows:
       · ORGANIZATION Cell H2
       · TYPE Cell I2
       · SALES_REP Cell J2
       · UNITS Cell K2
11. Import two XML files as follows:
    1. Set the XML map properties to validate imported and exported data against the schema and append new data to existing XML tables. (Hint: Use the XML Map Properties option available on the table's shortcut menu.)
    2. Import the XML data from the file Support_EX19_CS9-12a_April.xml into the worksheet at cell H3.
    3. Import the XML data from the file Support_EX19_CS9-12a_May.xml into the worksheet at cell H7.
    4. Close the XML Source pane.
12. Go to the Online worksheet, which includes details about online lesson subscriptions. Correct the errors in the worksheet as follows:
    1. In cell G4, trace the errors in the formula to identify the cause of the error message. Correct the formula so that the IF function tests whether the start date (the range named Online_Start) is less than the date in cell B2. If it is, multiply the monthly price (cell F4) by the discount rate (cell G2) and return the result. If it is not, return the monthly price (cell F4) in cell G4.
    2. Copy the formula in cell G4 into the range G5:G17 to fix the errors in that range.
13. After entering the product code data in the range C4:C17, Isabella applied data validation to the range to make sure she and others entered the correct codes. Check for data-entry errors and correct them as follows:
    1. Circle invalid data in the worksheet.
    2. Change the circled value to 305 to use the correct product code.
14. In the range I11:K15, Isabella has created an area for analyzing the subscriptions for Advanced lessons, which have the highest price. Create formulas to analyze this data as follows:
    1. In cell K12, enter a formula using the COUNTIF function to count the number of Advanced subscriptions sold. Use the list of product codes (range C4:C17) as the range and the code for Advanced lessons (in cell I6) as the criteria in your formula.
    2. In cell K13, use the SUMIF function to find the total of Advanced subscriptions sold. Use the list of product codes as the range, the code for Advanced lessons as the criteria, and the monthly prices (range F4:F17) as the range to sum in your formula.
    3. In cell K14, enter a formula using the AVERAGEIF function to find the average of discounted Advanced subscriptions sold. Use the list of product codes as the range, the code for Advanced lessons as the criteria, and the discounted prices (G4:G17) as the range to average in your formula.
15. In cell K15, Isabella wants to display the profit margin for Advanced subscriptions, which is contained in another worksheet. Create a formula using an external reference to include the profit margin as follows:
    1. Open the workbook Support_EX19_CS9-12a_Advanced.xlsx.
    2. In cell K15 of the Online worksheet, insert a formula that references cell B6 in the Support_EX19_CS9-12a_Advanced.xlsx workbook.
    3. Close the Support_EX19_CS9-12a_Advanced.xlsx workbook.
16. Go to the Stores worksheet. In the range A3:D6, Isabella wants to list data about stores that recently became Flamingo Guitars customers. She imported the store data in the range F2:J6, but it does not appear in the correct format. Incorporate the imported store data in the range A3:D6 as follows:
    1. In cell A3, enter a formula using the PROPER function to capitalize the first letter in each word in the Store text in cell F3.
    2. Fill the range A4:A6 with the formula in cell A3 to show all the store names.
17. The imported contact name data separates the first and last names, but Isabella wants to list the full name in one cell. List the first and last names of each contact in a single cell as follows:
    1. In cell B3, enter a formula using the CONCAT function that displays the first name shown in cell G3 followed by a space (" "), and then the last name shown in cell H3.
    2. Fill the range B4:B6 with the formula in cell B3 to list the full names of the remaining contacts.
18. List the number of years in business and the state where the store is located using worksheet references as follows:
    1. In cell C3, enter a formula using the LEFT function to insert the first 2 characters on the left of cell I3.
    2. Fill the range C4:C6 with the formula in cell C3.
    3. In cell D3, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell J3.
    4. Fill the range D4:D6 with the formula in cell D3.
    5. Hide columns F:J so the worksheet does not display duplicate data.
19. Isabella has an Access database containing data about guitar sales in the new stores. She asks you to import the two tables in the database so she can analyze the data in a new PivotTable. Import the database tables and insert a PivotTable as follows:
    1. Get data from the Access database named Support_EX19_CS9-12a_Stores.accdb.
    2. Select the Orders and Products tables to import.
    3. Choose to only create a connection to the data and add the data to the Data Model.
    4. Use Power Pivot to create a PivotTable on the Stores worksheet, beginning in cell A8.
20. Isabella wants to examine the imported order and product data in the new PivotTable so that she can compare the units sold by guitar model and store. Modify the PivotTable as follows:
    1. Add the ProductName field from the Products table to the Columns area.
    2. Add the Store field from the Orders table to the Rows area.
    3. Add the Units field from the Orders table to the Values area.
21. Create a relationship between the imported tables so that Isabella can combine and properly compare their data, as follows:
    1. Use Power Pivot to manage the Data Model.
    2. In the Power Pivot for Excel window, create a relationship between the Orders and Products tables, choosing ProductID as the column that relates the tables.
22. Go to the Projections worksheet. In the range A2:F5, Isabella wants to make three income projections based on different assumptions. Project the quarterly income for Isabella as follows:
    1. First, determine the income needed in Quarters 2 and 3 to achieve an income of $650,000 in Quarter 4. Project the income in the four quarters by filling the series for the first projection (range B3:E3) with a linear trend.
    2. Next, determine the income in Q2–Q4 if it increases by 2.5 percent each quarter, starting with an income of $551,000. Project the income in the four quarters by filling the series for the second projection (range B4:E4) based on a growth trend using 1.025 as the step value.
    3. Finally, extrapolate the income based on the income in Q4 of the current year and a growth rate of 4 percent. Project the income in the four quarters by filling the series for the third projection (range B5:E5) based on a growth trend using 1.04 as the step value.
23. Flamingo Guitars is considering taking out a loan to fund additional manufacturing equipment. Isabella asks you to help analyze three loan scenarios to fund the equipment. Calculate the monthly payment for Scenario 1 as follows:
    1. In cell B13, enter a formula using the PMT function to determine the monthly payment.
    2. In the formula, use the monthly interest rate (cell B11) as the rate, the term in months (cell B12) as the nper, and the loan amount (cell B9) as the pv.
    3. Add a minus sign (-) before the PMT function so the result appears as a positive value.
24. In Scenario 2, Isabella wants to make a monthly payment of $3,000 at an annual interest rate of 4.25 percent. She wants to know how much she can borrow on those terms. Calculate the loan amount for Isabella as follows:
    1. In cell C9, enter a formula using the PV function to determine the loan amount.
    2. In the formula, use the monthly interest rate (cell C11) as the rate, the term in months (cell C12) as the nper, and the monthly payment (cell C13) as the pmt.
    3. Add a minus sign (-) before the PV function so the result appears as a positive value.
25. In Scenario 3, Isabella wants to know the total amount spent for a loan of $165,000 at an annual interest rate of 4.13 percent and a monthly payment of $3,500. Calculate the future value of the loan for Isabella as follows:
    1. In cell D14, enter a formula using the FV function to determine the future value.
    2. In the formula, use the monthly interest rate (cell D11) as the rate, the term in months (cell D12) as the nper, and the monthly payment (cell D13) as the pmt.
    3. Add a minus sign (-) before the FV function so the result appears as a positive value.
26. Go to the Expense Report worksheet. Isabella has created two scenarios on the worksheet, one that includes current values, and another that reflects an increase in prices for three models of guitars. She also wants to know how profits would change if the company decreased prices, assuming that would increase the number of units sold. Create a third scenario to provide this information for Isabella as follows:
    1. In the Scenario Manager, add a new scenario and use Decrease Prices as the scenario name.
    2. Change the values in the nonadjacent range F4:H4, F8:H8.
    3. Use the information shown in Table 1 as the values for the changing cells.

Table 1: Decrease Prices Scenario Values

 

Changing Cell	Value
Vega_Price (F4)	359
Aquila_Price (G4)	409
Corvus_Price (H4)	429
Vega_Units_Sold (F8)	1100
Aquila_Units_Sold (G8)	1000
Corvus_Units_Sold (H8)	1000

 

27. Compare the results of the three scenarios as follows:
    1. Create a Scenario Summary Report to show the result for gross profit (cell B15) and for gross profit per unit (the range F9:H9).
    2. On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column E.
28. Go to the Gross Profit worksheet, where Isabella wants to determine how increasing or decreasing the number of Vega guitars will affect gross profit. Create a one-variable data table as follows:
    1. In cell G3, enter a formula that references the gross profit for Vega guitars (cell B9).
    2. Create a one-variable data table based on the Vega guitar sales data in the range F3:G9.
    3. Use the number of Vega guitars sold (cell B8) as the column input cell.
29. Isabella projects that sales of Vega guitars will increase by 2 percent in the coming year and associated expenses will rise by 2.5 percent. She wants to see the result in profit if sales and expenses change at different rates. Determine the changes in profit as follows:
    1. In cell F13, enter a formula that references the projected profit for Vega guitars (cell B17).
    2. Create a two-variable data table in the range F13:K19.
    3. Use the expense increase percentage (cell B16) as the row input cell.
    4. Use the projected growth percentage (cell B14) as the column input cell.
30. Go to the Premiums worksheet, which lists data about premiums Flamingo Guitars occasionally offers to include an accessory along with the sale of three types of deluxe guitars. The company wants to keep the total costs low when running the premium offers. Isabella wants to know how many premiums to offer of each type to minimize costs while running up to five premiums a year.
    Use Solver to find this information as follows:
    1. Use the total cost of the premiums (cell E9, named Total_cost) as the objective cell in the Solver model, with the goal of minimizing the value.
    2. Use the number of premiums offered (range B7:D7) as the changing variable cells.
    3. Determine and enter the constraints based on the information provided in Table 2.
    4. Use Simplex LP as the solving method to find a global optimal solution.
    5. Solve the model, keeping the Solver solution.