Our Services

Get 15% Discount on your First Order

[rank_math_breadcrumb]

lab

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

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

2

Diaz Marketing

COMPLETE A BUDGET SUMMARY WORKSHEET

GETTING STARTED

Save the file
SC_EX365_2021_1b_
FirstLastName_1.xlsx
as
SC_EX365_2021_1b_
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_1b_
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

Ashley Bowman manages the New England office for Diaz Marketing, a consulting firm that develops strategies for businesses to become more profitable. She has created a worksheet summarizing the revenue and expenses for the first six months of the year, and asks for your help in determining what the New England office needs to do in the rest of the year to meet its profit goals.

Go to the
Budget worksheet. Cut the worksheet subtitle from cell B1 and paste it in cell A2 to display the subtitle in its expected location.

In cell H2, add the text
consultants so that the complete text appears as “Number of consultants” and more clearly identifies the value in cell J2.

Enter
Feb in cell C3,
Mar in cell D3,
Apr in cell E3,
May in cell F3, and
Jun in cell G3.

In cell J4, enter a formula that subtracts the value in cell
I4 from the value in cell
H4 to determine how much revenue the New England office needs to earn from July to December to reach its goal for the year.

Ashley wants to calculate the gross margin from January to June and for the year to date. Provide this information as follows:

In cell B6, enter a formula that divides the value in cell
B5 (the gross profit for January) by the value in cell
B4 (the revenue in January).

Use the Fill Handle to fill the range C6:H6 with the formula in cell B6 to find the gross margin for February to June and for the year to date.

Ashley needs to sum the sales for each New England state for the year to date. Provide this information as follows:

In cell H9, enter a formula that uses the
SUM function to total the range
B9:G9 to calculate the year-to-date sales for Connecticut.

Use the Fill Handle to fill the range H10:H12 with the formula in cell H9 to find the year-to-date sales for the other states.

In cell B17, enter
27,363 as the general expenses for January.

Ashley wants to determine how much revenue each state needs to generate from July to December to reach its target for the year. Enter this information as follows:

In cell J9, enter a formula that subtracts the value in cell
I9 from the value in cell
H9 to find the amount of Connecticut sales Ashley needs to meet the goal.

Copy the formula in cell J9, and then paste it in the range J10:J14, pasting the formulas only, to find the differences for the other states, the total, and the revenue per consultant.

Ashley is interested in how much revenue the nine consultants each generate on average per month and for the year to date. Find this information as follows:

Edit cell B14 to include a formula that divides the value in cell
B13 (the total sales for Connecticut in January) by the value in cell
J2 (the number of consultants). Don’t change the reference to cell J2 that is already there.

Fill the range C14:H14 with the formula in cell B14 to determine the average revenue per consultant from February to June and for the year to date.

Ashley also needs to calculate the operating margin, which is the ratio of operating profit or loss to revenue and indicates how much the office makes after paying for expenses. Calculate the operating margin as follows:

In cell B19, enter a formula that divides the value in cell
B18 (the operating profit or loss in January) by the value in cell
B4 (the revenue in January).

Use the Fill Handle to fill the range C19:H19 with the formula in cell B19 to find the operating margin for February to June and for the year to date.

Ashley wants to visualize the total year to date sales separated into states. Insert a chart to provide this visualization as follows:

Create a
2-D Pie Chart based on the nonadjacent ranges
A8:A12 and
H8:H12.

Enter
Total Year to Date Sales by State as the chart title.

Reposition and resize the chart so its upper-left corner is in cell K3 and its bottom-right corner is in cell M17

Apply
Style 11 to the chart.

Hide the gridlines for the
Budget worksheet to make it easier to read.

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: Budget Worksheet

a062c1e0-146e-4cdb-b62e-6c2270387395.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

The Equifax Data Breach

Briefly explain what the Equifax data breach was, why it happened, and why it was important. You should mention that Equifax failed to protect personal data, discuss the ethical and security issues involved, and explain what companies can learn from the incident. Please see attached documents. 

Reflection on Big Data and AI

please see attached DAT 260 Module Five Journal Guidelines and Rubric Overview In this journal assignment, you will reflect on key concepts covered in this module. This assignment directly supports your work in Project Two, which is due in Module Seven. Directions In a well-crafted journal entry, address the following:

Disaster Recovery

  Questions: Research the network and server outage problems experienced during a previous man-made or natural disaster and answer the following questions: What parts of the infrastructure was impacted? How were the networks recovered? How redundancy could have mitigated the impact of the disaster?

Network Design and Plan Purpose

Please review attachment Mod 4 Project: Network Design and Plan Purpose In this module, we will introduce a course design project that will be completed in four parts during the course. This project provides you an opportunity to solve a comprehensive problem in firewall and virtual private network (VPN) implementation

Memecoins & Stablecoins Development

Please take a look at the attachment. Lab #3: Blockchain-Based Ecosystems: Memecoins & Stablecoins Development, Attack Simulation, and Auditing Lastname: ______________ First name:____________Date_______ TA_Prove_ Yes/No 1. Overview This lab guides students through designing, implementing, deploying, testing, and auditing a simple memecoin (ERC‑20) and a minimal ecosystem around it (liquidity pool,

Blockchain Presentation

presentation about a blockchain Term Project CIS 5730- Blockchain Application Project Guideline Part ii–Presentation Along with the project you will present what you did to the class in a short presentation. If you want, you can demonstrate the project, or just present an overview of what you did. Presentations should

Network Threats

  Questions: Below is a list of common network attacks: Distributed Denial of Service (DDoS) DNS poisoning ARP poisoning Domain hijacking MAC flooding MAC cloning Man-in-the-Middle Explain two of these network attacks and discuss methods/techniques for protecting the network against them.

Best Practices for Role-Based Privilege Management in Databases

  Research the best practices for managing and securing role-based privileges in databases and address the following questions in your discussion post:  What are the key challenges in managing role-based privileges, and how do they impact database security? Can you provide a real-world scenario where poor role-based privilege management led

Computer Science Cloud Computing assignment

Please see attachment for details 4 [ Note: To complete this template, replace the bracketed text with your own content. Remove this note before you submit your paper.] Cloud Computing Evaluation Paper 1 Cloud Computing [Differentiate between cloud computing models and their uses. What are the different types of deployment

Computer Science- Python Python Programming Assignment

Computer Science Python Programming Lab assignment help. Design a Python program that calculates a credit card customer’s minimum payment based on their balance. Please use If Else and elif statements in your code. You must add comments in your code. Please round the minimum payment to two decimal places using

Blockchain Application

I need help with my coursework project. Let me know if you need any help from me.  Lab 2: Blockchain-Based Academic Credentials Lab Lastname: ______________ Firstname:________________ 1. Overview AETHELRED UNIVERSITY Registrar’s Office wants to start issuing a digital transcripts and diploma for graduating students. You are called to build the

LOREM, IPSUM

The ability to develop a risk register is a skill needed by all cybersecurity leaders when assessing cybersecurity risks. A risk register provides a detailed listing of known risks as well as quantitative or qualitative assessments of those risks, resulting in the prioritization of action.

Virtual LANs

  Questions: A VLAN allows different devices to be connected virtually to each other as if they were in a LAN sharing a single broadcast domain. 1. Why a network engineer would want to deploy VLANs? 2. How do VLANs improve network security?

compliance and rules to follow in cybersecurity.

Follow the attached instructions to complete this work. Note: Make sure to follows rubric or aligns with Rubric. Unit 8 Assignment Directions: Case Study Review the following hypothetical case study. Consider the big-picture ideas and the specific concerns. Make use of the key terms and concepts from the readings in

Discussion on data ( computer science)

Follow the attached direction to complete this work Unit 7 Discussion   Overview Consider this scenario: PQR Corporation provides facial recognition technology to customers. Its products include customer access to consumer electronics as well as mass surveillance capabilities through networked camera systems. While operating legally, PQR has maintained a low

Computer Science – Machine Learning Python Programming Assignment

Assignment Help. Please don’t forget to add comments in the code Page 1 of 3 NorQuest College – CMPT 1011: Lab Assignment 5 CMPT 1011: Introduction to Computing Lab Assignment 2: Variables, mathematical operations and data types Value This coding challenge is worth 3% of your final grade. Background In