Our Services

Get 15% Discount on your First Order

[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

Related Questions

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

Week 8

 a major impact on the organization’s ability to operate globally.   Assignment Directions This assignment assesses CO-1, CO-2, CO-3, CO-4, CO-5.  [content page] You will conduct an organizational global readiness for your organization or one you are interested in working for. You will include and/or assess the following: 1. Organizational

Week 5 Discussion- Delivery of Healthcare

Continuum of Care Scenario: Team A Discussion 2  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

Financial Forecasting

Financial Forecasting project, Complete the Income Statement and Balance Sheet on the appropriate tabs in the Financial Forecasting Template, using the downloaded data. Calculate the expenses to determine what will change and what will remain the same. Summarize your rationale, within each line item expense.

Future Recommendations for Leaders in the 21st Century

Planning and Decision-Making in Public Health Organizations, Public Health and the Fundamentals of Managing It, Ethics for Public Health Managers and Workforce Diversity Pick one interest to discuss….. Reflect on an aspect(s) of this course that was or were of particular interest to you or might have implications for choices

FINCB/571: Corporate Finance Competency 3 – Assessment

   Course Title: Corporate Finance Competency Assessment Title: Financial Plan Total Number of Points: 100 Assignment Directions Review the company you chose to work with in Competencies 1 and 2. Describe the business, including the type of business. Create the business case: • Determine why funding is needed for the