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”.
2. If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
· With the file IL_EX16_CS5-8a_ FirstLastName _2.xlsx 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.
PROJECT STEPS
You are an intern at the Paterson Arts Center in Paterson, New Jersey, and work closely with Gwen Rayburn, the executive director of the center. Gwen is seeking a bank loan to expand the center. In preparation for meeting with a bank officer, she asked you to analyze data about the center’s group classes, lessons, and students, and to compare the terms and payments of three loan options.
Switch to the Group Classes worksheet, which Gwen has protected. Start by correcting data as follows:
a. Unprotect the worksheet.
b. In cell C9, change the value to 1:00 PM .
To reduce the chance of input error, Gwen wants you to make the Location column a dropdown list. Use data validation to create an in-cell dropdown list that restricts data in the Location column in the table (the range E4:E13) to the values Basie Hall, Cassatt Studio, Chopin Room .
Unhide the Students worksheet to prepare for working with student data.
Gwen added her name to the footer in each worksheet and now wants to remove it. Use the Document Inspector to remove only footer information from the workbook. ( Hint : Do not save data when the dialog box appears.) [Mac Hint: The Document Inspector is not available on Excel for Mac, so manually remove all footers from the workbook.]
To organize the worksheets more logically, move the Private Lessons worksheet so it appears between the Group Classes and Students worksheet.
Display the full name of the arts center as a header on all the worksheets except the Documentation worksheet as follows:
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:
f. In the Group Classes worksheet, add a hyperlink to the text in cell G16 that links to cell A1 of the Students worksheet.
g. Include View weekly revenue as the ScreenTip text.
h. Copy the hyperlink from cell G16 in the Group Classes worksheet to cell G16 of the Private Lessons worksheet.
In the Private Lessons worksheet, use data validation to create an in-cell dropdown list that restricts data in the Location column in the table (the range E4:E13) to the values Basie Hall, Chopin Room .
Gwen is adding two new private lessons on Saturdays and needs that reflected in the schedule.
In the ranges A14:G14 and A15:G15, add two records for the new private lessons using the data shown in Table 1 below. Use the in-cell dropdown list to enter the location information.
Table 1: Data for Private Lessons Worksheet Table
Gwen asks you to create a separate list of piano lessons on the Private Lessons worksheet. Create the list as follows:
i. Enter the text Piano into cell A18.
j. Using the range A17:A18 as the Criteria range, create an advanced filter that extracts records from the Lessons table in the List range A3:G15 to the range beginning in cell A20. Use the Copy to another location option.
Gwen asks you to create a worksheet showing the Monday group classes organized by the instructor name. Create this worksheet as follows:
k. Create a copy of the Group Classes worksheet and position it immediately before the Students worksheet.
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.
Switch to the Students worksheet. Gwen offers a $5.00 discount to repeat students. Apply this discount as appropriate to the student data as follows:
In cell H4, create a formula using the IF function and structured references to determine the correct amount paid based on the following criteria:
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.
r. Format the range H4:H49 using the Currency number format with zero decimal places and the $ symbol.
Gwen created an area for retrieving information from the Students table, but it needs to be filled in. In cell K4, enter a formula using the VLOOKUP function and structured references to retrieve the class name as follows:
s. Look up the value in cell K3 .
t. Use the Class Code and Class Name columns in the Students table ( Students[[Class Code]:[Class Name]] ) as the table_array argument.
u. Return the corresponding class name shown in column 2 of the specified columns.
v. Use FALSE as the range_lookup value.
In cell J6, enter a formula using the VLOOKUP function and structured references to retrieve the instructor’s name as follows:
w. Look up the value in cell K3 .
x. Use the Class Code through Instructor columns in the Students table ( Students[[Class Code]:[Instructor]] ) as the table_array argument.
y. Return the corresponding class name shown in column 3 of the specified columns.
z. Use FALSE as the range_lookup value.
In cell K7, enter a formula using the DSUM function and structured references to calculate the total amount paid for a specified instructor as follows:
aa. Use the entire Students table including the header row ( Students[#All] ) as the database argument.
ab. Use the Amount Paid header in cell H3 as the field argument.
ac. Use the range J5:J6 as the criteria.
In cell K8, enter a formula using the DCOUNTA function and structured references to count the number of classes taught by a specified instructor as follows:
ad. Use the entire Students table including the header row ( Students[#All] ) as the database argument.
ae. Use the Class Code header in cell C3 as the field argument.
af. Use the range J5:J6 as the criteria.
In her meeting with the bank officer, Gwen wants to show how much revenue the center receives for each class in a typical week. Create a copy of the Students worksheet and then modify it to prepare for showing this information as follows:
ag. Create a copy of the Students worksheet and position it immediately before the Expansion worksheet.
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.
ak. Apply the Table Style Medium 6 table style. ( Hint : Depending on your version of Office, the table style may be written as Gold, Table Style Medium 6.)
al. Convert the table to a range.
Include subtotals on the Subtotals worksheet as follows:
am. Add subtotals to the range A3:H49 so that for each change in the Class Name value, the formula uses the Sum function to add subtotals to the Amount Paid field. Use the default settings for the bottom checkboxes.
an. Use the outline buttons to display only the Class Name values with subtotals and the grand total.
Verify the grand total by switching to the Students worksheet and then adding a Total row to the Students table.
Switch to the Expansion worksheet. Gwen wants to expand the arts center into the adjoining vacant office space, and then renovate the new space. On the Expansion worksheet, she asks you to help her analyze the loan options to expand the business. Begin by determining the monthly payments as follows:
ao. In cell B9, enter a formula using the PMT function to calculate the monthly payment using the loan information in the range B5:B7.
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:
at. In cell B10, enter a formula without using a function that multiplies the monthly payments (cell B9 ) by the term in months (cell B7 ) for Loan Option 1.
au. Copy the formula from cell B10 into the range C10:D10.
Calculate the total interest as follows:
av. In cell B11, enter a formula without using a function that subtracts the loan amount (cell B5 ) from the total payments (cell B10 ) for Loan Option 1.
aw. Copy the formula from cell B11 into the range C11:D11.
Your workbook should look like the Final Figures below. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
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
Provide details on what you need help with along with a budget and time limit. Questions are posted anonymously and can be made 100% private.
Studypool matches you to the best tutor to help you with your question. Our tutors are highly qualified and vetted.
Your matched tutor provides personalized help according to your question details. Payment is made only after you have completed your 1-on-1 session and are satisfied with your session.
SAM Project Spreadsheets New Perspectives Excel 2019
User Generated
Business Finance
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:
oIf you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
oSupport_EX19_CS5-8a_2020.xlsx
oSupport_EX19_CS5-8a_Management.docx
oIf cell B6 does not display your name, delete the file and download a new copy from the SAM website.
oIn Excel, click the File tab, and then click the Options button in the left navigation bar. Click the Add-Ins option in the left pane of the Excel Options dialog box. Click the Manage arrow, click the Excel Add-Ins option, and then click the Go button. In the Add-Ins dialog box, click the Solver Add-In check box and then click the OK button. Follow any remaining prompts to install Solver.
PROJECT STEPS
- 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.
d.Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.
c.Fill the range B6:E7 with the formulas in the range B5:E5 to total the sales for the other products in Quarters 1–4.
d.In cell F7, enter a formula using the SUM function to display the total of the sales amounts in the Waterproof_Total range.
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.
6.In the range I3:L7, Benicio listed information about the managers of the DIG Technology main offices in the U.S., Canada, and Mexico. He needs to add a link to the email address of the U.S. manager. In cell L5, create a link to the [email protected] email address without changing the display text.
c.Use Access manager details as the ScreenTip text.
8.Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. Benicio wants to use the table data in formulas. Assign the table name Sales to the table of sales data in the range A5:G40 to make it easy to refer to the data.
d.If Excel does not fill the column, fill the range G6:G40 with the formula in cell G5.
10.The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date, then amount to make it easier to track the data. Apply a custom sort to the Sales table to sort it in ascending order first by the values in the Date column, and then by the values in the Amount column.
b.Delete the second duplicate record from the table. ( Hint : Do not delete the row from the worksheet.)
12.Benicio might want to filter the Product Details data in the range I4:M16, so he asks you to format it as a table. Format the range I4:M16 as a table using Light Gray, Table Style Medium 14 to match the Sales table.
d.Sum the Amount values
e.Change the PivotTable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet.
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.
d.Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21 .
21.Run Solver again, create an Answer report, and then close the Solver Parameters dialog box.
d.Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold (range B17:F17 ) as the result cells.
c.Change the number format of the value fields to Currency with 2 decimal places and the $ symbol.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
The Answer Report 1 worksheet and Scenario Summary worksheet have intentionally not been shown.
Unformatted Attachment Preview
Explanation & Answer
The soluti...
24/7 Homework Help
Stuck on a homework question? Our verified tutors can answer all questions, from basic math to advanced rocket science !
Similar Content
Related tags.
Cost of Capital personal budget excel assignment albany technical Summary accountant "J.D Salinger" "J.D Salinger" excel for time sheets at animal service excel CPMGT/303 Version 10 Project Estimating and Control Techniques Income Statement excel
Killers of the Flower Moon
by David Grann
Freakonomics
by Stephen J. Dubner and Steven D. Levitt
by Toni Morrison
Murder on the Orient Express
by Agatha Christie
by Cormac McCarthy
Cry the Beloved Country
by Alan Paton
Girl in Translation
by Jean Kwok
Crime and Punishment
by Fyodor Dostoyevsky
The Subtle Art of Not Giving a F*ck
by Mark Manson
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
Sign up with Google
Sign up with Facebook
Already have an account? Login
Login with Google
Login with Facebook
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