Create a chart for each sales person in the categories

Assignment Help Data Structure & Algorithms
Reference no: EM13944011

Instructions

- Open Excel and start with a blank spreadsheet. In cell A10 type in the words "iHaul Sales Force Facts and Figures" - do not use quotes, do use font size 16, and make the text bold. Merge and center these words from A10 to J10. Use rows 4 through 9 to insert any additional labels and values needed for this problem. USE 'IF' FUNCTION FOR LOGIC

DO not add any additional columns or rows to this spreadsheet only what's requires. However, add individual cell references (both labels and values) when necessary, but again, do not insert any additional columns or rows. For example, it is OK to create a cell with the word Increase and create a cell next to that with 10.00% inside of the cell. However, it would not be OK to create a column or row in between two existing columns or rows to find a total of some cells and then use that total in a different part of the problem Any formula or function you create in Excel 2007 must use cell references use the auto fill tool whenever possible. Read the following directions carefully

- Begin typing in the following information:

Sales Team

Web

Radio

TV

Print

Ad Income

Commissions

Expenses

Profit

Status

John

3

12

11

20

 

 

 

 

 

Wendy

5

8

8

30

 

 

 

 

 

Bill

7

10

15

10

 

 

 

 

 

Chris

1

5

7

15

 

 

 

 

 

Mike

9

16

9

19

 

 

 

 

 

Robert

6

17

5

4

 

 

 

 

 

Beth

4

11

12

13

 

 

 

 

 

Zeena

2

7

10

17

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Total

 

 

 

 

 

 

 

 

 

Average

 

 

 

 

 

 

 

 

 

Income

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

For this assignment, all values should be set at 2 decimal places - even when a decimal does not make sense! Do not round up or round down to reach a whole number.  All values representing money should be in currency format, all percentages in percentage format, and all remaining values should be in number format with the 1,000 separator showing. 

- Web, Radio, TV, Print: These cells show how many ads the sales team sold over the past year. Make sure you format the numbers correctly. There is nothing to calculate for these cells.

- Ad Income: In this column create a formula to show how much income (money) each salesperson generated in the past year from web, radio, TV, and print advertisements.

Important information: each web ad sells for $250, each radio ad sells for $625, each TV ad sells for $1,200, and each print ad sells for $750.

- Commissions: In this column use a function to show how much money each member of the sales team made in commission for selling ads. The amount of commission is based on the total ad income brought in by each member. Any member that generates $33,500.00 or more in ad income will receive 11% of that income as a commission. Any member that generates $29,500.00 or more in ad income will receive 8% of that income as a commission. All other sales members will receive 4% of their ad income as a commission.

- Expenses: In this column create a formula to find out how much money each sales member spent in expenses in order to generate their ad income. Knowing that it takes money to make money, you will assume that each member will typically spend 5.75% of their total ad income to make their sales.

- Profit: In this column create a formula to calculate how much profit (money) each sales member generated for the company.

Important information: For this problem, profit is loosely defined as total ad income minus any commission and expenses. 

- Status: It is time for the annual sales team review. In this column use a combination of functions to classify each sales member based on the average number of ads sold in the four categories of web, radio, TV, and print. Any member with an average number of ads above 10 from the web, radio, TV, and print categories  will be classified as "Well Rounded". All other sales members will be classified as "Specialist".

- Total: In this column use a function to calculate the total web, radio, TV, and print ads sold in the past year. You should only find the total for the web, radio, TV, and print columns - do not find the totals for the rest of the columns.

- Average: In this column use a function to calculate the average web, radio, TV, and print ads sold in the past year. You should only find the average for the web, radio, TV, and print columns - do not find the totals for the rest of the columns.

- Income: In this column create a formula to calculate the total income (money) generated by web, radio, TV, and print ads over the past year. Use the information about the prices of each ad type given earlier to solve this part of the problem.

- Conditional Formatting: Set conditional formatting 4 separate times; each time using the data bars category of conditional formatting. First, highlight the web ads for each sales person and format using the data bars category - pick a blue color. Second, highlight the radio ads for each sales person and format using the data bars category - pick a green color. Third, highlight the TV ads for each sales person and format using the data bars category - pick an orange color. Finally, highlight the print ads for each sales person and format using the data bars category - pick a red color. 

- Sort: **Save your work before doing this step**  Sort out the ad incomes so the highest ad incomes are at the top and the lowest ad incomes are at the bottom. Be sure to sort the information correctly so that all corresponding information moves with the ad incomes of each sales member.

- Chart: Finally, create a chart. Use a column chart (stacked column in 3-D) showing the ad sales for each sales person in the categories of web, radio, TV, and print.

This chart must show as a separate sheet tab entitled Ad Sales.

Make sure axis labels include the names of the sales members and the legend entries include the names of the different types of ads (web, radio, TV, and print). Place chart legend at the bottom of your chart. The title of the chart should be above the chart and should be: XXXXXXX XXXXX - Ad Sales For Entire Staff.

Near the top of the chart, insert a text box that contains the words: Top Sales Member. Insert an arrow pointing from the text box to the member with the highest total sales.

Once chart completed, format the colors of the chart in any manner you wish. However, make sure to leave enough contrast so each category can be seen.

Reference no: EM13944011

Questions Cloud

Mains part of an item of equipment through or across : ________ is current that flows from the mains part of an item of equipment through or across the insulation into the protective earth conductor.
Confidence interval for the mean price : Using the sample data, what is the margin of error associated with a 95% confidence interval? Develop a 95% confidence interval for the mean price charged by discount brokers for a trade of 100 shares at $50 per share.
What are some ethics and privacy issues : What are some ethics and privacy issues associated with developing a fitness application and website? What are some solutions to control these risks?
Transaction lacks commercial substance : Amble Inc. exchanged a truck with a carrying value of $12,000 and a fair value of $20,000 for a truck and $5,000 cash. The transaction lacks commercial substance.
Create a chart for each sales person in the categories : Open Excel and start with a blank spreadsheet. In cell A10 type in the words "iHaul Sales Force Facts and Figures" - do not use quotes, do use font size 16, and make the text bold.
Improve human memory in different professional settings : How could findings from the experiment be applied to develop policies or procedures to improve human memory in different professional settings? What strengths or weaknesses in human memory do the experiments highlight that would be important for p..
Mean active concentrations for the two catalysts : Find a 95% confidence interval on the difference in mean active concentrations for the two catalysts. Is there any evidence to indicate that the mean active concentrations depend on the choice of the catalyst? Base your answer on the results of par..
Opportunities of marketing products-services : What are the current challenges and opportunities of marketing products/services internationally?
Prepare the stockholders : Prepare the Stockholders' Equity section of the balance sheet as of June 30. 80,000 shares of common stock are authorized, and 9,000 shares have been reacquired.

Reviews

Write a Review

Data Structure & Algorithms Questions & Answers

  Implement an open hash table

In this programming assignment you will implement an open hash table and compare the performance of four hash functions using various prime table sizes.

  Use a search tree to find the solution

Explain how will use a search tree to find the solution.

  How to access virtualised applications through unicore

How to access virtualised applications through UNICORE

  Recursive tree algorithms

Write a recursive function to determine if a binary tree is a binary search tree.

  Determine the mean salary as well as the number of salaries

Determine the mean salary as well as the number of salaries.

  Currency conversion development

Currency Conversion Development

  Cloud computing assignment

WSDL service that receives a request for a stock market quote and returns the quote

  Design a gui and implement tic tac toe game in java

Design a GUI and implement Tic Tac Toe game in java

  Recursive implementation of euclids algorithm

Write a recursive implementation of Euclid's algorithm for finding the greatest common divisor (GCD) of two integers

  Data structures for a single algorithm

Data structures for a single algorithm

  Write the selection sort algorithm

Write the selection sort algorithm

  Design of sample and hold amplifiers for 100 msps by using n

The report is divided into four main parts. The introduction about sample, hold amplifier and design, bootstrap switch design followed by simulation results.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd