Excel Tutorial
Case Study
Introduction to Logistics and Supply Chain Management
Constructor University
I. Case Study
Imagine you are working as a Sales Assistant at Marvel Pick GmbH. Marvel Pick GmbH is a
young high-end Marvel related products manufacturer that processes most orders directly from
the customers online. The central manufacturing plant of Marvel Pick is in Germany.
Your manager has given you all relevant data concerning:
a) Sales Report 2013-14
b) Customer List
c) Production Warehouse Data in an Excel file.
Based on the provided information, you need to format and organize the data in an appropriate
way, summarize the results and prepare a report on the company’s performance.
In order to fulfill your assignment, please complete the following tasks.
Stanislav Chankov – MS Excel: Intermediate Level Training 1|Page
Go to Sales Report:
1. Calculate total sales before tax in the period 2013-14 for all different Marvel models in
production.
2. Calculate total sales after tax in the period 2013-14 for all different Marvel models in
production. Please use the value indicated by Cell U8 for your calculation.
3. Calculate average, minimum and maximum quarter sales (before tax) for all different
Marvel models.
Go to Statistics:
4. Complete Section I. Please use COUNTIF and SUMIF function.
Go to Customer List:
5. Determine which of the customers are “new customers”: yes/no output
!Note: “new customers” are those with contract less than two years
Use conditional formatting to color code cells. For example, those cells that contain
“yes” are marked in red, the others with green
6. Determine which of the authors are “loyal customers”: yes/no output
!Note: “loyal customers” are those with contract greater than five years and who have
sold more than 5500 models.
Use conditional formatting to color code cells. For example, those cells that contain
“yes” are marked in red, the others with green
7. Calculate royalty rates
A: customers with contract >5 years, models sold > 5500, revenue >150000 => 60%
B: customers with contract >2 years, models sold > 4500 => 45%
C: other customers => 30%
Go to Warehouse Data:
8. Calculate the warehousing cost for each item in the production warehouse.
𝑊𝑎𝑟𝑒ℎ𝑜𝑢𝑠𝑖𝑛𝑔 𝐶𝑜𝑠𝑡 = # 𝑃𝑎𝑙𝑙𝑒𝑡𝑠 × 𝐷𝑎𝑦𝑠 𝑖𝑛 𝑠𝑡𝑜𝑟𝑎𝑔𝑒 × 𝐹𝑖𝑥𝑒𝑑 𝑠𝑡𝑜𝑟𝑎𝑔𝑒 𝑟𝑎𝑡𝑒
Stanislav Chankov – MS Excel: Intermediate Level Training 2|Page
Storage rates for each type of item are given below:
Base: 1 euros/pallet/day
Battery Set: 2.25 euros/pallet/day
Decoration: 1.25 euros/pallet/day
Equipment: 0.75 euros/pallet/day
Head Set: 2 euros/pallet/day
Lower Body Set: 1.75 euros/pallet/day
Upper Body Set: 1.5 euros/pallet/day
Go to Statistics:
9. Complete Sales Department Summary
10. Marvel Pick rewards the customers with the most amount of models sold in each area
every year. Help the customer care department to choose the best performers.
11. Develop a “Search Engine” (Section III), where you enter the item ID as an input and
you obtain the number of pallets in storage, days already spent in storage and product
type (e.g. frame) as an output. (Hint: Use VLOOKUP)
12. Complete Section IV (Use information from sheet Sales Report)
13. Draw a line chart showing the sales of the models that you are managing: (1) Captain
America 1 (2) Captain America 2 (3) Captain America 3 in every quarter.
14. Draw a bar chart that shows the total profit earned by Marvel Pick GmbH and its major
competitor for the fiscal period 2000-2012. This information you obtain from the table
under “Statistics/Section IV Visual Representation”
15. Format those two charts. Add Axis Titles, Font, Color and Layouts.
Stanislav Chankov – MS Excel: Intermediate Level Training 3|Page
