Problem: The computer-consulting firm you and a friend started recently on a part-time basis has received its first contract. The client has specified in the contract, that you are to build a monthly sales analysis worksheet that is capable of monitoring the sales performance of each sales person on their staff. Sales performance includes keeping track of each sales person's sales but also keeping in mind that some customers return items so net sales, (sales less returns) are very important. The client also wants you to graphically show the relationship between net sales and individual net sales. All salespeople have a minimum quota of $9,500. There are four sales people. You must determine their first and last names. Give your company a Name.
Sales person #1 had sales of $19,789 with returns of $1,345.00 and a number of items sold of 9674. Sales person #2 had sales of $11,550.00 with returns of $500 and a number of items sold of 6441. Sales person #3 had sales of $10,295 with returns of $1,276 and a number of items sold of 3214 and Sales person #4 had sales of $10,955 with returns of $650 and for the number of items sold, 4000. Each salesperson had a sales quota of $9,500.00. Use the concepts and techniques presented in this class to create a worksheet and charts.
Part I Instructions:
1. Use the "Select All" button and the "bold" buttons to bold the entire worksheet.
2. Design and create your own Company name and worksheet title
3. Enter column titles (Sales Person, Sales, Returns, Number of Items Sold, Net Sales, Sales Quota and Above Quota) and the row titles below that (Sales people names).
4. Enter the sales and sales returns in the columns.
Part II Instructions
1. Increase the widths of each column so all content in appropriately displayed.
2. Obtain the Net Sales of each sales person in the appropriate column of the worksheet by subtracting the sales returns from the sales amount.
3. Obtain the above quota amounts by subtracting the sales quota from the net sales.
4. Enter the row title Total and find the total of each column except the Number of items sold, and Sales Quota. Apply the Total Cell Style.
5. Below Total row enter the row titles:
1. Average of Sales
2. Max,
3. Min
4. Use the appropriate functions to find the values of each column. (Do not find the values for the Number of Items Sold, the SalesQuota, and Above Quota column).
6. Change the worksheet title font (company name and sub-title)to an appropriate font type and size
7. Italicize the column titles and center in bold.
8. Apply a cell style of your choice to the ranges that contain the title of the worksheet and the subtitle.
9. Change the text color of the Worksheet Title (company name and sub-title), to a dark color of your choice. Rotate the column titles at a 45 degree angle and wrap text.
10. Merge and center the Company name and worksheet title that you created in Row one.
11. Apply a heading cell style to the range that contains the column headings.
12. Increase the height of row 1 to 42.00 points and increase the heights of rows 2 and 8 to 24.00 points
13. Use the buttons on the Formatting toolbar to apply the Comma Style with two decimal places in the ranges that contain numeric values.
14. Apply the Comma style with no decimals in the ranges that contains the Number of Items Sold.
15. Select the ranges that have the totals of each column and assign a floating dollar sign.
16. Apply any Cell Style to the range that contains the range A4:G7 and the total cell style to the total row.
17. Using conditional formatting, apply a color pattern of your choice to all sales people who did not meet their sales quotas, in the appropriate Net Sales column.
19. Apply the currency style format with a fixed dollar sign to the range that contains the Net Sales for each employee.
20. Enter Professor's name below the entries in separate cells after your last entry.
21. Add a footer that includes your Name on the left, the page number on the center, and the date and time on the right; make sure that the date is updated automatically.
22. Save the workbook using the filename Excel Exam Your Name.
Part III Instructions:
1. Select the ranges that contain the salesperson's names and their net sales. Use the Chart command on the Insert menu to create a chart on a new sheet.
2. Draw a 3-D Column chart (clustered column with 3-D effect).
3. Do the following to the chart:
(a) The data series for this chart is in columns.
(b) There is no legend on the chart.
(c) The chart does have a title of Net Sales Your Name
(d) Change the color of the chart walls to a light color pattern of your choice and change the color of the columns to a dark color pattern.
(e) Change the font size of the chart title to 24 points and the color of the text to the same color as the columns.
(f) Change the font of the data labels to 12 point, bold, same color as title.
Part IV Instructions:
Following the above conditions, create a 3-D Pie chart with 3-D visual effect on a separate sheet using the same ranges you used for the 3-D Column Chart, with the same title.
Do not change the colors of the pie, but create it without a legend and with leader lines, labels and percents. Show the leader lines and change the font in the title to Garamond Bold 24 dark color pattern, and each label to Garamond 12, bold, the same color as title.