Information systems and technologies in management, 2023

You need to load all necessary data to the provided Excel file, create connections between tables, calculate the price of the order according to the steps below, and build a report consisting of 2 pivot tables and 2 pivot charts as required in your variant.

Variant numbers are available in the Variants.xlsx file on the assignment’s page in Teams.

There are several assumptions about the business:

1.   Each order contains several items of the exact same product.

2.   All the options of metal blanks from the warehouse list  are always available in unlimited quantities.

3.   One blank is used for only one unit of a product.

4. Metal blanks differ in material (stainless steel, tool steel, bronze), diameter (30 mm, 60 mm), and length (300 mm, 600 mm). You know that a blank that has the minimum dimensions sufficient to make the requested part is chosen every time. For example, to manufacture a 205 mm long shaft, a 300 mm long blank is always selected, not a 600 mm one. The same logic applies to the maximum diameter of the part. So, you need to find out for each order, which blanks were used to know what the cost of them was.

5.   There is  an average basic labor cost per unit of any product: $20. It is multiplied by coefficients depending on complexity (depends on the material used) and urgency (depends on the deadline). See the CONSTANTS worksheet for the details.

6.   Price of the order   = (Cost of one blank  +  Cost of labor per unit  ×  Coefficient(s)) × Quantity of parts in the order

The recommended workflow in Excel

1.   Open the ISTM_assignment_ORDERS.xlsx and look through the available data.

2.   Load the external datasets to corresponding worksheets using Data → Get Data → From Web. Do not add the queries to the data model yet!

3.   For each dataset, check if any data format conversion is needed. Apply local settings to currency values if they are loaded as text (in the Power Query editor).

4.   As described in the practical lessons, create a helper column in the warehouse dataset query to create a unique BlankType, based on its material, diameter, and length. Do this in the Power Query editor, with a custom column formula or, even easier, by duplicating columns, extracting first symbols, and merging columns via the toolbar). In the simplest form, just concatenate values from 3 columns in any order. You need something like, for example, B30060 or Bronze60030 in this column.

5.   Based on the logic described above (item 4 in the assumptions), add a column with analogous BlankType for each order in the orders list using formula, so it corresponds the existing values in the warehouse records. Use the IF, LEFT functions, and concatenation with “&”. See the practical lesson recordings for a similar example.  Alternatively, do this step after the next one, but in the PowerPivot window. Formulas are the same.

6.   Using the Power Pivot toolbar, add all three tables to the data model. Do not add the constants table, they can be hardcoded into formulas. Alternatively, use Power Pivot Measures for each constant value.

7.   Create relationships between the three tables in the Power Pivot Data model using corresponding pairs of columns BlankType, ClientID.

8.   Calculate the total price for each order using the mentioned formula. Do it in the new column of the orders table in the Power Pivot windows, not on the worksheet. You may need separate helper  columns  for coefficients  (use  IF),  labor  cost,  blank  cost,  etc. Remember  to  use RELATED function when referencing the values from another connected data model table in Power Pivot (for example, when getting blank cost from the warehouse inventory list).

9.   Summarize the orderstable using 4 pivot tables as requested in the variant task. Place them onto the report template worksheet.  Create pivot  charts  too,  in  2  cases.  Do  not  forget  to  filter everything by the required month. Use appropriate chart types.

The recommended workflow for the assignment in Power BI is the same, but create a       Power       BI       document       first,       use        this       link       for       the       Orders        dataset https://api.onedrive.com/v1.0/shares/s!Ar0j47vnPkm3htFKtkVPe1NaOAXNxw/root/content , and then continue with adding the other datasets as described. All Power Query manipulations are the same. All the helper columns can be created in the table view similar to the Power Pivot.

To use constants (labor cost, coefficients), use Measures.  Instead of formulas, just make them equal to a number.

Challenging task for bonus points. Not obligatory.

Try to create a timeline-style (like a Gantt chart) visualization of the orders (using start and deadline) for your variant’s month in Excel, on a separate worksheet. Be creative! There are many completely different ways to do this.