GC SumProduct Pty Ltd Tornado Chart Example Go to Table of Contents Primary Developer: Liam Bastick General Cover Notes: How to build a ranked sensitivity chart (often known as a tornado chart). "Any queries, please e-mail:" liam.bastick@sumproduct.com Website: www.sumproduct.com For past articles visit: www.sumproduct.com ##### Sheet/List 2 ##### C Table of Contents Tornado Chart Example Go to Cover Sheet é Section & Sheet Titles Page 1. Tornado Chart Example 3 a. Tornado Chart 4 b. Simple P&L Example 5 Total Pages: 5 ##### Sheet/List 3 ##### SC Tornado Chart Example Section 1. Tornado Chart Example Go to Table of Contents ç è Section Cover Notes: Walk-through example. ##### Sheet/List 4 ##### BA Tornado Chart Tornado Chart Example Go to Table of Contents é ç è Tornado Chart Sensitivity Data Units Produced - Proportion of Product A - Price of Product A - Price of Product B - "Gross Margin, Product A" - "Gross Margin, Product B" - Operating Expenditure - Tax - Data Table Raw Data Activate? (10.0%) - 10.0% Units Produced " $317,909 " " $454,344 " " $590,778 " (10.0%) - 10.0% Proportion of Product A " $438,397 " " $454,344 " " $470,291 " (10.0%) - 10.0% Price of Product A " $348,031 " " $454,344 " " $560,656 " (10.0%) - 10.0% Price of Product B " $424,222 " " $454,344 " " $484,466 " (10.0%) - 10.0% "Gross Margin, Product A" " $348,031 " " $454,344 " " $560,656 " (10.0%) - 10.0% "Gross Margin, Product B" " $424,222 " " $454,344 " " $484,466 " (10.0%) - 10.0% Operating Expenditure " $545,344 " " $454,344 " " $363,344 " (10.0%) - 10.0% Tax " $473,816 " " $454,344 " " $434,872 " Cleaned Data (10.0%) - 10.0% Spread Adj Spread Rank 1 Units Produced "($136,434)" - " $136,434 " " $272,869 " " $272,869 " 1 2 Proportion of Product A "($15,947)" - " $15,947 " " $31,894 " " $31,894 " 8 3 Price of Product A "($106,313)" - " $106,313 " " $212,625 " " $212,625 " 2 4 Price of Product B "($30,122)" - " $30,122 " " $60,244 " " $60,244 " 5 5 "Gross Margin, Product A" "($106,313)" - " $106,313 " " $212,625 " " $212,625 " 3 6 "Gross Margin, Product B" "($30,122)" - " $30,122 " " $60,244 " " $60,244 " 6 7 Operating Expenditure " $91,000 " - "($91,000)" " $182,000 " " $182,000 " 4 8 Tax " $19,472 " - "($19,472)" " $38,944 " " $38,944 " 7 Ranked data (10.0%) - 10.0% 1 Units Produced "($136,434)" - " $136,434 " 2 Price of Product A "($106,313)" - " $106,313 " 3 "Gross Margin, Product A" "($106,313)" - " $106,313 " 4 Operating Expenditure " $91,000 " - "($91,000)" 5 Price of Product B "($30,122)" - " $30,122 " 6 "Gross Margin, Product B" "($30,122)" - " $30,122 " 7 Tax " $19,472 " - "($19,472)" 8 Proportion of Product A "($15,947)" - " $15,947 " Tornado Chart ##### Sheet/List 5 ##### BA Simple P&L Example Tornado Chart Example Go to Table of Contents é ç Simple P&L Example Assumptions Input Sensitivity Used Units Produced " 45,000 " - " 45,000 " Proportion of Product A 75.0% - 75.0% Proportion of Product B 25.0% Price of Product A $100.00 - $100.00 Price of Product B $75.00 - $75.00 "Gross Margin, Product A" 45.0% - 45.0% "Gross Margin, Product B" 51.0% - 51.0% Operating Expenditure " $1,300,000 " - " $1,300,000 " Tax 30.0% - 30.0% Outputs Revenue Product A Product B Total Units " 33,750 " " 11,250 " " 45,000 " Price $100.00 $75.00 $93.75 Revenue " $3,375,000 " " $843,750 " " $4,218,750 " Cost Of Goods Sold "($1,856,250)" "($413,438)" "($2,269,688)" Gross Margin " $1,518,750 " " $430,313 " " $1,949,063 " Operating Expenditure "($1,300,000)" Net Profit Before Tax " $649,063 " Tax Expense "($194,719)" Net Profit After Tax " $454,344 "