Kibata

Sign In

Our Services

Get 15% Discount on your First Order

Order Now
[rank_math_breadcrumb]

lab

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

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

2

Raybridge Recruiting

CREATE A SALES REPORT

GETTING STARTED

Save the file
SC_EX365_2021_3a_
FirstLastName_1.xlsx
 as
SC_EX365_2021_3a_
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_3a_
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

Lindsey Sawyer is the director of recruiting and business development for Raybridge Recruiting in Washington, D.C. She has been analyzing sales and commissions for recruiters in an Excel workbook and has asked you to help her complete the analysis.

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

Unfreeze column A so it is not displayed when you scroll horizontally.

Middle-align the contents of the merged cell A1 to improve the appearance of the worksheet title.

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

Fill the range C6:G10 with the formatting from the range B6:B10 to use a consistent number format for the sales data.

Lindsey wants to show the sales trend for each position and month from January to June. Provide this information as follows:

In cell H6, insert a
Line sparkline based on the data in the range B6:G6.

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

Change the color of the sparklines in the range H6:H10 to
Dark Purple, Text 2 (4th 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 I6 and paste it in the range I7:I10, pasting only the formula and number formatting, to include sales totals.

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

In cell B13, insert a formula without using a function that divides the total sales for January (cell
B10) by the total sales to date (cell
I10).

Use an absolute reference to cell I10 in the formula.

Use the Fill Handle to fill the range C13:G13 with the formula in cell B13.

Lindsey also needs to calculate the dividends earned each month. If the company earns $200,000 or more in a month, the dividend is 35% of the sales. If the company earns less than $200,000 in a month, the dividend is 27% of the sales. Calculate the dividends as follows:

In cell B17, enter a formula that uses the
IF function and tests whether the total sales for January (cell
B10) is greater than or equal to
200000.

If the condition is true, multiply the total sales for January (cell
B10) by
0.35 to calculate a dividend of 35%.

If the condition is false, multiply the total sales for January (cell
B10) by
0.27 to calculate a dividend of 27%.

Use the Fill Handle to fill the range C17:G17 with the formula in cell B17 to calculate the dividends for February through June.

Change the sparklines in the range H17:H18 as follows to use a more meaningful format:

Change the Column sparklines to
Line sparklines.

Apply the sparkline style
Dark Gray, Sparkline Style Dark #3 (3rd column, 5th row of the Sparkline Styles palette) to make the sparklines easier to see.

Lindsey would like to increase the average number of placements per month. Because executive placements are the most profitable, Lindsey wants to know how many executive placements she needs to reach the goal of 30 placements per month.

Use Goal Seek to set the average number of placements for all positions (cell
I27) to the value of
30 by changing the average number of executive placements (cell
I23).

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

Merge and center the range J22:J27.

Rotate the text down to
-90 degrees in the merged cell so that the text reads from top to bottom.

Change the width of column J to
6.00.

Delete row 35, which contains information Lindsey does not need anymore.

Lindsey also wants to compare the estimated sales of July and December. Insert a chart that provides this information as follows:

Create a
Clustered Column chart based on the nonadjacent ranges A22:B26 and G22:G26.

Enter
Estimated Sales: July and December as the chart title.

Resize and reposition the chart so its upper-left corner is in cell A35 and its lower-right corner is in cell E49.

Change the chart layout to
Layout 2 to display the data values above the bars instead of including a vertical axis.

(Note: The value in cell B3 of the sales and dividends worksheet may differ from that shown below.)

Your workbook should look like the Final Figures on the following pages. The value in cell B3 has been intentionally blurred as it will never be constant. 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 and Dividends Worksheet

© Elzipo/Shutterstock.com

5aeda309-5734-44ab-9585-7a2755544745.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

Order Now

Related Questions

HRM 6303 VII SA

2 Training and Development HRM 6303 Unit VII SA For this assignment, you will write a report that details the components of an executive leadership development program for a fictitious company. The fictitious company can be within an organization and industry of your choice. Do not use the name of

ACC 5301 VI

2 Management Applications of Accounting ACC 5301 Unit VI Assignment This assignment measures your mastery of ULOs 2.5, 4.1, 5.2, and 6.1. You have been hired at ABC Manufacturing, and you have been asked to work on the budget for the upcoming year regarding labor costs and the number of

Lead 4

Week 4: The KPI Dashboard The KPI Dashboard Review the following video. Transcript attached Instructions After reviewing the Week 4 Reading and Supplemental Materials, create a 2-page Microsoft Word Memo and infographic (instead of a traditional dashboard) for an organization you have been or still are affiliated with.  You do not have

MIS -4

Week 4: Project Paper #2: Jobs to be Done and Value Proposition Hypothesis Introduction You will use the Jobs to Be Done (JTBD) framework to reframe the problem from the customer’s perspective, then define the key value your product or service must deliver to solve it. This is your opportunity

Response

see attached  Informative Presentation Assessment After the final presentations are complete, write a brief (250-word minimum) reflection essay on how things went for you that includes the following: 1. Describe your own process as you prepared to give your presentation. How did you settle on the topic that you chose?

Firm

   Paul Duncan, financial manager of EduSoft Inc., is facing a dilemma. The firm was founded 5 years ago to provide educational software for the rapidly expanding primary and secondary school markets. Although EduSoft has done well, the firm’s founder believes an industry shakeout is imminent. To survive, EduSoft must

Excel spreadsheet

Need this project completed Sheet1 Key Check Figures Schedules Schedule B July August September Sched E15 Total collections $2,274,993 $2,572,479 $2,858,704 $929,200 Schedule D COGS $1,692,384 $1,909,842 $2,156,968 Schedule E Total Payments $2,076,010 $1,985,264 $2,177,350 Projected Income statements Net Income before Taxes $125,877 $161,323 $203,086 Projected Balance Seet September Accounts

Business

PSYC 320 Behavior Modification Project: Treatment Phase Report Assignment Instructions Overview The treatment phase is like the baseline phase with the exception of the treatment being added to determine if there is a change in the data. Treat your target behavior, using the treatment method(s) that you have researched in

HUM 102 Module Five Project Draft

SEE ATTACHMENT Module Five Project Draft Guidelines and Rubric.html HUM 102 Module Five Project Draft Guidelines and Rubric Overview At this stage, you have learned how creative works are components of the humanities, how diverse perspectives add value to works, how bias and assumptions can influence how you view creative

int 6

topic:   World Financial Markets and Institutions 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,

CASE STUDY 20 nov

Follow all instruction. Work will be checked no plagarism. strategic management case study based on Case 12: Electronic Arts (EA) from the Thompson, A. (2024). Crafting & Executing Strategy: Concepts and Readings for Competitive Advantage (Custom 24th ed.) McGraw-Hill. textbook.

Your Trusted Source for BMW Replacement Parts

 Whether you need headlights, interior trims, steering wheels, or electrical modules, MT Auto Parts delivers genuine BMW components at competitive prices. Our dismantled parts come from 2012+ models and undergo strict quality checks. Reliable solutions for every BMW owner.  

Case Study

See attachment  BUSI 690 Case Study: Matrices Assignment Instructions Overview Complete a case study of ABC Corporation (your instructor will assign the specific company for the case study at the beginning of Module 2: Week 2) . You will find the case in the case section of the text. A

theory question

theory question accounting RS-14 Resilience Scale The RS-14 (Resilience Scale – 14 items) is a widely used tool to measure individual resilience. Participants rate how well each statement applies to them on a 7-point Likert scale: 1 = Strongly Disagree to 7 = Strongly Agree. No Resilience Statement Score (from

D4

Course Project: Request for Proposal (RFP) Instructions This week, you are required to produce your project Request for Proposal (RFP) deliverable. The RFP is a formal invitation issued by the University of Prosperity requesting interested PPM vendors to submit written proposals meeting a set of requirements. If interested in bidding

Business Finance – Accounting homework

see attached 2 Use this reference for below questions. Brickley, J.A., Smith, C.W., & Zimmerman, J.L. (2021). Managerial economics and organizational architecture (7th ed.). New York: McGraw Hill/Irwin. #1. 1 page with 2 references. Should decision management and decision control be separated? As part of your discussion, provide a real-world