Expert answer:Need computer science help with an Excel project
Answer & Explanation:Please check the attached word file for instructions. (long but easy)Create a PivotTable and navigate.docx use this file SalesdataPivotV01 (1).xlsx
create_a_pivottable_and_navigate.docx
salesdatapivotv01__1_.xlsx
Unformatted Attachment Preview
Create a PivotTable and navigate
Open the Excel file
Open Excel and the file SalesdataPivotV01.xlsx.
Quickly inspect the contents of the SalesdataPivotV01 worksheet, and note that there is a large
number of records. Also be very clear in your mind with regards to how the transaction values have
been recorded – e.g., have they been recorded in one currency, or multiple currencies?
Create a Pivot Table
Start with a high level overview and create a pivot table, which shows the revenue in Germany and
the US throughout the years.
Open the Insert tab (1) and choose insert PivotTable (2).
Accept the system defaults for the range (1) and New Worksheet (2).
Drag and Drop the fields YEAR, Country and Revenue in USD from the field list (1) to the report
areas (2) and watch the result (3).
Use the context menu on a data cell to adjust Value Field Settings.
Choose Number Format (1) Currency (2) $ English (U.S.) with 0 Decimal Places (3).
Your PivotTable should now look like:
You might want to save the intermediate result.
Rotate
Rotate the view by swapping the axes.
GOOD TO KNOW
Rotating axes means that what were row attributes, become column attributes, or vice
versa.
Change Country and YEAR.
You can observe a different behavior of two countries: whereas Germany shows a continuous
increase in revenue, there is a sharp decline in the US in 2009. Try now to identify reasons for this
behaviour in the data! Do this by slicing the data (see the next step).
Your PivotTable should now look like:
Slice
We analyze the data from Germany first and, therefore, do a slice on country.
GOOD TO KNOW
Slicing data means to reduce the focus of analysis to a smaller sub-set of the data, by applying
filters. For example, adding a filter on country (e.g., country = Australia) will reduce the focus of
analysis to only the sub-set of transactions relating to Australia. An extended version of slicing
is
dicing: applying a filter in more than one dimension to create a “smaller” cube (subset).
Switch the Key figure to Revenue in local currency. Exchange Revenue USD (1) with Revenue (2)
and format appropriately.
NOTE
European Currency Formatting
After exchanging in the PivotTable field list revenue in USD (Revenue USD) for revenue in local
currency (Revenue), the German values may not formatted according to the above image (i.e.,
the Euro symbol is not showing, as well as the periods). Can this be fixed? To change the
formatting to Euro currency, there are two options (both of which, however, will not result in the
exact format, as shown in the above image).
Option 1
There is an Excel add-in (which, by default, is disabled) that allows one to convert foreign
currencies, as well as change a number format to European format (add-in menu shown below).
When the add-in is enabled, on the Data ribbon, you will see the following:
Once the add-in is enabled, you can use the following Accounting format option to put the Euro
symbol in front of the Revenue amounts (but the periods are not converted to commas).
See below note with regards using the value field setting to apply it to the entire field contents
(note: when displaying US amounts in the revenue field, they will also be formatted with the Euro
symbol).
Option 2
If the add-in is unavailable (e.g., in CEIT labs you may not be able to enable it), you can create a
custom number format, as shown below (the Euro symbol can be obtained by using the Symbols
feature in MS Word – or press [Alt] and, while holding it, type 0128) (or copy-n-paste actual
character from this page).
Again, both these options do not completely fix the problem, as it fails to covert the use of
commas and periods – but this method does give you the Euro symbol, which may be a little less
annoying. Just for a little more about different currency formats (we are not talking exchange
rates), nuances are summarized below:
Filter out non-German data.
Your PivotTable should now look like:
Drill down to Sales Organization and Customer
Now drill down to the sales organization. There is no dependency visible: both sales organizations
behave similarly.
GOOD TO KNOW
Drilling down means to take some summary figure and unpack it
– i.e., reveal in more
detail the components making up that value. There may be several layers one can drill down to, and
the layers are usually described in what is called a ‘hierarchy.’ An example of a hierarchy is: All
regions->specific region->state->county->city->store.
Move Country to the Report Filter (1) and Salesorg to the Row Labels (2).
Add CustomerDescr to the Row Labels.
Make sure the Classic PivotTable layout is NOT selected (2).
From sales organization, drill down to customer. Everything looks fine!
Rotation
Check the dependency on the product category and product description (ProductDescr) by
rotating the cube.
Note: keep the country filter as ‘DE’
GOOD TO KNOW
Full Rotation (Pivoting): While the axes of the display grid can be swapped (as
mentioned earlier), sometimes an analyst may completely swap-out one or more of the dimensions
of the display grid: in effect, the whole cube is being pivoted/rotated, as illustrated in the below
image.
We observe the introduction of a new product in 2010: the new E-bike Tailwind. Now analyze the new
market in more detail!
Adjust Row Labels (1). Expand and Collapse as needed (2).
Your PivotTable should now look like:
NOTE
The above image shows decimal places (but in European format). If your PivotTable values does
not show decimal places (i.e., is not formatted to your liking), do not be concerned, as it is
relatively easy to modify the formats of the ‘Values’ cells. But appreciate thatit’s not a good idea
to format PivotTable cells like you normally format cells (i.e., going through the font group on
the Home toolbar), as sometimes rows of data may not be showing at the time of applying a
format, due to the possibility that specific filters are in place that may hide certain records. So, to
work around this possible complication, it’s best to format cells within the PivotTable using
the Value Field Settings.. [Number Format], option.
You might want to save the intermediate result.
SUBMISSION NOTE
At this point, take a screen-shot of your Excel worksheet, showing both the PivotTable
and PivotTable Field List Place this image on the appropriately labeled slide within the proforma
PowerPoint slide-deck you downloaded earlier. You will be instructed to add more images to this
slide deck as we move forward. Place the image on the slide titled: [Part 3A] Step 1.6.
Note: if the field list is now showing, click inside the PivotTable, or show the field list using the ribbon
button.
Reminder: Any slide without the Windows operating system date/time clearly displayed indicating
when exactly the screenshot was taken, will be skipped during grading.
Show percentage values
Compare the new E-bike with other bikes.
Remove Product Category (1). Filter out all but the last two years data on all bikes (3). Add a
Report Filter for Division (2) – excluding Division ‘AS’.
Instead of showing the revenue in absolute numbers, show values as % of Column Total. Sort the
data by revenue. We recognize that the new product already contributes more than 5% to the total
revenue!
Show values as % of Column Total (1) and sort (2) descending.
Your PivotTable should now look like:
NOTE
Do not be concerned if your worksheet is displaying percentages using American notation for
decimal places.
Drill-through
To finish the analysis of Germany, find out when the new E-bike was sold for the first time. For this
have a look at all order items sorted by date.
Double-click on the E-Bike 2010 value. A new worksheet is created.
On the Data tab (1) call Sort (2) and use Add Level (3) to sort by MONTH and DAY.
You might want to save the intermediate result.
SUBMISSION NOTE
At this point, take a screen-shot of part of your Excel Table (which will be in a different worksheet to
the one containing the PivotTable) showing the details of the Year/Month/Day the E-bike was first
sold. Place this image on the appropriately labeled slide within the proforma PowerPoint slide-deck
you downloaded earlier. You will be instructed to add more images to this slide deck as we move
forward. Place the image on the slide titled: [Part 3A] Step 1.8.
Analyzing the US Data
In the following, we analyze the situation in the US.
Remember that we observed a sharp revenue decline between 2008 and 2009 and we want to
find out reasons for this. Therefore change country to US (1) and remove all other filters. No longer
display revenue as % of Column Total. Since revenue is in local currency, change the format to $
(no decimal places).
In order to obtain Customer and City in two separate columns (2) you have to switch to the Classic
PivotTable layout (cf. PivotTable Options) before adding CustomerDescr and CITY to Rows. We
observe that a very important customer is lost between 2010 and 2011 (3).
To switch to the Classic PivotTable layout call PivotTable Options…
… and use the Display tab (1) to select the required option (2).
All other adjustments can be done as before. By default, subtotals are shown, which can be changed
in context menu (i.e., remove subtotals).
Your PivotTable should now look like:
SUBMISSION NOTE
At this point, take a screen-shot of part of your PivotTable worksheet. Place this image on the
appropriately labeled slide within the proforma PowerPoint slide-deck you downloaded earlier. You
will be instructed to add more images to this slide deck as we move forward. Place the image on the
slide titled: [Part 3A] Step 1.9.
Insert a Chart
We want to explore this in more detail and use a chart to visualize the data. Filter Silicon Valley
Bikes and Beantown Bikes Boston and the years 2009 to 2011 (dice operator). Drill down to
calendar month and insert a line chart.
We observe that revenue of Silicon Valley Bikes is zero from August 2010 on. We have to ask the
responsible sales person to explain this.
All navigation steps can be done as before.
NOTE
To complete this step you will need to first ‘reconstruct’ this view of data based on what you see
(e.g., take note of the shown PivotTable Field List values). It’s a little challenge, but everything is
there you need. However, the numbering formatting is a little exotic and the Instructor resolved
this with a custom format of: $#,###,###
If the MONTH field is not nested in Column A, under the YEAR field, you can change the layout
to be the same as the below image by adjusting the field setting (Layout & Print) of both the
YEAR & MONTH fields, within the PivotTable Field List, as shown below (see: Show item labels
in outline form):
A chart can be added using the Insert tab.
We observe that revenue of Silicon Valley Bikes is zero from August 2010 on. We have to ask the
responsible sales person what was going on here.
SUBMISSION NOTE
At this point, take a screen-shot of your line chart but with some of background cells showing (just to
ensure your image will be a little different from the one above, thereby ensuring this image cannot be
simply cut-n-pasted). Place this image on the appropriately labeled slide within the proforma
PowerPoint slide-deck you downloaded earlier. You will be instructed to add more images to this
slide deck as we move forward. Place the image on the slide titled: [Part 3A] Step 1.10.
Conditional Formatting
In order to detect other effects we remove the filter on the calendar year, exclude this customer
(Silicon Valley Bikes) from the subsequent steps….
Move YEAR to columns, and remove CustomerDescr
Change presentation of numbers by right-clicking on a data cell and selecting Show values as %
Difference from
… and look at the monthly values compared to the previous year. Choose YEAR as Base Field.
If necessary accept the system message.
Mark all the data cells (1) and select Conditional Formatting (2) with Green – Yellow – Red Color
Scale (3).
I
This shows a decline in September 2008 (Lehman crisis) which is partially recovered in the following
years.
SUBMISSION NOTE
At this point, take a screen-shot of your PivotTable with the conditional formatting showing (like the
immediately above image). Place this image on the appropriately labeled slide within the proforma
PowerPoint slide-deck you downloaded earlier. You will be instructed to add more images to this
slide deck as we move forward. Place the image on the slide titled: [Part 3A] Step 1.11.
CALCULATED KEY FIGURES
So far Robert restricted his investigation to the key figure revenue and found three different incidents
which influenced our revenue, being a new innovation in Germany (E-Bike) and Lehman crisis and
loss of the biggest customer in the US.
However the original data includes discount and cost of goods manufactured as well. So Robert
extends his analysis on net salesand contribution margin.
Reminder: ‘Key Figures’ is SAP-speak to refer to those fields within transactions (recorded business
events) that contain the value measurement of the transaction – e.g., a key figure in a sales
transaction would be sales amount; another would be sales quantity.
NOTE
The key figures can be calculated as follows
Net sales = Revenue USD – Discount
Contribution Margin = Net Sales – Cost of Goods Manufactured
Contribution Margin Ratio = Contribution Margin / Revenue
TASK
Now techniques for calculating new key figures based on existing key figures will be shown.
Before tacking the next task, remove the conditional formatting and clear all PivotTable field List
parameters.
Calculate Contribution Margin
Explore which product made in 2011 the most Contribution Margin in USD…
Calculated Fields can be added as Pivot Table Options.
Insert Name first (1). When defining the Formula (2) you might want to use the Insert Field option
(3). Make sure you use all key figures in USD.
When defining the Contribution Margin USD (1) make sure you use the costs in USD (2).
Sorting can be done as described before.
GOOD TO KNOW
If you made a mistake in defining your calculated field formula, you can modify an existing calculated
field by selecting it from the list and choosing Modify.
Your PivotTable should now look like:
NOTES
If your values above are smaller than shown, check to ensure you have removed the filter
on Country.
To order your results, click a cell within the column of values and chose the sort menu option:
As yet, you have not created the Contribution Margin Ratio calculated field – do that now. The
formula is: Contribution Margin Ratio = Contribution Margin USD / Revenue USD.
Calculate Contribution Margin Ratio
This also can be done as before.
… and the highest Contribution Margin Ratio
SUBMISSION NOTE
At this point, take a screen-shot of your PivotTable with the immediately above image showing (With
the Contribution Margin columns). Place this image on the appropriately labeled slide within the
proforma PowerPoint slide-deck you downloaded earlier. You will be instructed to add more images
to this slide deck as we move forward. Place the image on the slide titled: [Part 3A] Step 1.13.
Develop your Skills
TASK
Now it is time you practice some of the skills we have just covered.
NOTES
Before attempting this last set of exercises, please read the last submission note below.
GRADING
The questions in this section are double-weighted in terms of points.
Flops
Which product sold the least number (in terms of overall quantity) of units in 2011?
Top Seller
Which product category provided the most revenue in 2011?
NOTES
Do not confuse the fields: Product Category and Product Description.
Ensure to base your analysis on US currency amounts (if unclear, please closely inspect your
data).
Sales by Product Category
[a] For 2011, what percentage did sales of the product category: ‘off-road bikes’ contribute to the
overall bicycle sales quantity? [Hint: please change how the values are shown as]
[b] For 2011, in which 3 cities was this percentage significantly above the average? (see below
note on conditional formatting)
NOTES
Do not confuse the fields: Product Category and Product Description.
To highlight in [b] those values that are above the average, create a conditional formatting rule
that highlights all values above the average. See below images for an example (however,
resulting highlighted cells have not been shown):
Resulting rule:
Seasonal Behavior
One would normally expect that bicycles and accessories would more likely be purchased during
spring and summer, rather than during fall and winter time. To investigate whether this hypothesis
holds for all products, identify any products exhibiting a lack of seasonality. Support your findings
(evidence of a lack of seasonality) by using an appropriate chart to present the temporal data.
NOTE
So, the seasonality question addresses whether there is a lack of seasonality (the observation),
when one would ‘normally’ expect to see it (the expectation).
SUBMISSION NOTE
Once you have an ‘answer’ to each of the above questions (2.1-2.4) take a screen-shot of your Excel
worksheet showing both the PivotTable and PivotTable Field List.
In addition, highlight the answer to the question by placing a yellow fill color to the appropriate cells.
‘Develop your skills’ questions are worth double points.
Place each image on the appropriately labeled slide within the proforma PowerPoint slide-deck you
downloaded earlier. You will be instructed to add more images to this slide deck as we move
forward. Place the image/s on the slide/s titled: [Part 3A] Step 2.1. (or as is applicable).
YEAR
MONTH
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
DAY
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
Customer
5000
5000
5000
5000
5000
5000
5000
5000
5000
5000
5000
5000
15000
15000
15000
15000
15000
15000
15000
15000
15000
15000
15000
15000
15000
15000
15000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
16000
CustomerDescrCity
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Beantown Bikes
Boston
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Bavaria Bikes München
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Capital Bikes Berlin
Salesorg
UE00
UE00
UE00
UE00
UE00
UE00
UE00
UE00
UE00
UE00
UE00
UE00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DS00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
DN00
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
2007
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2 …
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades