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.