Introduction
This week, I wanted to challenge myself to do something I didn’t know how to do in Sigma, I wanted to start laying the groundwork to do sensitivity analysis.
I’ve often thought this would be possible in Sigma, since we can connect to Snowflake User-Defined Functions (UDFs) via Sigma’s function bar to call models and pass in our inputs. While we’re not going all the way there today, I wanted to do a really simple analysis of how much our total profit is impacted by a single factor: Cost of a good.
This Workout Wednesday challenge will leverage input tables and sample Food Truck data, so let’s dig in!
Need access to Sigma?
Note: You will only have view access to WOW Workbooks, Folders, and Workspaces, not edit access. Please create your WOW Workbooks under “My Documents.” We suggest creating a folder to organize all your workbooks.
Requirements
- Get your data source pulled into a workbook
- Data source: Workspaces > Workout Wednesday > 2024 > 2024W39 – Can you test out price changes? > Orders > Orders Actual
- Add this to a ‘Sources’ tab
- Create a group in this table for ‘Truck Brand Name’
- Bring the ‘Profit’ field into the calculations for this group, rename it as ‘Actual Sum of Profit’
- Create an Input table in your ‘Sources’ tab called Adjustment Percentages
- Add a new numeric column (or update the default text column). Call it ‘Multiplier’, each row should have an integer between -3 and 3, stepping up 1 each time. (-3, -2, -1, 0, 1, 2, 3)
- Create a Segmented Control called ‘Adjustment Percent’,
- it should be numeric
- the values should be numeric percents, while the ‘Display As’ value should be a human-readable representation of the percent (0.005 -> .05% | 0.01 -> 1%, etc.)
- Change the Control Id to ‘Adjustment-Percent’
- Add a new calculation column called ‘Adjustment’. The logic should be [Adjustment-Percent], you can force it to be a number, if needed, by doing the formula Number([Adjustment-Percent])
- Add another new calculation column called ‘Total Adjustment Percent’, the formula should be [Multiplier] * [Adjustment]. This is the column that we’ll apply to our actual costs to get the new values
- Move the ‘Adjustment-Percent’ control over to a new tab, call the tab ‘Presentation’
- Create a few more controls with the target as the ‘Orders Actual’ table that is in your workbook (Don’t make it the orders actual from the original source!)
- Date
- Region
- Truck Brand Name – REQUIRED
- Create one control called Menu Items to Update
- It should have no target and should be REQUIRED
- This will be used in a formula to adjust *only* the prices for menu items we want to adjust.
- Build the Adjusted Sales table
- Create a new table element by joining the:
- ‘Orders Actual’ table in your Sources tab to the
- ‘Adjustment Percentages’ table in your Sources tab
- The join criteria should be a custom formula on both sides of the join: Set the formulas to ‘1’
- You should see 1 = 1, this will join together every row of your input table with every row of the Orders Actual Table, so be careful if you try this on a large table, as it will expand it drastically.
- In the new table, create a few new calculations:
- Adj Cost – this is the Cost after our adjustments
- [Cost] + If(ArrayContains([Menu-Items-to-Update], [Menu Item Name]), [Cost] * [Total Adjustment Percent], 0)
- This formula is applying the adjustment percent only on the Menu Items that are selected. Note how we check if the current row’s menu item name is in the array produced by the Menu-Items-to-Update control
- Adj Profit
- [Price] – [Adj Cost]
- Adj Cost – this is the Cost after our adjustments
- Create a new group for ‘Total Adjustment Percent’
- Add ‘Adj Profit’ to the calculations, it should be a Sum. Change the name to ‘Adjusted Profit’
- Add a new calculation to get the percent change called ‘Percent Change from Actual’
- ([Actual Sum of Profit] – [Adjusted Profit]) / [Actual Sum of Profit] * -1
- Create a new table element by joining the:
- Bring it all together in a container
- Highlight all of the elements you’ve created and select ‘create container’ in the box that pops up at the top right of the selections.
Dataset
Workspaces > Workout Wednesday > 2024 > 2024W39 – Can you test out price changes? > Orders > Orders Actual
Share
After you finish your workout, share on LinkedIn, Sigma’s Community page, (or Twitter) using the hashtags #WOW2024 and #SigmaComputing, and tag Ashley Bennett, Eric Heidbreder, Katrina Menne, and Michal Shaffer!
Create an interactive, sharable version of your solution here.
Also, make sure to fill out the Submission Tracker so that we can count you as a participant this week to track our participation throughout the year.
Solution
Coming Soon!