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

HRM 6304 Unit I DB

2 Labor Relations and Collective Bargaining HRM 6304 Unit I Discussion Board In your opinion, does having two different existing labor federations (i.e., American Federation of Labor and Congress of Industrial Organizations [AFL-CIO] and Change to Win) strengthen or weaken the ability of organized labor to represent the interests of

Which of the following is a topic of macroeconomics?

 Which of the following is a topic of macroeconomics? A) why nations have different rates of growth B) what causes inflation and what can be done about it C) why unemployment periodically reaches very high levels D) all of the above

Business Finance – Management Week 9 Assignment- Delivery of Healthcare

Delivery of Healthcare Week 9 Assignment Improvement Plan- Part 2 This week, you should continue your Improvement Plan: Part 2. As you work on your Improvement Plan, be sure to incorporate peer-review feedback (as appropriate). This week, focus on the areas of: · Information technology (value-based care model) · Be

Business Finance – Accounting Assignment Week 2

 Tax Research using Code and Administrative Sources. Facts: You are a slightly over-confident  CPA who advises Changling Industries, LLC, a Florida Manager Managed  LLC a small start up limited liability that has garnered the attention  of several Angel Financing organizations. The founder, Nodan Idiote, of  middle-eastern origin and three of

Ecosystems

   Do you think the introduction of non-native species to an ecosystem always pose a threat to that ecosystem? 

Innovative and Strategic Thinking

Market entry, discussing details.  Market Entry The Impact of Innovation The graduate identifies the impact of innovation in personal and professional settings. Techniques of Strategic Management The graduate utilizes evidence-based techniques to make strategic decisions. Business Strategy and Analysis The graduate applies appropriate business practices to formulate recommendations that impact

Week 3 pj

 wood-based shipping container and a plastic-based shipping  Instructions:  You are going to compare and contrast the advantages and disadvantages of a paper- based shipping container, a wood-based shipping container and a plastic-based shipping container for use as a distribution package (a non-retail shipping package). You must consider the entire supply chain in

Week 3 MGMT618

 Workplace scenario involving an observed unethical  Assignment Directions This assignment assesses  CO-3 [content page].  Students will provide a workplace scenario involving an observed unethical practice. Submission Instructions: Write a 5–7 page report analyzing the situation, evaluating potential responses, and recommending the most ethically sound course of action. Requirements: Use at least

A22- Discussion

 Additionally, you will explore the concept of Relationship Marketing and, in particular, you will focus on Differentiating Customers by Their Needs. Please answer these questions and use scholarly support for your answers: 

Week 8

 building blocks from the business model canvas where modification  Assignment Directions: Goal: Conduct/construct an experiential evaluation/assessment of your organization’s business model (or one with which you are closely familiar) using the business model canvas and related text. Introduction: Managers who want to improve the effectiveness of their business model and

SEMCO Case study 1.0

Please see rubric, instruction and questions for the assignment that needs to be address attached. BMAL 710 Disorganization at Semco Case Study Assignment Instructions Overview Please read the Integrative Case 1.0 in Daft text and respond to all four questions at the end of the case study. Instructions Please make

Unit VIII

see attached Assignment Instructions Tactical financing decisions are essential as they guide a company’s immediate financial strategies. They encompass the decisions about how a company obtains and uses funds in the short term. In this journal entry, you will dive deep into a real-world example, analyzing a company’s recent tactical

cw2

the company for the work is Trader Joe’s entering the uk market files below with details  6MK509 Marketing Across Cultures CW2 Fit to Submit General: • 2,000 words (+/- 10%) Report format. • Do you have a good hierarchy of information (cover page, table of contents, headings, subheadings that have

Governance Review Presentation Using AI Insights

  Read the case study, “Zest: A Driving Force in Motors” concerning the strategic direction of Zest, particularly in terms of product diversification and market expansion.  For this assignment, you will develop a governance plan presentation for key stakeholders based on a real business case and AI tools like PMI

Assignment 1 689

Webinars are an effective way to obtain on-demand information regarding issues and challenges that leaders face. This assignment is designed to help students learn about various resources available for professional development, as well as engage in the thoughtful examination of pertinent leadership topics. After you have listened to your chosen

Response 1689

Write a 100 word response Due 1/18/2026 Mendes Based on the challenges identified in Week 1 discussion, it is clear that the need for stronger communication, reliability and flexibility in my work environment is the focus. A transformational leadership model appears most suitable for addressing current societal and organization issues.

Response 2689

Write a 100 word response Due 1/18/2026 Bradford Reflecting on a previously identified challenge related to change fatigue and ongoing organizational strain, a leadership model that is well suited to address current societal and organizational issues is transformational leadership. This approach emphasizes purpose, communication, and shared commitment, which are essential