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

Unit VII

see attached Assignment Instructions You will use the multinational corporation (MNC) you researched in Unit VI for this assignment. The purpose of this scholarly activity is to continue to analyze fundamental aspects of corporate finance management in multinational corporations. You will examine supply chains and working capital management and make

Discussion 1

Write a 350 word response with 1 reference Due 1/6/2026 Discussion Question One: In this course, you will have an opportunity to explore ways to address topics of your interest that may represent challenges or areas in need of improvement or growth facing the organization.  · Considering your position, what

Business Finance – Management MBA 511 Week One Assignment. Powerpoint Presentation 7-8 slides

Week One Assignment Instructions Assignment 1: Assess Emotional Intelligence Top of Form Bottom of Form Instructions Competent leadership drives organizational success. Research shows that self-awareness, a central aspect of Emotional Intelligence, is more crucial for effective leadership than IQ or technical skills (Showry & Manasa, 2014). Self-awareness underpins managerial capability

sdddjsjs

Personal Reaction Paper Instructions: The purpose of the assignment is to reflect on the concepts gleaned from reading chapters 1 – 3 of the required textbook ( Human Resource Management). The objective of the paper is to analyze how human resource concepts apply to employees and the organizations they are

7-1 Discussion: What Would You Do Differently?

 For this discussion, describe a persuasive message that your workplace leader sent to an audience who resisted the idea, product, or service. Imagine yourself as a member of the audience. What would you recommend to the leader to better construct the message to get the intended results? Identify at least

Week 7 Discussion- Delivery of Healthcare

Continuum of Care Scenario: Team B Discussion 1 In this Discussion, you will continue to analyze your continuum of care scenario using the value-based care model, the IOM Aims, and a country comparison. You will be placed into a team and share your suggestions for improvements. Then, you will provide

Week 7 Discussion- Managerial Finance

Using Financial Forecast  Financial forecasts are crucial for businesses to make informed decisions, allocate resources effectively, and measure performance. They are typically based on historical data, which can make it challenging to predict sudden changes in market conditions or business operations. To mitigate financial risk, managers can employ strategies such

week 5

 overall business strategy your canvas depicts.  Assignment Directions: GOAL: Build on your work with your business model canvas by discussing the patterns, the design, and the overall business strategy it illustrates.  Introduction: Using the week 5 Learning Material in the Content area (coupled with the other course resources), examine and discuss

BUSN100

See attached. Instructions For this assignment just type the transcript Assignment Instruction: You are the public information officer (PIO) for a small company, responsible for communicating and distributing information for your organization. Prompt In this scenario, an unexpected incident has occurred within your local community, impacting the safety of employees

MKTG201

see attached Instructions: EOS Brand Assignment Directions: Objective:  For this assignment, you will explore how brands implement Integrated Marketing Communications (IMC) by evaluating their ability to deliver a consistent message across various marketing channels. Start by selecting a brand that interests you; this can be a well-known company or a

Unit V

see attached Post 1: Initial post addressing the discussion board topic • Your initial post should be at least 300 words in length. • Your initial post should include at least one APA-formatted scholarly, professional, or textbook reference with accompanying in-text citation to support any paraphrased, summarized, or quoted material.

Is Royal Ludo a good first online game?

  Royal Ludo stands out as a welcoming entry point into online real-money gaming, especially for those wary of overly complex platforms. It emphasizes simplicity, familiarity, and low-risk learning. With easy   <a href=” Ludo download</a> options and cross-device compatibility, it’s accessible to everyone.  

How Do Pattern Recognition and Timing Affect Success in Stock Trading

 How does understanding market behavior in sideways trends (like TTQA shifts in sentiment and large vs. small fund activity) compare to recognizing patterns and timing in a game like [url= surfers[/url]  , where success depends on reading the level and reacting at the right moment? 

Create a Shipping list for the items bellow. It needs to be editable

   six-bedroom household shipment.  List of items in shipping Container: * Two (2) dining table sets * Three (3) living room couch sets * Washer and dryer * Two (2) master bedroom furniture sets * Four (4) regular bedroom furniture sets , (2) Kids’ basketball court  10 Children’s toys and

Week 6 Discussion- Delivery of Healthcare

Continuum of Care Scenario: Team A Discussion 3 For this team Discussion, you will have the opportunity to review the work of others and provide them with peer-reviewed feedback, as well as consider how their continuum of care might be improved. Further, you will be able to compare and contrast

Business Finance – Management Week 5 Assignment- Managerial Finance

Module 2 Assignment: Financial Health Assessment: Barry Computer Company Report prepared by: Replace this text with your name. Date: Replace this text with the submission date. Walden University MBAX 6070: Managerial Finance 1 Part 1: Financial Information Replace this text with introductory information. Add or remove headings as necessary. [Heading]

Week 8 MGMT601

 align and discuss the implications for organizational commitment   Assignment Directions:  You will conduct an interview with a colleague or contact currently employed, focusing on their perceptions of the psychological contract with their employer. You will then write a 3–5-page report outlining the unwritten expectations from both the employee and