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.

OrderDateRegionRepItemUnits SoldUnit CostTotal Sales
1/6/2010EastJonesPencil951.99 $              –
1/23/2010CentralKivellBinder5019.99 $              –
2/9/2010CentralJardinePencil364.99 $              –
2/26/2010CentralGillPen2719.99 $              –
3/15/2010WestSorvinoPencil562.99 $              –
4/1/2010EastJonesBinder604.99 $              –
4/18/2010CentralAndrewsPencil751.99 $              –
5/5/2010CentralJardinePencil904.99 $              –
5/22/2010WestThompsonPencil321.99 $              –
6/8/2010EastJonesBinder608.99 $              –
6/25/2010CentralMorganPencil904.99 $              –
7/12/2010EastHowardBinder291.99 $              –
7/29/2010EastParentBinder8119.99 $              –
8/15/2010EastJonesPencil354.99 $              –
9/1/2010CentralSmithDesk2125 $              –
9/18/2010EastJonesPen Set1615.99 $              –
10/5/2010CentralMorganBinder288.99 $              –
10/22/2010EastJonesPen648.99 $              –
11/8/2010EastParentPen1519.99 $              –
11/25/2010CentralKivellPen Set964.99 $              –
12/12/2010CentralSmithPencil671.29 $              –
12/29/2010EastParentPen Set7415.99 $              –
1/15/2011CentralGillBinder468.99 $              –
2/1/2011CentralSmithBinder8715 $              –
2/18/2011EastJonesBinder44.99 $              –
3/7/2011WestSorvinoBinder719.99 $              –
3/24/2011CentralJardinePen Set504.99 $              –
4/10/2011CentralAndrewsPencil661.99 $              –
4/27/2011EastHowardPen964.99 $              –
5/14/2011CentralGillPencil531.29 $              –
5/31/2011CentralGillBinder808.99 $              –
6/17/2011CentralKivellDesk5125 $              –
7/4/2011EastJonesPen Set624.99 $              –
7/21/2011CentralMorganPen Set5512.49 $              –
8/7/2011CentralKivellPen Set4223.95 $              –
8/24/2011WestSorvinoDesk3275 $              –
9/10/2011CentralGillPencil71.29 $              –
9/27/2011WestSorvinoPen761.99 $              –
10/14/2011WestThompsonBinder5719.99 $              –
10/31/2011CentralAndrewsPencil141.29 $              –
11/17/2011CentralJardineBinder114.99 $              –
12/4/2011CentralJardineBinder9419.99 $              –
12/21/2011CentralAndrewsBinder284.99 $              –

Last Updated on February 11, 2019