Our Services

Get 15% Discount on your First Order

[rank_math_breadcrumb]

Business Finance homework help

lab 2

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

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

>Business & Finance homework help

Share This Post

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

Related Questions

Presentation to Staff on Hangar Renovation Project Progress

Your supervisor has asked you to give a 15-min formal presentation during weekly staff meetings about (this week’s material). What will you share? How will you teach it? Put together a short slide deck outlining the topics you’d cover and how you would present them. Feel free to use

Legal Environment of Business

This is Jamaican/ British/TCI law. Kindly use cases and materials that refer to our law jurisdictions. Additionally, when reference material is used, it is often from the Caribbean and the US region. Ensure cases are real and not fictional. The professor gave instructions that when answering questions, we should ensure

D2

Procurement Management Plan Instructions This week, you are required to produce your project Procurement Strategy deliverable. Your Procurement Strategy will define how procurements will be planned, executed, and managed throughout the life of the project. The Procurement Strategy is prepared during the planning process phase of your project. The proposed

Audit- Internal controls

Materials posted. Need it by November 4th, 8:00 p.m. EST. N.B. Try to reference more US/Caribbean as that is more in my region.. but i belive the powerpoints notes and the book may suffice.

Assistance with Player Management Simulation

Player Management Simulation Assistance Player Management Player Management Sequence 1. Read Concept Overview 2. Take the Quiz 3. Review Case Briefing Document 4. Complete Learning Phase in Simulator 5. Complete Challenge Phase in Simulator Player Management Concept Overview – Table of Contents • The Key To Fielding A Winning Team

orientation

MGT 561 – Test 3 Essay Answers should be cited and referenced. You can do the references for all three at the end of all the essays or you can reference each one. The Answer should be 2-3 pages and double spaced. Be thorough in your answer. This work is

All Carpet Cleaner

How Often Should You Get Your Carpets Cleaned? Many people wait until their carpets look visibly dirty before cleaning them, but by that time, a lot of hidden dirt and allergens have already accumulated. The frequency of all carpet cleaning depends on a few key factors, including how much foot traffic

FEG Plus With Free Derma Roller Price in Lahore

Why Choose FEG Plus with Free Derma Roller? When you purchase FEG Plus, you not only get the powerful lash and brow serum but also a complimentary Derma Roller. The Derma Roller helps to enhance the absorption of the serum, allowing it to penetrate more deeply into the skin for

Business & Finance Assignment

Purpose: This assignment gives students an opportunity to apply principles of budget design, equity, and strategic planning to secure funding for an initiative that addresses an identified organizational need. It develops skills in resource justification, cost framing, and mission alignment. Assignment Description: Students will create a professional-style grant proposal seeking

I’m Working On This Question And I Need A Sample Draft To Help Me Study MKT600

Hide Assignment Information Turnitin™ This assignment will be submitted to Turnitin™. Instructions Objectives: LO1: The student will understand the role and content of the quality management plan for a project. LO2: The student will be able to develop a Quality Management Plan for a project. Instructions: Fill in the attached

short answer

instructions and reading attched Please answer the following questions. 1. In the ‘Turning the Tables’ reading there is a profile of first contract campaigns by the News Guild-CWA at the LA Times and at Law360. How did the organizing/bargaining committees use communication and mobilization during the negotiations to enhance their

HUM 102 Module Two Project

Please see attached file Unit Resources {Insert Unit Title]1.html Introduction The learning resources in this module will introduce you to the three topics that you’ll be able to choose from for your project and will provide you with examples of how the humanities relate to those topics. You’ll also learn

IDS 104 Module Two Journal Guidelines and Rubric

please see attached file. You will find 2 assignments. I will pay extra for both. Module Two Journal Guidelines and Rubric.html IDS 104 Module Two Journal Guidelines and Rubric Overview A growth mindset—the belief that abilities and intelligence can be developed through dedication and effort—is an essential skill for lifelong

Organizational Change

Describe a significant organizational change that you have witnessed or been a part of. Discuss its origins, impacts, and outcomes. Was it ultimately good for the organization? Were there winners and losers? Be sure to respond to at least TWO of your classmates’ posts.

Unit VIII

see attachment Unit VIII Assignment ABC Corporation has 3 plants across the United States in 3 different regions consisting of East, Central, and West. Last year, each plant purchased new equipment for their plants, and each plant earns the same income of $200,000. However, each plant required a different investment

Unit VII

see attachment Problem ABC Manufacuring Instructions: Direct Labor Budget for the Year Ending Dec 31, 202X 1. Review the data given, fill in the information in yellow cells to determine number of employees needed. Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Direct labor hours per unit 1 1

Foriegn

Topic Foreign exchange market BUSI 606 Discussion Assignment Instructions The Discussions allow you the opportunity to apply what you have learned from the Module: Week’s readings. There are 2 parts to each Discussion: thread and replies. Both parts must be posted and written in a substantive manner, without spelling and

Keeping up with the joneses

Research what the idiom; “Keeping up with the Joneses” means and relate how it can affect a person’s spending as they age. 2. It’s easier than ever to spend money today than it was in the past. Explain why this is so.