Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Modules 5 8 sam capstone project 1a

27/10/2021 Client: muhammad11 Deadline: 2 Day

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

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png 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

A

B

C

D

E

F

G

14

PR110

Piano

10:00 AM

Saturday

Chopin Room

$25

Tamada

15

PR111

Voice

11:00 AM

Saturday

Basie Hall

$25

Thurow

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

C:\Users\akellerbee\Documents\SAM Development\SAM Projects\SAM Excel 2016 - Prof. Juan Martinez\WIP\ILL\Capstone\WorkingFiles\Capture-7a.png

Final Figure 4: Students Worksheet

C:\Users\akellerbee\Documents\SAM Development\SAM Projects\SAM Excel 2016 - Prof. Juan Martinez\WIP\ILL\Capstone\WorkingFiles\Capture-7a.png

Final Figure 5: Subtotals Worksheet

C:\Users\akellerbee\Documents\SAM Development\SAM Projects\SAM Excel 2016 - Prof. Juan Martinez\WIP\ILL\Capstone\WorkingFiles\Capture-7a.png

Final Figure 6: Expansion Worksheet

C:\Users\akellerbee\Documents\SAM Development\SAM Projects\SAM Excel 2016 - Prof. Juan Martinez\WIP\ILL\Capstone\WorkingFiles\Capture-7a.png

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Finance Homework Help
Homework Tutor
Peter O.
Academic Master
Unique Academic Solutions
Top Academic Tutor
Writer Writer Name Offer Chat
Finance Homework Help

ONLINE

Finance Homework Help

Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading.

$19 Chat With Writer
Homework Tutor

ONLINE

Homework Tutor

I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project.

$36 Chat With Writer
Peter O.

ONLINE

Peter O.

I am an elite class writer with more than 6 years of experience as an academic writer. I will provide you the 100 percent original and plagiarism-free content.

$30 Chat With Writer
Academic Master

ONLINE

Academic Master

I will be delighted to work on your project. As an experienced writer, I can provide you top quality, well researched, concise and error-free work within your provided deadline at very reasonable prices.

$47 Chat With Writer
Unique Academic Solutions

ONLINE

Unique Academic Solutions

I reckon that I can perfectly carry this project for you! I am a research writer and have been writing academic papers, business reports, plans, literature review, reports and others for the past 1 decade.

$46 Chat With Writer
Top Academic Tutor

ONLINE

Top Academic Tutor

I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles.

$15 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Bus 475 - How to build a static consolidation in excel - Emirates airlines marketing case study - Sum of products canonical form - Cure for diabetes ielts reading answers - Nostalgic ceramics stoke on trent - The life span human development for helping professionals 4th ed - Golden absorbent ground hobby lobby - How to remove wallpaper paste from plaster walls - What is an example of proximodistal development - Examples of euphony in literature - Cluny clays driving range - Www whitehouseblackmarket store account associate registration jsp - Indicator lab answers - Liner algebra - Merbau weight per metre - Give me liberty 5th edition pdf volume 2 - Alyogyne huegelii blue heeler - When was the egyptian number system created - Cardiff university campus map - Martinez company's relevant range of production is - Is muslim a proper noun - Deprag d 92224 amberg manual - Fsgdfg - The art of possibility - How to read literature like a professor chapter main points - Cisco room kit default password - Discussion - Rereading america 11th edition pdf - Factor v leiden and flying - Buchholz relay operation pdf - Flat pack kitchens ballarat - Adaptation worksheet answer key - Mixing tank baffle design - Matilda who told lies - Figure 34.1 label this anterior view of the human heart - Of mice and men dreams - On a karnaugh map grouping the 0s produces - The black balloon movie - Hcf health insurance packages - Paper - Linear algebra matlab projects - ONet Online differences - Edm 300 exhaust fan - Ps music continuing problem answers - Outline some other indicators of elder abuse - Somewhere in outer space god has prepared a place - Teaching strategies individual care plan - What is 3 as a fraction in simplest form - Enduring power of attorney short form - Skullcandy case study - Martha, can you assist me please with a paper regarding emotional intelligence and it's importance to children development and or society please? - Crop circles ielts reading answers mini ielts - Polypodium hydriforme in humans - How to date a browngirl thesis - Operational : 4 - 189 urquhart street coburg - Excel 2016 module 11 sam project 1a - Shadow health neurological assessment documentation - Mcguire property management colorado - Definition of educational sociology by george payne - Goldilocks and the three bears summary - Owasp application security architecture cheat sheet - Compare and contrast the european and north american globalized trade - Text message expander java - Is rock armour hard engineering - Family Business 5 - Cisco 3825 router manual - Swot analysis of coca cola 2015 - Advantages and disadvantages of full wave rectifier - Marketing analytics udacity - Monument 14 savage drift summary - Grand canyon university student teaching requirements - United States Government Accountability Office website for Key Issues - Fundamentals of organizational behavior 6th edition pdf - How important groups, roles, statuses, and social networks are in the structure of society - How to clear mathematica notebook - Canon 430ex ii manual - Examples of reversible changes - Statements of cash flows three examples pdf - Masters of professional psychology deakin - Ghost mountain riders mc - San diego sheet metal inc - Ancient artefact crossword clue - Olds trumpet serial numbers - The communication process involves all of the following except - Pharmacology - Does toms still give away shoes - Leeson speedmaster motor control - Can you imagine an imaginary menagerie manager - Oscar chess v williams - Latvian song and dance festival 2013 - East group person feng shui - Persuasive essay on vaccines - What does negative times negative equal - Least common multiple of 9 75 and 15 - 2 Responses to discussion. Leadership - Elevator traffic handbook pdf - Are too many people going to college - Morrisons distribution centre swan valley