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

Tutorial 8 sam project 1a answers

07/11/2020 Client: arwaabdullah Deadline: 10 Days

Shelly Cashman PowerPoint Chapter 1: SAM Project 1a

New Perspectives Excel 2013 | Tutorial 8: SAM Project 1a

New Perspectives Excel 2013

Tutorial 8: SAM Project 1a

Spring Software

ADVANCED FUNCTIONS AND CONDITIONAL FORMATTING

Project Goal

M Project Name

Project Goal

PROJECT DESCRIPTION
Henrique Silva works in the Sales department of Spring Software, a software development company headquartered in Cambridge, Massachusetts. Henrique has created a worksheet to capture customer sales data for the Nashua office for the past three years. He has asked you to enhance the workbook using conditional formatting and advanced formulas to better analyze the customer data, highlight trends, and identify top customers.

GETTING STARTED
· Download the following file from the SAM website:

· NP_Excel2013_T8_P1a_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

· NP_Excel2013_T8_P1a_FirstLastName_2.xlsx

· Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· With the file NP_Excel2013_T8_P1a_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
Go to the CustomerList worksheet and complete the following actions:

a. Apply a conditional formatting rule to the range A4:A32 that formats any duplicate values with Yellow Fill with Dark Yellow Text.

b. Update the Customer ID value for Firehouse Partners LLC to 1015 and the Customer ID value of the Applewild Learning Center to 1023.

The conditional formatting rule should no longer highlight any values in the range A4:A32.

Edit the conditional formatting rule applied to the range G4:G32 so that the highlighted cells are formatted with the font color White, Background 1 and the fill color Blue, Accent 5, Lighter 40% (9th column, 4th row in the Theme color palette.)

In cell D4, enter a formula that calculates customer tenure, in years, using absolute and structured references. The formula should calculate tenure based on subtracting the value in the First Order column from the current year value in cell B1. If necessary, copy the formula you created in step D4 to the range D5:D32. (Tip: Remember to use an absolute reference to the current year value in cell B1.)

In cell I4, enter a formula that uses the IF function and structured references to calculate the 2-Year Growth Percentage.

c. The 2-Year Growth Percentage can be calculated if the value in 2015 Sales column is greater than 0.

d. To calculate the 2-Year Growth Percentage, divide the value in the 2-Year Sales Growth column by the value in the 2016 Sales column.

e. If the value in the 2015 Sales column is not greater than 0, return a value of N/A.

In cell J4, enter a formula that uses an IF function and structured references to determine customer discount eligibility. (Tip: You will need to use the OR function in this formula.)

f. A customer is eligible for a discount if the customer’s 2016 sales greater than or equal to 100000 OR if the customers First Order was placed in 2016.

g. If the customer qualifies for a discount, return a value of Y. (Tip: For the value_if_true value, use “Y”.)

h. If the customer does not qualify for a discount, return a value of N. (Tip: for the value_if_false, use “N”.)

In cell K4, enter a formula that uses a nested IF function and structured references to calculate the discount percentage:

i. If the value in Discount Y/N column is equal to N, the Discount column value should be 0.

j. If the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 5.

k. If the value in Tenure (Yrs) column is less than 5, the Discount column value should be 0.07

l. Otherwise, the value of Discount column should be 0.1.

(Note: The Discount column is formatted with the Percentage Number format, so the values returned by the nested IF function will appear as 0%, 7%, or 10%.)

In cell L4, Enter a formula that uses an IF function and structured references to assign a priority value to each customer. (Tip: You will need to use an AND function in this formula.)

m. The If function should check if a customer has a Tenure field value greater than or equal to 5 years AND 2016 sales field value greater than or equal to 150000.

n. If the customer meets both those criteria, the function should return the value High.

o. If the customer does not meet both those criteria, the function should return the value Low.

In cell Q4, nest the VLOOKUP function in an IFERROR function. (Hint: The arguments for the VLOOKUP function should not be modified.) The cell should display the error message Invalid Customer ID instead of the error value, if the VLOOKUP function finds an error. Confirm the error message appears and then update the Customer ID value in cell Q3 to 1019.

In cell Q6, enter a formula using the VLOOKUP function to lookup the Customer ID value shown in cell Q3 in the CustomersNashua table (located in the range A3:L32). The VLOOKUP function should then retrieve the Discount value from the CustomersNashua table (the 11th column in the table) for this record. The VLOOKUP function should find an exact match to the value in Q3.

In cell Q7, enter a formula using the HLOOKUP function to determine the free services customer incentive offered to Spring Sales bigger clients. The HLOOKUP function should look up the value in cell Q5 in the range O10:R11 (which has the defined name CustomerIncentives). The HLOOKUP function should then retrieve the value in the 2nd row of the CustomerIncentives lookup table. Since these free services are offered to customers that meet or exceed the yearly sales levels listed in the CustomerIncentives table, the HLOOKUP function should find an approximate match to the value in Q5.

Go to the CustomerAnalysis worksheet. In cell C4, enter a formula that uses the COUNT function and structured references that counts the total number of customers in the CustomersNashua table on the CustomerList worksheet (Tip: The COUNT function only counts rows that contain a value, you cannot use the Customer column as an argument in the function. Use the Customer ID column instead.)

In cell D4, enter a formula that uses the COUNTIF function and structured references to count the number of customers with a Priority rating of High in the CustomersNashua table on the CustomerList worksheet. (Tip: The COUNTIF formula should use a structured reference to the Priority column.)

In cell C5, enter a formula that uses the SUM function and structured references to calculate 2016 sales for all customers.

In cell D5, enter a formula that uses the SUMIF function and structured references to calculate 2016 sales for customers with a Priority rating of High in the CustomersNashua table on the CustomerList worksheet.

In cell D6, enter a formula to calculate the percentage of High Priority Customers Sales out of All Customers 2016 sales. (Tip: The calculation should divide the 2016 High Priority Customer sales by the total 2016 sales for all customers.) Format the cell using the Percentage number format with no decimal places.

In cell C7, enter a formula that uses the AVERAGE function and structured references to calculate the average tenure of all customers in the CustomersNashua table on the CustomerList worksheet.

In cell D7, enter a formula that uses the AVERAGEIF function and structured references to calculate the average tenure of customers with a Priority rating of High in the CustomersNashua table on the CustomerList worksheet.

In cell C8, enter a formula to calculate the average sales per customer for all customers. (Tip: To calculate this average, divide the total 2016 sales (cell C5) by the total number of customers (cell C4) rather than using the AVERAGE function.)

In cell D8, enter a formula to calculate the average sales per High Priority Customer. (Tip: To calculate this average, divide the total 2016 sales for high priority customers (cell D5) by the total number of high priority customers (cell D4) rather than using the AVERAGEIF function.)

Your workbook should look like the Final Figures below and on the following page. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1-1: CustomerList worksheet, Cells A1:L27

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 1-2: CustomerList worksheet, Cells M1:S14

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 2: Customer Analysis worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

2

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:

Essay Writing Help
Peter O.
Quality Homework Helper
Top Grade Essay
A+GRADE HELPER
Buy Coursework Help
Writer Writer Name Offer Chat
Essay Writing Help

ONLINE

Essay Writing Help

I am a qualified and experienced Writer, Researcher, Tutor, analyst and Consultant. I hold MBA (Strategic Management) (Finance and Marketing) & CPA.K (Accounting and Finance.)

$187 Chat With Writer
Peter O.

ONLINE

Peter O.

Hello, I can assist you in writing attractive and compelling content on ganja and its movement globally. I will provide with valuable, informative content that you will appreciate. The content will surely hit your target audience. I will provide you with the work that will be according to the needs of the targeted audience and Google’s requirement.

$180 Chat With Writer
Quality Homework Helper

ONLINE

Quality Homework Helper

Hi dear, I am ready to do your homework in a reasonable price.

$187 Chat With Writer
Top Grade Essay

ONLINE

Top Grade Essay

Working on this platform from a couple of time with exposure of dynamic writing skills gathered with years experience on different other websites.

$187 Chat With Writer
A+GRADE HELPER

ONLINE

A+GRADE HELPER

Greetings! I’m very much interested to work on this project. I have read the details properly. I am a Professional Writer with over 5 years of experience, therefore, I can easily do this job. I will also provide you with TURNITIN PLAGIARISM REPORT. You can message me to discuss the detail. Why me? My goal is to offer services to you that are profitable. I don’t want you to place an order once and that’s it. For me to be successful, I need you to come back and order again. Give me the opportunity to work on your project. I wish to build a long-term relationship with you. We can have further discussion in chat. Thanks!

$180 Chat With Writer
Buy Coursework Help

ONLINE

Buy Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$187 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

Delilah carol ann duffy poem - Global Marketing - Anderson plug solder or crimp - Fundamental concepts of organizational behavior ppt - Non judgemental in social work - Unit 2 DB Technological Influences - Forum: Chapter 9: Discussion - Capstone -SWOT- 2pages - Facts about youth employment - Grapes of wrath intercalary chapters - Delivering healthcare in america a systems approach - La profesora martínez es . rosa y teresa son . nosotros somos - Order 2620017: Performance Evaluation and Action Plan - Find the missing side of each right triangle - Hazchem code 2x meaning - Siegler deloache & eisenberg 2014 - RUA: Analyzing Published Research - Myitlab access grader project - Electric field mapping lab report theory - Physical development in early adulthood powerpoint - Coca cola case study file type ppt - Shoprite checkers food prices - One example of neolithic myths are - Module 05 Assignment-Exercise and Nutrition - Henry gantt management theory pdf - Caesium chloride balanced equation - Alibaba case study questions and answers - A to z environmental words - 3m ranger blood warmer set up - Oh now carry me to bethlehem song - Egg drop experiment report - Ford motor company six sigma case study - Non fruiting olive trees perth - Types of industry sectors - Shipbuilding industry in india - Teaching the bluest eye - Blundells cottage virtual tour - Enhance Security Policies with Multi-Factor Authentication. - Recover all keys from the scrolls of romulus - Paul root wolpe it's time to question bio engineering - Circular causality family therapy - Strategic plan part 3 balanced scorecard - Research Question - Astc rule in trigonometry - What is channel stuffing in accounting - How are present values affected by changes in interest rates - Uts room booking system - Re-write - Project management simulation scope resources and schedule - Betty parris personality traits - Doctor role play script - Which of the following statements is true regarding self-managed work teams? - Asthma soap note - Discussion - Melbourne city tram map - Watch pan's labyrinth hulu - Purpose of a physical assessment - Social marketing process fan acquisition - Manufacturing systems class Topic ( safety in manufacturing) - Food handling course chisholm - Darrell o donoghue architect - Homework - Cdc distracted driving study tradução do texto - Illuminated backlit container used in photography crossword - Seven areas of responsibility for health educators - Msn program outcomes chamberlain college of nursing - Prepare a trial balance as of april 30 - The problem of social order - Noel carroll the philosophy of horror pdf - The sarbanes oxley act does all of the following except - Anxiety Disorders in the Media - Distance vector routing algorithm in c - His name is jesus friend of sinners - Unilever lifebuoy case study solution - Deep space composition in film - Week 4 - Ode to a conversation stuck in your throat chords - Ten commandments coptic orthodox - Ethical Dilemma: Todd and Zar - How many soldiers died on hacksaw ridge - Informative Speech - Two paths diverged from the main road answers com - National criminal history record check nchrc - Project - Access grader project chapter 3 - Business Proposal for Change - Understanding business research terms and concepts part 2 - 1 page summary - Cox communications channel list - Transformational geometry unit test b answers - Fiedler's contingency leadership model determines if a leader's style is - Nrs 490 capstone project change proposal - Https symc sam symantec com auth - Chris gardner jr net worth - How to calculate edp - Genetic engineering ppt template - Advantages of master slave jk flip flop - Two ways to belong in america by bharati mukherjee essay - The more stitches the less riches - Data management plan curtin