Description
- Distinguish between functional and psychological needs. Identify 1 product that would fulfill each need for you and explain how it fulfills your need.
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
Explanation & Answer
Example, right now I have shoulder pain, the functional need would be that of massage therapy. (Physical)
Psychological needs affect our cognitive process and patterns, thus, remedial product would be that of a physiologist. (mential)
Completion Status:
100%
Review
Review
Anonymous
Really helped me to better understand my coursework. Super recommended.
Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
24/7 Homework Help
Stuck on a homework question? Our verified tutors can answer all questions, from basic math to advanced rocket science!
Most Popular Content
11 pages
5ivp Part B Revised Paper
What performance management activity is conducted? (in relation to the performance management cycle/stages). This should i ...
5ivp Part B Revised Paper
What performance management activity is conducted? (in relation to the performance management cycle/stages). This should include a focus on the ...
BUS 340 San Diego Mesa College Ethical Dilemma in Workplace Questions
1 - Prior to beginning work on this discussion, read Chapter 1, Chapter 2, and Chapter 3 from your textbook and the Week 1 ...
BUS 340 San Diego Mesa College Ethical Dilemma in Workplace Questions
1 - Prior to beginning work on this discussion, read Chapter 1, Chapter 2, and Chapter 3 from your textbook and the Week 1 Weekly Lecture.Describe the difference between the terms “ethical dilemma” and “ethical lapse” using a minimum of one scholarly and/or credible source from the library to defend your answer. Provide an example from your life, or of someone you knew, who was faced with an ethical dilemma in the workplace. Without mentioning the name of the organization, describe the situation and the decision that was made. Explain an alternative solution to the dilemma. Then think of a time where you or someone you knew demonstrated an ethical lapse. Describe the situation and the decision. Then provide an alternative solution to the situation.250 words2- Prior to beginning work on this assignment, review Chapter 1, Chapter 2, and Chapter 3 from your textbook and the Week 1 Weekly Lecture.The insurance company you work for plans to raise all premiums for health care coverage for its customers. Your boss has asked you to proofread a letter she drafted to customers announcing the new, higher rates. The first two paragraphs discuss some exciting medical advances and the expanded coverage offered by the company. Only in the final paragraph do customers learn that they must pay more for coverage starting next year. Describe the ethical implications of this draft. What changes would you suggest? If your boss tells you not to make content changes, what will you do, and why?250 wordsplease use citation and refrences
6 pages
Export Strategies.edited
Exporting and getting involved in international trade is a significant aspect that helps an organization to experience sim ...
Export Strategies.edited
Exporting and getting involved in international trade is a significant aspect that helps an organization to experience simultaneous growth, ...
6 pages
Pa1 Assignment
Web surfing, also known as internet surfing, refers to moving from one page to the other in the internet browsing for topi ...
Pa1 Assignment
Web surfing, also known as internet surfing, refers to moving from one page to the other in the internet browsing for topics that one is interested in ...
JWU Management HRM Organizational Development Blog Activity
Objective: To exhibit an understanding of implementing change through organizational development
Think of your current wor ...
JWU Management HRM Organizational Development Blog Activity
Objective: To exhibit an understanding of implementing change through organizational development
Think of your current work situation - if you are not currently working, interning, or volunteering in a work environment, use a situation from a past school or work experience. Determine a something that is viewed as a problem by the employees (or students).
In place of this problem, what do you believe the ideal situation looks like?
Describe the favorable forces for change, those that would lead to your ideal situation (this can be done with bullet points with brief descriptions).
Describe the forces against change, those that could prevent your ideal situation (this can be done with bullet points with brief descriptions).
For the forces you listed in steps 3 and 4, evaluate each one in terms of importance to the situation. Rate each force on a scale from 1 to 10 with 1 being extremely weak, and 10 being extremely strong (a chart/table is a good format for this).
Take the most important forces identified in step 5, and construct a figure similar to 14-2.
Looking at your figure, which side has the strongest forces? What does this suggest about the likelihood of change in this situation?
Now assume you are tasked with making a change. Choose at least one strategy for each intervention category (Human Process-Based, Techno-Structural, Sociotechnical Systems, Organizational Transformation) to propose to your organization as a viable change method. Explain the specifics of each intervention as it relates to the situation you have chosen.
For each intervention, describe your role as the HRD practitioner.
Referring to the Planned Change Model, regardless of which intervention method is used, what type of cognitive change would be the desired outcome for this HRD program? Briefly explain.
How to do it?
Complete the tasks below using Microsoft Excel.You may refer to your notes, the textbook, or other resources (for example, ...
How to do it?
Complete the tasks below using Microsoft Excel.You may refer to your notes, the textbook, or other resources (for example, you may search the Web for information).You may not get interactive help from any other person (either in person, via email, text messaging, IM, or other communications channels). NOTE:Be sure you SPELL CHECK on every worksheet and correct all spelling errors.If you encounter technical issues of any type during the Final Exam, please notify your instructor and contact the appropriate helpdesk. Getting started Open Excel Open the Practical Exam workbook you downloaded from the Exam page in Canvas.Save the workbook as Lastname_FirstInitial Week8 FinalExam.xlsx.Example: If your name was Jane Doe, the file name would be:DoeJ Week8 FinalExam.xlsx Formatting and charts Formulas and applications Data cleansing, lists, sorting, conditional formatting, and pivot tables Data consolidation, analysis, and reporting What-if analysis Enter your name and today's date on the Documentation sheetSelect the Q1&Q2 sheet, and adjust the widths of the columns as neededCenter the title "Cool Clock, Inc. Sales by Product" across columns A-D, increase the font size to 20, and change the font color to red.Bold the column headings "Product", "Unit Price", "Quantity" and "Total Sales".Add formulas to the Total Sales column to calculate the total sales for each product.Add a grand total at the bottom of the Total Sales column, in cell D8.Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places.Create a 3-D pie chart on a separate sheet that displays the percentage of total sales for each product & the product name. (Do not include the grand total in the pie chart!). Make the title of the chart & the worksheet tab "Total Sales by Product". Place each product percentage of sales inside its pie slice.Place a legend for all products on the right side of the chart.Move the sheet named “Total Sales by Product” with the pie chart immediately after the Q1&Q2 sheet.Save your work (CTRL+s) - (but leave it open to continue). On the Q1&Q2 sheet, in cell E8, add a formula using a lookup function that will look up the grand total in cell D8 in the table of sales and assessments in A11:B13, and display the corresponding assessment. Use a range lookup. (For example, if the grand total of $12,500 is displayed in cell D8, "Good" should be displayed in cell E8.) In cell E9 enter the label Highest Sales. In F9 enter a formula using a function to display the highest total sales value for any product. In cell E10 enter the label Lowest Sales. In F10 enter a formula using a function to display the lowest total sales value for any product. In cell E11 enter the label Average Sales. In F11 enter a formula using a function to display the average total sales value for all products. Format the labels and statistics (Highest Sales, Lowest Sales, and Average Sales) to display an outline (exterior border) around these cells for readability. Resize all columns for visibility. Add comments to cells F9, F10, and F11. In your comments, explain briefly in your own words what is displayed in each cell. Add gridlines and cell formatting and color formatting to cells A10:B13. Add a top and double bottom border to cell D8. Protect the worksheet so the contents cannot be accidentally changed by a user. Do not use a password. Save your work (CTRL+s) - (but leave it open to continue). On the Q3 sheet, adjust the widths of the columns as needed. Column C contains each customer’s combined first and last name. Insert two empty columns to the right of column C, and use an Excel feature to separate the customers’ first names and last names into the new columns. In the first row, label the new columns Customer First Name and Customer Last Name. After doing this, delete the original Customer Name column. Resize the columns. Convert the list of orders (A1 – J21) into an Excel table. Sort the table into descending order (highest to lowest) by Order Amount. Apply conditional formatting to the Order Amount column so that order amounts greater than $100 are highlighted in green. Convert the table back into a range. Create a pivot table on a new worksheet that uses Customer Country as the row field, Product as the column field, and the sum of Order Amount as the values. Place this sheet immediately after the Q3 sheet, labeled Q3-Pivot. Format the cells in the Pivot table using all borders with accounting or currency format with 2 decimal places. Add gridlines to the Pivot table. Save your work (CTRL+s) - (but leave it open to continue). Group the four sheets Q4 East, Q4 Central, Q4 West, and Q4 Summary.While the sheets are grouped, bold the labels in row 1 and column A, and format the values in cells B2:E5 as currency with no decimal places. Resize all columns. Ungroup the sheets (very important!).On the Q4 Summary sheet, use 3D formulas to create a summary that displays the totals for each product and quarter over all 3 regions (East, Central, and West).Create a clustered column chart that shows total sales of each product in each quarter. Each cluster should represent a quarter, and each individual column should represent sales of a product within that quarter. Place your chart on the Q4 Summary sheet to the right of the data. Give the chart an appropriate title.Add a trend line to the chart to show how sales of alarm clocks are changing over time. In the space provided on the Q4 Summary sheet, write a brief (2-3 paragraph) analysis report to the manager of the Cool Clocks business. In your report, explain your findings on the trend in sales of alarm clocks, and any other patterns you observed in sales of the product categories; and recommend at least one specific action that Cool Clocks should take regarding the alarm clocks product line. Save your work (CTRL+s) - (but leave it open to continue). On the Q5 sheet, please format the Title row at the top of the worksheet stating “Detailed Sales What-if Analysis”.Make sure it is centered across columns A – G and has a heading format.Enter formulas in cells B6, B7, and B8 to calculate the total sales, total cost, and net income for a new product line, based on the projected unit sales, unit price and unit cost provided. Adjust column widths as needed and format all values except unit sales as currency with no decimal places.Complete the one-variable data table under the heading “Effect of Varying Unit Sales on Total Sales, Total Cost, and Net Income” to calculate the total sales, total cost and net income for projected sales of 80, 90, 100, 110, and 120 units.Complete the two-variable data table under the heading “Effect on Net Income of Varying Unit Sales and Unit Price” to calculate the net income for projected sales of 80, 90, 100, 110, and 120 units at unit prices of $4, $5, $6, $7, and $8.Format cells A3 – B8 with a thick box border. Format the one-variable data table with themed cell style.Make sure the Initial values, Total Sales, Total Cost and Net Income have an accent color.Format the two-variable data table with a themed cell style. Make sure the initial values for the horizontal and vertical have an accent color.Add conditional formatting to the one-variable data table. Format the top 10% with green fill.Format the bottom 10% with red fill. Save your work (CTRL+s) and close.Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook,
or other resources (for example, you may search the Web for information). You may not
get interactive help from any other person (either in person, via email, text
messaging, IM, or other communications channels).
NOTE: Be sure you SPELL CHECK on every worksheet
and correct all spelling errors. If you
encounter technical issues of any type during the Final Exam, please notify
your instructor and contact the appropriate helpdesk.
Getting
started
a. Open
Excel
b. Open
the Practical Exam workbook you downloaded from the Exam page in Canvas.
c. Save
the workbook as Lastname_FirstInitial
Week8 FinalExam.xlsx. Example: If
your name was Jane Doe, the file name would be:
DoeJ Week8 FinalExam.xlsx
1. Formatting
and charts
a.
Enter your name and today's date on the Documentation sheet
b.
Select the Q1&Q2 sheet, and adjust the widths of the
columns as needed
c.
Center the title "Cool Clock, Inc. Sales by
Product" across columns A-D, increase the font size to 20, and change the
font color to red.
d.
Bold the column headings "Product", "Unit
Price", "Quantity" and "Total Sales".
e.
Add formulas to the Total Sales column to calculate the
total sales for each product.
f.
Add a grand total at the bottom of the Total Sales column,
in cell D8.
g.
Format the numbers in the Unit Price and Total Sales columns
as accounting or currency format with a dollar sign and two decimal places.
h.
Create a 3-D pie chart on a separate sheet that displays the
percentage of total sales for each product & the product name. (Do not include the grand total in the pie
chart!).
i.
Make the title of the chart & the worksheet tab "Total
Sales by Product".
j.
Place each product percentage of sales inside its pie slice.
k.
Place a legend for all products on the right side of the
chart.
l.
Move the sheet named “Total Sales by Product” with the pie
chart immediately after the Q1&Q2 sheet.
m.
Save your work (CTRL+s) - (but leave it open to continue).
2. Formulas
and applications
On
the Q1&Q2 sheet, in cell E8, add a formula using a lookup function
that will look up the grand total in cell D8 in the table of sales and
assessments in A11:B13, and display the corresponding assessment. Use a
range lookup. (For example, if the grand total of $12,500 is displayed in
cell D8, "Good" should be displayed in cell E8.)
In
cell E9 enter the label Highest Sales. In F9 enter a formula using a
function to display the highest total sales value for any product.
In
cell E10 enter the label Lowest Sales. In F10 enter a formula using a
function to display the lowest total sales value for any product.
In
cell E11 enter the label Average Sales. In F11 enter a formula using a
function to display the average total sales value for all products.
Format
the labels and statistics (Highest Sales, Lowest Sales, and Average Sales)
to display an outline (exterior border) around these cells for
readability. Resize all columns for visibility.
Add
comments to cells F9, F10, and F11. In your comments, explain briefly in
your own words what is displayed in each cell.
Add
gridlines and cell formatting and color formatting to cells A10:B13.
Add
a top and double bottom border to cell D8.
Protect
the worksheet so the contents cannot be accidentally changed by a user. Do
not use a password.
Save
your work (CTRL+s) - (but leave it open to continue).
3. Data
cleansing, lists, sorting, conditional formatting, and pivot tables
On
the Q3 sheet, adjust the widths of the columns as needed.
Column
C contains each customer’s combined first and last name. Insert two empty
columns to the right of column C, and use an Excel feature to separate the
customers’ first names and last names into the new columns. In the first
row, label the new columns Customer First Name and Customer Last Name.
After doing this, delete the original Customer Name column. Resize the
columns.
Convert
the list of orders (A1 – J21) into an Excel table.
Sort
the table into descending order (highest to lowest) by Order Amount.
Apply
conditional formatting to the Order Amount column so that order amounts
greater than $100 are highlighted in green.
Convert
the table back into a range.
Create
a pivot table on a new worksheet that uses Customer Country as the row
field, Product as the column field, and the sum of Order Amount as the
values.
Place
this sheet immediately after the Q3 sheet, labeled Q3-Pivot.
Format
the cells in the Pivot table using all borders with accounting or currency
format with 2 decimal places. Add gridlines to the Pivot table.
Save
your work (CTRL+s) - (but leave it open to continue).
4. Data
consolidation, analysis, and reporting
a. Group
the four sheets Q4 East, Q4 Central, Q4 West, and Q4 Summary.
b. While
the sheets are grouped, bold the labels in row 1 and column A, and format the
values in cells B2:E5 as currency with no decimal places. Resize all columns.
c. Ungroup
the sheets (very important!).
d. On the
Q4 Summary sheet, use 3D formulas to create a summary that displays the totals
for each product and quarter over all 3 regions (East, Central, and West).
e. Create
a clustered column chart that shows total sales of each product in each
quarter. Each cluster should represent a quarter, and each individual column
should represent sales of a product within that quarter. Place your chart on
the Q4 Summary sheet to the right of the data. Give the chart an appropriate
title.
f.
Add a trend line to the chart to show how sales of alarm
clocks are changing over time.
g. In the
space provided on the Q4 Summary sheet, write a brief (2-3 paragraph) analysis
report to the manager of the Cool Clocks business. In your report, explain your
findings on the trend in sales of alarm clocks, and any other patterns you
observed in sales of the product categories; and recommend at least one
specific action that Cool Clocks should take regarding the alarm clocks product
line.
Save your work
(CTRL+s) - (but leave it open to continue).
5. What-if
analysis
a. On the
Q5 sheet, please format the Title row at the top of the worksheet stating
“Detailed Sales What-if Analysis”. Make
sure it is centered across columns A – G and has a heading format.
b. Enter
formulas in cells B6, B7, and B8 to calculate the total sales, total cost, and
net income for a new product line, based on the projected unit sales, unit
price and unit cost provided.
c. Adjust
column widths as needed and format all values except unit sales as currency
with no decimal places.
d. Complete
the one-variable data table under the heading “Effect of Varying Unit Sales on
Total Sales, Total Cost, and Net Income” to calculate the total sales, total
cost and net income for projected sales of 80, 90, 100, 110, and 120 units.
e. Complete
the two-variable data table under the heading “Effect on Net Income of Varying
Unit Sales and Unit Price” to calculate the net income for projected sales of
80, 90, 100, 110, and 120 units at unit prices of $4, $5, $6, $7, and $8.
f.
Format cells A3 – B8 with a thick box border.
g. Format
the one-variable data table with themed cell style. Make sure the Initial values, Total Sales,
Total Cost and Net Income have an accent color.
h. Format
the two-variable data table with a themed cell style. Make sure the initial
values for the horizontal and vertical have an accent color.
i.
Add conditional formatting to the one-variable data table.
Format the top 10% with green fill.
Format the bottom 10% with red fill.
Save your work
(CTRL+s) and close.
Similar Content
Blossoms up Case Study Email
Resources: Blossoms Up! Case Study and Email No. 1Numerous emails have been sitting in the HR Director's in-box for two mo...
APUS hrmt week 7
Define the types of benefits required by law. How can organizations minimize the cost of these benefits while complying wi...
SMU Data Analytics A Data Story impact of Dependency Ratios Project
Analyze data, provide examples and data, and write a data story.This task requires you to analyse data to tell a story tha...
Exchange Rates and International Investing, business and finance homework help
Many factors affect currency exchange rates. Determine the effect of the following factors on changes in currency exchange...
Tourism Competitiveness in Argentina Paper
Complete an article on the subject of Argentina. The length of this article is 5-6 pages, double-spaced, 12-point font, us...
Look Policy and Ethical Considerations Questions
Use the below information to reply to the below bold questions and answers. The “Look” PolicyGiven the hyper-sexualiz...
Information Technology Management Class Notes_36925638-Information-Technology-Infrastructure-Project
uploads/services/161584/Information Technology Management Class Notes_36925638-Information-Technology-Infrastructure-Proje...
Third Party Consent
Crime investigations often present the need for searching a property belonging to the suspect or persons of interest. The ...
Management Right To Work
Management: Right-to-Work Laws in the US, California and China Legislation on the right to work (RTW) is distinct federal...
Related Tags
Book Guides
Narrative of the Life of Frederick Douglass
by Frederick Douglass
12 Rules for Life
by Jordan Peterson
The Jade Peony
by Wayson Choy
Beowulf
by Anonymous Anglo-Saxon poet
A Brief History of Humankind Sapiens
by Yuval Noah Harari
Dune
by Frank Herbert
Shutter Island
by Dennis Lehane
The Girl With The Dragon Tattoo
by Stieg Larsson
The Prince
by Niccolò Machiavelli
Get 24/7
Homework help
Our tutors provide high quality explanations & answers.
Post question
Most Popular Content
11 pages
5ivp Part B Revised Paper
What performance management activity is conducted? (in relation to the performance management cycle/stages). This should i ...
5ivp Part B Revised Paper
What performance management activity is conducted? (in relation to the performance management cycle/stages). This should include a focus on the ...
BUS 340 San Diego Mesa College Ethical Dilemma in Workplace Questions
1 - Prior to beginning work on this discussion, read Chapter 1, Chapter 2, and Chapter 3 from your textbook and the Week 1 ...
BUS 340 San Diego Mesa College Ethical Dilemma in Workplace Questions
1 - Prior to beginning work on this discussion, read Chapter 1, Chapter 2, and Chapter 3 from your textbook and the Week 1 Weekly Lecture.Describe the difference between the terms “ethical dilemma” and “ethical lapse” using a minimum of one scholarly and/or credible source from the library to defend your answer. Provide an example from your life, or of someone you knew, who was faced with an ethical dilemma in the workplace. Without mentioning the name of the organization, describe the situation and the decision that was made. Explain an alternative solution to the dilemma. Then think of a time where you or someone you knew demonstrated an ethical lapse. Describe the situation and the decision. Then provide an alternative solution to the situation.250 words2- Prior to beginning work on this assignment, review Chapter 1, Chapter 2, and Chapter 3 from your textbook and the Week 1 Weekly Lecture.The insurance company you work for plans to raise all premiums for health care coverage for its customers. Your boss has asked you to proofread a letter she drafted to customers announcing the new, higher rates. The first two paragraphs discuss some exciting medical advances and the expanded coverage offered by the company. Only in the final paragraph do customers learn that they must pay more for coverage starting next year. Describe the ethical implications of this draft. What changes would you suggest? If your boss tells you not to make content changes, what will you do, and why?250 wordsplease use citation and refrences
6 pages
Export Strategies.edited
Exporting and getting involved in international trade is a significant aspect that helps an organization to experience sim ...
Export Strategies.edited
Exporting and getting involved in international trade is a significant aspect that helps an organization to experience simultaneous growth, ...
6 pages
Pa1 Assignment
Web surfing, also known as internet surfing, refers to moving from one page to the other in the internet browsing for topi ...
Pa1 Assignment
Web surfing, also known as internet surfing, refers to moving from one page to the other in the internet browsing for topics that one is interested in ...
JWU Management HRM Organizational Development Blog Activity
Objective: To exhibit an understanding of implementing change through organizational development
Think of your current wor ...
JWU Management HRM Organizational Development Blog Activity
Objective: To exhibit an understanding of implementing change through organizational development
Think of your current work situation - if you are not currently working, interning, or volunteering in a work environment, use a situation from a past school or work experience. Determine a something that is viewed as a problem by the employees (or students).
In place of this problem, what do you believe the ideal situation looks like?
Describe the favorable forces for change, those that would lead to your ideal situation (this can be done with bullet points with brief descriptions).
Describe the forces against change, those that could prevent your ideal situation (this can be done with bullet points with brief descriptions).
For the forces you listed in steps 3 and 4, evaluate each one in terms of importance to the situation. Rate each force on a scale from 1 to 10 with 1 being extremely weak, and 10 being extremely strong (a chart/table is a good format for this).
Take the most important forces identified in step 5, and construct a figure similar to 14-2.
Looking at your figure, which side has the strongest forces? What does this suggest about the likelihood of change in this situation?
Now assume you are tasked with making a change. Choose at least one strategy for each intervention category (Human Process-Based, Techno-Structural, Sociotechnical Systems, Organizational Transformation) to propose to your organization as a viable change method. Explain the specifics of each intervention as it relates to the situation you have chosen.
For each intervention, describe your role as the HRD practitioner.
Referring to the Planned Change Model, regardless of which intervention method is used, what type of cognitive change would be the desired outcome for this HRD program? Briefly explain.
How to do it?
Complete the tasks below using Microsoft Excel.You may refer to your notes, the textbook, or other resources (for example, ...
How to do it?
Complete the tasks below using Microsoft Excel.You may refer to your notes, the textbook, or other resources (for example, you may search the Web for information).You may not get interactive help from any other person (either in person, via email, text messaging, IM, or other communications channels). NOTE:Be sure you SPELL CHECK on every worksheet and correct all spelling errors.If you encounter technical issues of any type during the Final Exam, please notify your instructor and contact the appropriate helpdesk. Getting started Open Excel Open the Practical Exam workbook you downloaded from the Exam page in Canvas.Save the workbook as Lastname_FirstInitial Week8 FinalExam.xlsx.Example: If your name was Jane Doe, the file name would be:DoeJ Week8 FinalExam.xlsx Formatting and charts Formulas and applications Data cleansing, lists, sorting, conditional formatting, and pivot tables Data consolidation, analysis, and reporting What-if analysis Enter your name and today's date on the Documentation sheetSelect the Q1&Q2 sheet, and adjust the widths of the columns as neededCenter the title "Cool Clock, Inc. Sales by Product" across columns A-D, increase the font size to 20, and change the font color to red.Bold the column headings "Product", "Unit Price", "Quantity" and "Total Sales".Add formulas to the Total Sales column to calculate the total sales for each product.Add a grand total at the bottom of the Total Sales column, in cell D8.Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places.Create a 3-D pie chart on a separate sheet that displays the percentage of total sales for each product & the product name. (Do not include the grand total in the pie chart!). Make the title of the chart & the worksheet tab "Total Sales by Product". Place each product percentage of sales inside its pie slice.Place a legend for all products on the right side of the chart.Move the sheet named “Total Sales by Product” with the pie chart immediately after the Q1&Q2 sheet.Save your work (CTRL+s) - (but leave it open to continue). On the Q1&Q2 sheet, in cell E8, add a formula using a lookup function that will look up the grand total in cell D8 in the table of sales and assessments in A11:B13, and display the corresponding assessment. Use a range lookup. (For example, if the grand total of $12,500 is displayed in cell D8, "Good" should be displayed in cell E8.) In cell E9 enter the label Highest Sales. In F9 enter a formula using a function to display the highest total sales value for any product. In cell E10 enter the label Lowest Sales. In F10 enter a formula using a function to display the lowest total sales value for any product. In cell E11 enter the label Average Sales. In F11 enter a formula using a function to display the average total sales value for all products. Format the labels and statistics (Highest Sales, Lowest Sales, and Average Sales) to display an outline (exterior border) around these cells for readability. Resize all columns for visibility. Add comments to cells F9, F10, and F11. In your comments, explain briefly in your own words what is displayed in each cell. Add gridlines and cell formatting and color formatting to cells A10:B13. Add a top and double bottom border to cell D8. Protect the worksheet so the contents cannot be accidentally changed by a user. Do not use a password. Save your work (CTRL+s) - (but leave it open to continue). On the Q3 sheet, adjust the widths of the columns as needed. Column C contains each customer’s combined first and last name. Insert two empty columns to the right of column C, and use an Excel feature to separate the customers’ first names and last names into the new columns. In the first row, label the new columns Customer First Name and Customer Last Name. After doing this, delete the original Customer Name column. Resize the columns. Convert the list of orders (A1 – J21) into an Excel table. Sort the table into descending order (highest to lowest) by Order Amount. Apply conditional formatting to the Order Amount column so that order amounts greater than $100 are highlighted in green. Convert the table back into a range. Create a pivot table on a new worksheet that uses Customer Country as the row field, Product as the column field, and the sum of Order Amount as the values. Place this sheet immediately after the Q3 sheet, labeled Q3-Pivot. Format the cells in the Pivot table using all borders with accounting or currency format with 2 decimal places. Add gridlines to the Pivot table. Save your work (CTRL+s) - (but leave it open to continue). Group the four sheets Q4 East, Q4 Central, Q4 West, and Q4 Summary.While the sheets are grouped, bold the labels in row 1 and column A, and format the values in cells B2:E5 as currency with no decimal places. Resize all columns. Ungroup the sheets (very important!).On the Q4 Summary sheet, use 3D formulas to create a summary that displays the totals for each product and quarter over all 3 regions (East, Central, and West).Create a clustered column chart that shows total sales of each product in each quarter. Each cluster should represent a quarter, and each individual column should represent sales of a product within that quarter. Place your chart on the Q4 Summary sheet to the right of the data. Give the chart an appropriate title.Add a trend line to the chart to show how sales of alarm clocks are changing over time. In the space provided on the Q4 Summary sheet, write a brief (2-3 paragraph) analysis report to the manager of the Cool Clocks business. In your report, explain your findings on the trend in sales of alarm clocks, and any other patterns you observed in sales of the product categories; and recommend at least one specific action that Cool Clocks should take regarding the alarm clocks product line. Save your work (CTRL+s) - (but leave it open to continue). On the Q5 sheet, please format the Title row at the top of the worksheet stating “Detailed Sales What-if Analysis”.Make sure it is centered across columns A – G and has a heading format.Enter formulas in cells B6, B7, and B8 to calculate the total sales, total cost, and net income for a new product line, based on the projected unit sales, unit price and unit cost provided. Adjust column widths as needed and format all values except unit sales as currency with no decimal places.Complete the one-variable data table under the heading “Effect of Varying Unit Sales on Total Sales, Total Cost, and Net Income” to calculate the total sales, total cost and net income for projected sales of 80, 90, 100, 110, and 120 units.Complete the two-variable data table under the heading “Effect on Net Income of Varying Unit Sales and Unit Price” to calculate the net income for projected sales of 80, 90, 100, 110, and 120 units at unit prices of $4, $5, $6, $7, and $8.Format cells A3 – B8 with a thick box border. Format the one-variable data table with themed cell style.Make sure the Initial values, Total Sales, Total Cost and Net Income have an accent color.Format the two-variable data table with a themed cell style. Make sure the initial values for the horizontal and vertical have an accent color.Add conditional formatting to the one-variable data table. Format the top 10% with green fill.Format the bottom 10% with red fill. Save your work (CTRL+s) and close.Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook,
or other resources (for example, you may search the Web for information). You may not
get interactive help from any other person (either in person, via email, text
messaging, IM, or other communications channels).
NOTE: Be sure you SPELL CHECK on every worksheet
and correct all spelling errors. If you
encounter technical issues of any type during the Final Exam, please notify
your instructor and contact the appropriate helpdesk.
Getting
started
a. Open
Excel
b. Open
the Practical Exam workbook you downloaded from the Exam page in Canvas.
c. Save
the workbook as Lastname_FirstInitial
Week8 FinalExam.xlsx. Example: If
your name was Jane Doe, the file name would be:
DoeJ Week8 FinalExam.xlsx
1. Formatting
and charts
a.
Enter your name and today's date on the Documentation sheet
b.
Select the Q1&Q2 sheet, and adjust the widths of the
columns as needed
c.
Center the title "Cool Clock, Inc. Sales by
Product" across columns A-D, increase the font size to 20, and change the
font color to red.
d.
Bold the column headings "Product", "Unit
Price", "Quantity" and "Total Sales".
e.
Add formulas to the Total Sales column to calculate the
total sales for each product.
f.
Add a grand total at the bottom of the Total Sales column,
in cell D8.
g.
Format the numbers in the Unit Price and Total Sales columns
as accounting or currency format with a dollar sign and two decimal places.
h.
Create a 3-D pie chart on a separate sheet that displays the
percentage of total sales for each product & the product name. (Do not include the grand total in the pie
chart!).
i.
Make the title of the chart & the worksheet tab "Total
Sales by Product".
j.
Place each product percentage of sales inside its pie slice.
k.
Place a legend for all products on the right side of the
chart.
l.
Move the sheet named “Total Sales by Product” with the pie
chart immediately after the Q1&Q2 sheet.
m.
Save your work (CTRL+s) - (but leave it open to continue).
2. Formulas
and applications
On
the Q1&Q2 sheet, in cell E8, add a formula using a lookup function
that will look up the grand total in cell D8 in the table of sales and
assessments in A11:B13, and display the corresponding assessment. Use a
range lookup. (For example, if the grand total of $12,500 is displayed in
cell D8, "Good" should be displayed in cell E8.)
In
cell E9 enter the label Highest Sales. In F9 enter a formula using a
function to display the highest total sales value for any product.
In
cell E10 enter the label Lowest Sales. In F10 enter a formula using a
function to display the lowest total sales value for any product.
In
cell E11 enter the label Average Sales. In F11 enter a formula using a
function to display the average total sales value for all products.
Format
the labels and statistics (Highest Sales, Lowest Sales, and Average Sales)
to display an outline (exterior border) around these cells for
readability. Resize all columns for visibility.
Add
comments to cells F9, F10, and F11. In your comments, explain briefly in
your own words what is displayed in each cell.
Add
gridlines and cell formatting and color formatting to cells A10:B13.
Add
a top and double bottom border to cell D8.
Protect
the worksheet so the contents cannot be accidentally changed by a user. Do
not use a password.
Save
your work (CTRL+s) - (but leave it open to continue).
3. Data
cleansing, lists, sorting, conditional formatting, and pivot tables
On
the Q3 sheet, adjust the widths of the columns as needed.
Column
C contains each customer’s combined first and last name. Insert two empty
columns to the right of column C, and use an Excel feature to separate the
customers’ first names and last names into the new columns. In the first
row, label the new columns Customer First Name and Customer Last Name.
After doing this, delete the original Customer Name column. Resize the
columns.
Convert
the list of orders (A1 – J21) into an Excel table.
Sort
the table into descending order (highest to lowest) by Order Amount.
Apply
conditional formatting to the Order Amount column so that order amounts
greater than $100 are highlighted in green.
Convert
the table back into a range.
Create
a pivot table on a new worksheet that uses Customer Country as the row
field, Product as the column field, and the sum of Order Amount as the
values.
Place
this sheet immediately after the Q3 sheet, labeled Q3-Pivot.
Format
the cells in the Pivot table using all borders with accounting or currency
format with 2 decimal places. Add gridlines to the Pivot table.
Save
your work (CTRL+s) - (but leave it open to continue).
4. Data
consolidation, analysis, and reporting
a. Group
the four sheets Q4 East, Q4 Central, Q4 West, and Q4 Summary.
b. While
the sheets are grouped, bold the labels in row 1 and column A, and format the
values in cells B2:E5 as currency with no decimal places. Resize all columns.
c. Ungroup
the sheets (very important!).
d. On the
Q4 Summary sheet, use 3D formulas to create a summary that displays the totals
for each product and quarter over all 3 regions (East, Central, and West).
e. Create
a clustered column chart that shows total sales of each product in each
quarter. Each cluster should represent a quarter, and each individual column
should represent sales of a product within that quarter. Place your chart on
the Q4 Summary sheet to the right of the data. Give the chart an appropriate
title.
f.
Add a trend line to the chart to show how sales of alarm
clocks are changing over time.
g. In the
space provided on the Q4 Summary sheet, write a brief (2-3 paragraph) analysis
report to the manager of the Cool Clocks business. In your report, explain your
findings on the trend in sales of alarm clocks, and any other patterns you
observed in sales of the product categories; and recommend at least one
specific action that Cool Clocks should take regarding the alarm clocks product
line.
Save your work
(CTRL+s) - (but leave it open to continue).
5. What-if
analysis
a. On the
Q5 sheet, please format the Title row at the top of the worksheet stating
“Detailed Sales What-if Analysis”. Make
sure it is centered across columns A – G and has a heading format.
b. Enter
formulas in cells B6, B7, and B8 to calculate the total sales, total cost, and
net income for a new product line, based on the projected unit sales, unit
price and unit cost provided.
c. Adjust
column widths as needed and format all values except unit sales as currency
with no decimal places.
d. Complete
the one-variable data table under the heading “Effect of Varying Unit Sales on
Total Sales, Total Cost, and Net Income” to calculate the total sales, total
cost and net income for projected sales of 80, 90, 100, 110, and 120 units.
e. Complete
the two-variable data table under the heading “Effect on Net Income of Varying
Unit Sales and Unit Price” to calculate the net income for projected sales of
80, 90, 100, 110, and 120 units at unit prices of $4, $5, $6, $7, and $8.
f.
Format cells A3 – B8 with a thick box border.
g. Format
the one-variable data table with themed cell style. Make sure the Initial values, Total Sales,
Total Cost and Net Income have an accent color.
h. Format
the two-variable data table with a themed cell style. Make sure the initial
values for the horizontal and vertical have an accent color.
i.
Add conditional formatting to the one-variable data table.
Format the top 10% with green fill.
Format the bottom 10% with red fill.
Save your work
(CTRL+s) and close.
Earn money selling
your Study Documents