Excel Assignment

In this assignment, you will create a spreadsheet for a small home business which is selling three vitamin products:  CoQ-10, Iron, and Zinc.  The owner wants to look at sales by product for the first quarter of the year and calculate monthly profit on the products.  Open a spreadsheet and do the following:

1. Merge cells B1 through D1 and type Sales & Profit in all caps.  Center this in the merged cells. 
2. In cell B2, type CoQ-10.  In cell C2, type Iron.  In cell D2, type Zinc.  Center these in their cells and make them bold.
3. In A3, type Jan.  In A4, type Feb.  In A5, type Mar.  These should all be bold as well.
4. Type in some numbers to represent the sales of each vitamin product for each month.  Each number should be different.  Make sure they all have a dollar sign. You need to do this by formatting the cells as currency with no decimal places.  
5. In A7, type Sales and make it bold and in italics.  
6. Using the SUM function, calculate the total sales for each product.  These results should be in cells B7, C7, and D7.  
7. In A8, type Average Sales in bold and italics.
8. Using the average function, find the average sales for each product.  These results should be in cells B8, C8, and D8.
9. In E2, type Profit in bold and italics and centered in the cell.
10. Each product has a different profit margin.  The CoQ-10 product has a 10% profit margin.  This means if $80 is sold, the profit is 0.10*80 or $8.  The profit margin for Iron is 15%, and the profit margin for Zinc is 5% (which is a multiplication factor of 0.05).  In E3, calculate the total profit for January from the sales of the three products.  You will need to type in the formula rather than use a function (like SUM) to do this.  Remember that to multiply, you must use the asterisk symbol (hold shift while hitting the 8 key).  Do the same to determine the February and March profits.
11. In A9, type Total Profit in bold and italics.   
12. In E9, determine the total profit.  Fill this cell with a different color so that it stands out.  

Save this excel document as doc1.

1. In this part of the assignment you will continue to work with the above scenario. The sales and profits info need to be deplayed with charts.  We want to identify the products which were not creating as much revenue as the others.

1. Use your spreadsheet. Fill in that column E in any way that you want (although no two numbers should be the same or widely different, like 1 and 100).  The important thing is to have some numbers in that column for monthly profits.
2. Look at the range of numbers you have for monthly sales of each product.  These are the numbers in rows 3 to 5 and in columns B to D.  Pick a number somewhere in the middle or slightly lower.  For instance, if I have a range of numbers scattered between 10 and 100, I might choose the number 40.  Using the conditional formatting feature, highlight in red the sales figures which are less than the number you chose (less than 40, for instance).
3. Create a bar graph of the monthly sales based on product.
4. The months should display along with a legend of the three products.    
5. Give the chart a title of Monthly Sales.
6. Give the y-axis a title of Sales in $. 
7. Create a pie chart of the monthly profit.
8. Give the chart a title of Monthly Profit.
9. See if you can get the legend to display the months.  (Hint:  You can select one column, hold the shift key down, and then select another column in order to select two different columns.)
10. Create a line chart of monthly sales.
11. Give the chart a title of Monthly Sales.
12. Give the y-axis a title of Sales in $.
13. The legend and months should display if columns and rows are properly selected.
14. Attached are some examples of charts and what they should look like.

Save this document as doc2.