Data exercise using an Excel spreadsheet

data exercise using an Excel spreadsheet to manipulate & tell a story using data.

In the Supply Chain/Logistics world, we actively use Excel to provide us (and our bosses) with information.

In this situation, you’ve been given this raw data and need to analyze and format the information by using various Excel functions.

So, download this spreadsheet, analyze the data and send the final Excel worksheet to me.

Please do the following analysis and formatting:

1. Add the $ sign to the Unit Cost column;

2. Determine the Total Sales for each item (multiply Units Sold times Unit Price);

3. Highlight in bold the Column Headings, center the Headings and then place a solid Border around each data cells (forming a grid);

4. Add filters to the data and sort the data by ITEM from A-Z;

5. Calculate the Sum Total of the Total Column (create a total at the bottom of the column);

6. Bold this Sum Total in bright yellow highlight;

7. Subtotal each of the items (Binders, Desk, Pens, Pen Sets & Pencils);

8. Using the Item Subtotals and the Sum Total, calculate the percent % that each Item Subtotal represents of the Sum Total.

9. Create either a visual chart – either a Bar Chart or a Pie Chart with the Item and the % Percent of Sales.

OrderDate Region Rep Item Units Sold Unit Cost Total Sales
1/6/2010 East Jones Pencil 95 1.99  $              –
1/23/2010 Central Kivell Binder 50 19.99  $              –
2/9/2010 Central Jardine Pencil 36 4.99  $              –
2/26/2010 Central Gill Pen 27 19.99  $              –
3/15/2010 West Sorvino Pencil 56 2.99  $              –
4/1/2010 East Jones Binder 60 4.99  $              –
4/18/2010 Central Andrews Pencil 75 1.99  $              –
5/5/2010 Central Jardine Pencil 90 4.99  $              –
5/22/2010 West Thompson Pencil 32 1.99  $              –
6/8/2010 East Jones Binder 60 8.99  $              –
6/25/2010 Central Morgan Pencil 90 4.99  $              –
7/12/2010 East Howard Binder 29 1.99  $              –
7/29/2010 East Parent Binder 81 19.99  $              –
8/15/2010 East Jones Pencil 35 4.99  $              –
9/1/2010 Central Smith Desk 2 125  $              –
9/18/2010 East Jones Pen Set 16 15.99  $              –
10/5/2010 Central Morgan Binder 28 8.99  $              –
10/22/2010 East Jones Pen 64 8.99  $              –
11/8/2010 East Parent Pen 15 19.99  $              –
11/25/2010 Central Kivell Pen Set 96 4.99  $              –
12/12/2010 Central Smith Pencil 67 1.29  $              –
12/29/2010 East Parent Pen Set 74 15.99  $              –
1/15/2011 Central Gill Binder 46 8.99  $              –
2/1/2011 Central Smith Binder 87 15  $              –
2/18/2011 East Jones Binder 4 4.99  $              –
3/7/2011 West Sorvino Binder 7 19.99  $              –
3/24/2011 Central Jardine Pen Set 50 4.99  $              –
4/10/2011 Central Andrews Pencil 66 1.99  $              –
4/27/2011 East Howard Pen 96 4.99  $              –
5/14/2011 Central Gill Pencil 53 1.29  $              –
5/31/2011 Central Gill Binder 80 8.99  $              –
6/17/2011 Central Kivell Desk 5 125  $              –
7/4/2011 East Jones Pen Set 62 4.99  $              –
7/21/2011 Central Morgan Pen Set 55 12.49  $              –
8/7/2011 Central Kivell Pen Set 42 23.95  $              –
8/24/2011 West Sorvino Desk 3 275  $              –
9/10/2011 Central Gill Pencil 7 1.29  $              –
9/27/2011 West Sorvino Pen 76 1.99  $              –
10/14/2011 West Thompson Binder 57 19.99  $              –
10/31/2011 Central Andrews Pencil 14 1.29  $              –
11/17/2011 Central Jardine Binder 11 4.99  $              –
12/4/2011 Central Jardine Binder 94 19.99  $              –
12/21/2011 Central Andrews Binder 28 4.99  $              –

Last Updated on February 11, 2019 by Essay Pro