## Illustrated Excel 2016 Modules 5-8 - SAM Capstone Project 1a

Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a

Paterson Arts Center

mANAGING FORMULAS, DATA, AND TABLES

## GETTING STARTED

· Open the file IL_EX16_CS5-8a_ FirstLastName _1.xlsx , available for download from the SAM website.

· Save the file as IL_EX16_CS5-8a_ FirstLastName _2.xlsx by changing the “1” to a “2”.

## PROJECT STEPS

b. In cell C9, change the value to 1:00 PM .

Unhide the Students worksheet to prepare for working with student data.

c. Group the Group Classes , Private Lessons , Students , and Expansion worksheets.

d. In cell A1, type Paterson Arts Center as the worksheet title.

e. With the worksheets still grouped, add the Sheet Name to the center header section.

Switch to Normal view, if necessary. Ungroup the worksheets.

To make it easier to navigate the workbook, add hyperlinks as follows:

g. Include View weekly revenue as the ScreenTip text.

Gwen is adding two new private lessons on Saturdays and needs that reflected in the schedule.

Table 1: Data for Private Lessons Worksheet Table

i. Enter the text Piano into cell A18.

l. Use Monday Classes as the name of the new worksheet.

m. In the Monday Classes worksheet, filter the Day column to display only Monday classes.

n. Sort the filtered table in ascending order by Instructor.

o. If the Repeat? value is “Yes” , calculate the amount paid by subtracting 5 from the Fee .

p. Otherwise, the amount paid is the Fee value.

q. Let Excel copy the formula in cell H4 into the range H5:H49.

s. Look up the value in cell K3 .

u. Return the corresponding class name shown in column 2 of the specified columns.

v. Use FALSE as the range_lookup value.

w. Look up the value in cell K3 .

y. Return the corresponding class name shown in column 3 of the specified columns.

z. Use FALSE as the range_lookup value.

ab. Use the Amount Paid header in cell H3 as the field argument.

ac. Use the range J5:J6 as the criteria.

ae. Use the Class Code header in cell C3 as the field argument.

af. Use the range J5:J6 as the criteria.

ah. Use Subtotals as the name of the new worksheet.

ai. On the Subtotals worksheet, clear the contents and formatting from the range J3:K8.

aj. Sort the table on the Subtotals worksheet in ascending order by class name.

al. Convert the table to a range.

Include subtotals on the Subtotals worksheet as follows:

ap. In the formula, divide the rate (cell B6 ) by 12 .

aq. Use the term in months (cell B7 ) as the nper.

ar. Use a negative value for the pv (cell B5 ).

as. Copy the formula from cell B9 into the range C9:D9.

Calculate the total payments as follows:

au. Copy the formula from cell B10 into the range C10:D10.

Calculate the total interest as follows:

aw. Copy the formula from cell B11 into the range C11:D11.

Final Figure 1: Group Classes Worksheet

Final Figure 2: Private Lessons Worksheet

Final Figure 3: Monday Classes Worksheet

Final Figure 4: Students Worksheet

Final Figure 5: Subtotals Worksheet

Final Figure 6: Expansion Worksheet

- Applied Sciences
- Architecture and Design
- Business & Finance
- Computer Science
- Engineering
- Environmental science
- Human Resource Management
- Information Systems
- Mathematics
- Political Science
- Social Science
- Liberty University
- New Hampshire University
- Strayer University
- University Of Phoenix
- Walden University
- Homework Answers

## SAM Project Spreadsheets New Perspectives Excel 2019

## Description

Open the file np_ex19_cs5-8a_ firstlastname _1.xlsx , available for download from the sam website..

- Save the file as NP_EX19_CS5-8a_ FirstLastName _2.xlsx by changing the “1” to a “2”.
- To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
- With the file NP_EX19_CS5-8a_ FirstLastName _2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
- This project requires you to use the Solver add-in. If this add-in is not available on the Data tab in the Analyze group (or if the Analyze group is not available), install Solver as follows:

oSupport_EX19_CS5-8a_2020.xlsx

oSupport_EX19_CS5-8a_Management.docx

- Group the U.S. , Canada , and Mexico worksheets.
- In cell F5, insert a formula using the SUM function that totals the Mini sales amounts for Quarters 1–4 (range B5:E5 ).
- Fill the range F6:F7 with the formula in cell F5 to display the totals for the other types of portable speakers.
- In cell B5, enter a formula using the SUM function and 3-D references that totals the Mini sales values (cell B5 ) in Quarter 1 from the U.S. , Canada , and Mexico worksheets.
- Fill the range C5:E5 with the formula in cell B5 to total the Mini sales for Quarters 2–4.
- Create a defined name for the Waterproof sales amounts (range B7:E7) using Waterproof_Total as the name.
- In cell F5, enter a formula using the SUM function to display the total of the sales amounts in the Mini_Total range.
- In cell F6, enter a formula using the SUM function to display the total of the sales amounts in the Voice_Activated_Total range.
- Open the workbook Support_EX19_CS5-8a_2020.xlsx .
- Return to the All Locations worksheet in the original workbook.
- In cell G5, enter a formula using an external reference to display the total sales of Mini products in 2020 (cell F5 ).
- In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number.
- Fill the range G6:G8 with the formula in cell G5, filling without formatting.
- In cell B12, start to enter a formula using the HLOOKUP function.
- Use the Total Q1 sales (cell B8 ) as the value to look up.
- Use the Revenue Amts and Rating information (range $B$14:$F$15 ) as the table containing the lookup data, using absolute references to specify the range.
- Specify that row 2 contains the value you want to return, which is the performance rating.
- Specify an approximate match ( TRUE ) because the Revenue Amts represent ranges of values.
- In cell I9, create a link to the Word document Support_EX19_CS5-8a_Management.docx .
- Use Management Details as the text to display.
- In cell G5, start to enter a formula using the AND function and structured references.
- The first condition tests whether the value in the Product column ( [@Product] ) equals "Mini 2" , the product eligible for the promotional offer.
- The second condition tests whether the value in the Location column ( [@Location] ) equals "U.S." , the location eligible for the promotional offer.
- In the range A5:A40, create a conditional formatting Highlight Cells Rule that formats Duplicate Values in Light Red Fill with Dark Red Text .
- On a new worksheet, insert a PivotTable based on the data in the Sales table, and use January Pivot as the name of the worksheet.
- Display the Channel Type values as column headings.
- Display the Location values as row headings.
- Apply the Currency number format with 0 decimal places and the $ symbol to the Sum of Amount values.
- Use Sales (000s) as the custom name of the Sum of Amount field.
- In cell A4, use Country to identify the row headings.
- In cell B3, use Channel to identify the column headings.
- Insert a slicer based on the Product field.
- Insert a Stacked Column PivotChart based on the data in the PivotTable.
- Change the colors of the PivotChart to Monochromatic Palette 6 to coordinate with the PivotTable.
- Add the IFERROR function to the formula in cell B17.
- For the range D4:K9, create a two-variable data table using the price per unit (cell B6 ) as the Row input cell.
- Set the objective as minimizing ( Min ) the total cost (cell E10 ).
- Use the units produced values (range B5:D5 ) as the changing variable cells.
- Adjust the number of units produced by each supplier using the following constraints: Set the total number of virtual assistants produced (cell E5 ) as greater than or equal to 11,000 , DIG Technology's minimum production goal. Set the total cost (cell E10 ) to be less than or equal to 925,000 , the maximum total cost DIG Technology wants to spend. Set the total number of virtual assistants produced by a single supplier (range B5:D5 ) to be less than or equal to 4000 to balance the production among the suppliers. Make sure the values in the range B5:D5 are integers since DIG Technology cannot sell a fraction of a product.
- In the Scenario Manager, add a scenario using Reduced Costs as the name.
- Accept the same changing cells (nonadjacent ranges B6:F6 and B11:F12) as the other two scenarios.
- Reduce each variable cost per unit value (Mini1_Variable_Cost, Mini2_Variable_Cost, VoiceXP_Variable_Cost, VoiceXP10_Variable_Cost, and Waterproof_Variable_Cost) by $5.00.
- Create a Scenario PivotTable report using the profit per unit sold (range B17:F17 ) as the result cells.
- Remove the Filter field from the PivotTable.

f.Close the workbook Support_EX19_CS5-8a_2020.xlsx.

f.Fill the range C12:E12 with the formula in cell B12 to enter ratings for Quarters 2–4.

c.Use Access manager details as the ScreenTip text.

d.If Excel does not fill the column, fill the range G6:G40 with the formula in cell G5.

b.Move and resize the slicer so that it covers the range F3:G12.

b.Move and resize the PivotChart so that it covers the range A10:E25.

b.Use the slicer to display sales data for only the Mini 1 in the PivotTable and PivotChart.

b.Use "Divide total expenses by units manufactured" as the message to display in case of an error.

b.Use the units sold (cell B5 ) as the Column input cell.

21.Run Solver again, create an Answer report, and then close the Solver Parameters dialog box.

c.Change the number format of the value fields to Currency with 2 decimal places and the $ symbol.

The Answer Report 1 worksheet and Scenario Summary worksheet have intentionally not been shown.

## Unformatted Attachment Preview

## Explanation & Answer

## 24/7 Homework Help

## Similar Content

## Killers of the Flower Moon

## Freakonomics

by Stephen J. Dubner and Steven D. Levitt

## Murder on the Orient Express

## Cry the Beloved Country

## Girl in Translation

## Crime and Punishment

## The Subtle Art of Not Giving a F*ck

working on a homework question?

Studypool is powered by Microtutoring TM

Copyright © 2023. Studypool Inc.

Studypool is not sponsored or endorsed by any college or university.

## Ongoing Conversations

Access over 20 million homework documents through the notebank

Get on-demand Q&A homework help from verified tutors

Read 1000s of rich book guides covering popular titles

Already have an account? Login

Don't have an account? Sign Up

## IMAGES

## VIDEO

## COMMENTS

SAM Capstone Project 5 8 Solution. 18K views 7 years ago. Lew. Lew. 472 subscribers. Subscribe. 40. I like this. I dislike this.

Question: New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project la san NewSight Consulting ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS GETTING

New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1aa.For the range D4:K9, create a two-variable data table using the price per unit (cellB6)

View SAM - CAPSTONE Project Module 5 - 8 Study Guide.pdf from CIS 1050 at Georgia State University. Project Study Guide CAPSTONE PROJECT: MODULES 5 ‐

nothing new perspectives excel 2019 modules sam capstone project 1a newsight consulting analyze data with tables and tools getting started open the file.

new perspectives excel 2016 modules sam capstone project 1a wild planet conservation data validation, advanced functions, tables, and pivottables getting.

Illustrated Excel 2016 | Modules 5–8: SAM Capstone Project 1a. C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\

New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a 4. 5. Benicio wants to compare the sales of each product in 2021 with the sales in 2020.

1 (480) 866-1691 ; New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a. $ 25 ; New Perspectives Excel 2019 | Module 7: SAM

5, P_2001500_1, MS Excel 2016, Advanced, Mirror, Shelly Cashman Excel 2016 Module 9: SAM ... Shelly Cashman Excel 2016 Modules 8-11: SAM Capstone Project 1a