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

GSCMW8

Prepare a final presentation video. · Presentation video or narrated PowerPoint must be at least 5 to 8 minutes long. · In this presentation, you will summarize your findings from the Product Buying Report. · This part of the assignment will provide the visual presentation of the report. Should have

WK III

See attached WK III Essay This 3-to-5-page essay measures your mastery of ULOs 1.1, 1.2, 2.1, 6.1, 6.2 and 7.1. Elasticity Assignment objective: Unit I introduced the benefits of markets to improving outcomes for producers and consumers. Unit II examined the role of costs and prices in decision-making. For this

WK IV Journal

see attached This journal measures your mastery of ULOs 1.3, 3.1, 5.1, and 6.3. Journal objective: International trade can have significant effects on domestic markets. For use in the assignment, identify a good that the United States imports and a good the United States exports.  Length: Your submission is required

Week 2 db 2

See attached Post 1: Initial post addressing the discussion board topic is due by the end of day on Saturday. This unit’s readings include information on pricing. You are introduced to the shutdown price, using elasticity to determine the effects of price changes, and cost-based pricing. Discuss the importance of

issue exploration with business administration

Research Topic: The Impact of Remote Work on Business Efficiency and Employee Productivity Research Questions: How has the shift to remote work influenced business efficiency and employee productivity? Will there be a shift in productivity with employees that are being forced back into the office? What factors contribute to higher or

Memo #6 Human Resources and Sustainability

Find an article (electronic) on sustainability in the functional area being discussed (see Functional Areas above and schedule) as it relates to one of the categories of the environment (see Categories of the Environment above).  From the article, you will write a very particular type of memo that is a

Finance

See attached Report Topics/Individual Research Paper: Select one of the following topics 1. Ratio and Financial Statement Analysis – Your report should critically analyze the benefits and limitations of ratio analysis, explaining what factors impact the meaningfulness of such measures and what new practices or theories may be emerging regarding the

Case study 8

See attached Case Study Purpose: Flexibility, adaptability, and responsiveness to the external environment is essential if organizations are going to survive and thrive in a rapidly changing environment. Therefore, leaders and managers must know how to  implement change successfully in their organizations or departments in response to rapidly shifting conditions. The

Business & Finance Individual written assignment

See instruction My Professional Work Journal Response Essay Previously you were directed to keep a professional work journal ( No journal will be provided, you will make something up for your imaginary professional journal as a sustainability analyst ). Now you will write a journal response paper. You will review

financial projections chart

need the following types of charts using the financial projections provided for the business for both 2025 and 2026     Revenue by Month INSTRUCTIONS: Include a chart that shows your projected revenue.      Expenses by Month    INSTRUCTIONS: Include a chart that shows your projected expenses.    Net Profit

dis #3

 Read the attached article (found at the end of the instructions), “How to Give the Best Presentation You Possibly Can,” by Karl Gude from “The Huffington Post.” Then, answer the following questions:   A. FIRST STEP – Create a post to reply to question 1 and question 2: 1. How do

dis #4 pers

  Discussion 4 – Persuasive Speaking Aligns to SLO 3 Watch the following video, “Sample Persuasive Presentation” (9:11 mins; cc):   A. FIRST STEP – Create a post to reply to question 1 and question 2: 1)  Did you find the video persuasive?  Why or why not?  Explain your thoughts.

Forume dubai

I’m planning a trip through soon and wanted to get some insights on parking options. I’ve read that there are different types of parking (premium, economy, and multi-level), but I’d love to hear from people who have firsthand experience.

Best Practices for Setting Up UptimeRobot Alerts

  Uptime monitoring is essential for keeping websites and servers running smoothly. As a great way of pursuing UptimeRobot to support businesses with the tracking of their online services and to receive immediate alerts in the course of any possible failures, UptimeRobot has become widely accepted. However, to get the