Our Services

Get 15% Discount on your First Order

[rank_math_breadcrumb]

lab 2

Shelly Cashman Excel 365/2021 | Module 3: SAM Project 1b

Shelly Cashman Excel 365/2021 | Module 3: SAM Project 1b

2

Sales Dart Services

CREATE A SALES REPORT

GETTING STARTED

Save the file
SC_EX365_2021_3b_
FirstLastName_1.xlsx
as
SC_EX365_2021_3b_
FirstLastName_2.xlsx

Edit the file name by changing “1” to “2”.

If you do not see the
.xlsx file extension, do not type it. The file extension will be added for you automatically.

With the file
SC_EX365_2021_3b_
FirstLastName_2.xlsx
open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

Amir Nader is a sales analyst for Sales Dart Services, a sales and marketing services company in Chicago, Illinois, that works with businesses in the consumer goods industry. Amir has been creating a report that analyzes sales and commissions in an Excel workbook, and has asked you to help him complete the report.

Go to the
Report worksheet. Rename the
Report worksheet to as
Sales Report, which is a more accurate name.

Unfreeze the panes to scroll the worksheet without keeping the top and left panes displayed.

Middle Align the contents of the merged cell A2 to improve the appearance of the “Sales Report” subtitle.

In cell K2, insert a formula that uses the
NOW function to display today’s date.

Fill the range C5:G9 with the formatting from the range B5:B9 to use a consistent number format for the sales data.

Amir wants to show the sales trend for each service category and month from July to December. Provide this information as follows:

In cell H5, insert a
Line sparkline based on the data in the range
B5:G5.

Use the Fill Handle to fill the range H6:H9 without formatting based on the contents of cell H5.

Change the color of the sparklines in the range H5:H9 to
Brown, Accent 5 (9th column, 1st row of the Theme Colors palette) to emphasize them.

Show a
High Point and a
Low Point in the sparklines to make those values easy to identify.

Copy the formula in cell I5 and paste it in the range I6:I9, pasting only the formula and number formatting, to include sales totals.

Amir wants to determine how the sales of each month contributed to the total sales. Calculate this information for him as follows:

In cell B12, insert a formula without using a function that divides the total sales for July (cell
B9) by the total sales (cell
I9).

Use an absolute reference to cell I9 in the formula.

Use the Fill Handle to fill the range C12:G12 with the formula in cell B12.

Amir also needs to calculate the commissions paid each month. If the company earns $275,000 or more in a month, the commission is 26% of the sales. If the company earns less than $275,000 in a month, the commission is 17% of the sales. Calculate the commissions as follows:

In cell B16, enter a formula that uses the
IF function and tests whether the total sales for July (cell
B9) is greater than or equal to
275000.

If the condition is true, multiply the total sales for July (cell
B9) by
0.26 to calculate a commission of 26%.

If the condition is false, multiple the total sales for July (cell
B9) by
0.17 to calculate a commission of 17%.

Use the Fill Handle to fill the range C16:G16 with the formula in cell B16 to calculate the commissions for August through December.

Change the sparklines in the range H16:H17 as follows to use a format similar to the Monthly Sales sparklines:

Change the Column sparklines to
Line sparklines.

Apply the sparkline style
Dark Red, Sparkline Style Accent 1, Darker 50% (1st row, 1st column of the Sparkline Color theme palette) to make the sparklines easier to see.

Amir would like to increase the average number of clients served per month. Because retail merchandising has the lowest sales, Amir wants to know how many retail merchandising clients he needs to reach the goal of 35 clients served per month.

Use Goal Seek to set the average number of clients for all services (cell
I26) to the value of
35 by changing the average number of retail merchandising clients (cell
I23).

Format the text in cell J21 to clarify what it refers to as follows:

Merge and center the range J21:J26.

Rotate the text down in the merged cell.

Change the width of column J to
7.00.

Delete row 32 to keep the Estimated Commissions information together.

Amir also wants to compare the estimated sales of January and June of next year. Insert a chart that provides this information as follows:

Create a Clustered Column chart based on the nonadjacent ranges A21:B25 and G21:G25.

Enter
Estimated Sales: Jan and June as the chart title.

Resize and reposition the chart so that its upper-left corner is in cell A33 and its lower-right corner is in cell E45.

Change the chart layout to
Layout 5 to display a data table with the chart.

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 website to submit your completed project.

Final Figure 1: Sales Report Worksheet

81828f37-65b3-4928-82e8-2bba7ab71268.jpeg

image9.png

image6.png

image7.png

image8.png

Share This Post

Email
WhatsApp
Facebook
Twitter
LinkedIn
Pinterest
Reddit

Order a Similar Paper and get 15% Discount on your First Order

Related Questions

Prof Double R

  Directions Initiative Materials Your initiative needs professional materials to support it. Create one or more relevant products that address all of the required elements: Explain how your topic—either the juvenile justice system or allied professionals—relates to the law enforcement branch of the criminal justice system. Include information from one

Business Finance – Accounting Week Four Assignment 7.

Assignment 7: Describe Corporate Governance Top of Form Instructions (CLO:  5, 8)  · Complete Chapter 13:  Mini-case (pp. 559-560).  Complete the above textbook assignment and submit in Brightspace using a Word document.  Length: This assignment must be long enough to completely answer the question.  Bottom of Form

BUSINESS( NO PLAGARISM A+ WORK, ON TIME)

Discussion Tutor 1 IG ( Respond no more than 150 words) Hello Everyone At first glance, this situation looks like a simple “highest bidder wins” scenario. But contract law is not about who offers the most money. It is about whether a binding agreement was formed and when that happened.

A family plan

   A family plans to purchase their first home in 5 years. To afford the monthly payments, the couple estimates they will need a down payment of $150,000 at that time. Assuming they can earn 7% of their investment, how much money will they need to deposit today?

Advance Auditing

Please review the assignment attached. I live in the caribbean region, therefore I prefer information/sources being used to not be that far out of the region.. I am okay with the USA and UK material.  additional information to assist with the assignment and every aspect of MSC level work and

Unit VI GSM

See Attached Unit VI Assignment Strategic Expansion of a Multinational Firm into a New Global Market This assignment measures your mastery of ULOs 4.2, 5.1, and 5.2. This assignment will help you develop strategic decision-making skills crucial for global business management and the successful expansion of multinational enterprises into new

Total Quality Management Discussion 5

This is the problem from week 1 I went with    One issue at the Cook County Courthouse that could benefit from quality improvement is invoice processing and how long it takes vendors to get paid. Cook County uses Net 60 payment terms, while many private companies operate under Net

SCMG201

see attached 5 Assignment 2 – Weeks 3 & 4 Essay Questions SCMG 201 – Principles of Supply Chain Management Enter Student Name Enter Date Chapter 3: Network and Systems Design (Questions 1 through 5) Question 1 Response: Question 2 Response: Question 3 Response: Question 4 Response: Question 5 Response:

DEFM200

see attached Instructions Assignment Directions This assignment assesses  CO-3 [content page]. Read the article by David Garvin (1987),  Eight Dimensions of Quality. Create a PowerPoint presentation consisting of eight slides, excluding the title and reference pages. List four to five main points (using bullet point format) on each main slide.

Business & Finance a homework project . The class is Estate Planning and the project is a memo for Client interview Assignment. a homework project . The class is Estate Planning and the project is a memo for Client interview Assignment.

a homework project . The class is Estate Planning and the project is a memo for Client interview Assignment.  BUSI 354 Memo: Client Interview Assignment Instructions Overview This assignment requires you to use the information you have studied in the first 5 modules of the course in order to identify

week 7

 corporate ethical dilemma or fraud case of their choice  Students will write a comprehensive 12–15 page paper analyzing a major corporate ethical dilemma or fraud case of their choice. The paper should include identification of ethical issues, stakeholders, analysis using at least two ethical frameworks, evaluation of leadership and organizational

BUSINESS( NO PLAGARISM A+ WORK, ON TIME)

Discussion Assignment Week 7 Assignment: Final Project Signature Assignment: This assignment aligns with all course COs. Recommendation: Print complete Assignment to fully read and reference in preparing your paper! Paper outline is included. Prompt: Background For your final project, you are creating your own hypothetical scenario within the following parameters

case up

BUSI 642 Case Analysis: Approaches to Performance Management Assignment Instructions Overview Performance management systems are important because they deal with how employees achieve organizational goals. A key aspect of managing performance is accurately identifying why an employee may not be meeting expectations. This assignment allows you to hone your skills

discussion convey

INDS 491 Discussion Assignment Instructions The discussions in this course are meant to be collaborative learning experiences that foster a spirit of community and collegiality, but you must also view these as academic assignments with formal requirements. For these discussions, you are required to submit a thread in response to

Alternative Energy

  Over the past two centuries, fossil fuels have helped us build the complex industrialized societies we enjoy today. Yet our supplies of conventional fossil fuels are declining. We have responded to this challenge by expanding our search for new sources of fossil fuels. This will eventually lead to us

discussion

BUSI 301 Discussion Assignment Instructions Overview Discussions bring dialogue and communication into our learning experience. Unlike so much of the unsubstantiated opinion we see in the public square and social media, these discussions provide a space for in-depth analysis and application of the topics in our Learn readings and presentations.

discussion

BUSI 301 Discussion Assignment Instructions Overview Discussions bring dialogue and communication into our learning experience. Unlike so much of the unsubstantiated opinion we see in the public square and social media, these discussions provide a space for in-depth analysis and application of the topics in our Learn readings and presentations.

Competitive advantages

   What are the primary means for gaining and sustaining competitive advantages for companies shifting downstream? Is that also the case in a circular economy? Explain and discuss these statements.