Building Climate Resiliency through Urban Plans and Design

1. Building Climate Resiliency through Urban Plans and Design HLURB Coaching/Mentoring Workshop 27 April 2019 This Photo by Unknown Author is licensed under CC BY-SA-NC

2. To be mainstreamed in the CLUP and CDP Consistent with LGC (RA 7160), CC Acts (RA 9729 & 10174), DRM Act (RA 10121) CDRA Outputs and Results LGU decision making tool to formulate thematic plans: LCCAP and DRRMP

3. Framework for the Analysis

4. The Exposure Data Base Vulnerability Score Adaptive Capacity Degree, rate, or levels of ‘variables’ that enable the exposed factor cope, adjust, address impacts = Adaptive Capacity Score What it tries to generally ‘uncover’ for every element? Hazard Susceptibility =Likelihood of Occurrence Exposure Exposed ‘factors’ =Exposure Score Sensitivity Degree or rate of ‘variables’ that make exposed factor susceptible to impacts =Sensitivity Score Risk Score = Severity of Consequence x likelihood of Occurrence Severity of consequence = Exposure Score + Vulnerability Score / 2

5. CDRA Exposure Data Base Population Exposure shall indicate the spatial location and number of potentially-affected persons Urban Use Exposure pertain to the built environment currently utilized for residential, commercial, industrial, tourism, sanitary waste management facilities, cemeteries, and other land uses unique to the locality Natural Resource Based Production Area pertain to areas utilized for crop, fisheries, and forest related production Critical Point Facilities facilities providing key socio-economic support services such as schools, hospitals/rural health units, local government buildings, roads, bridges, air/ sea ports, communication towers, and power-related and water related facilities Lifeline Utilities cover the transportation, water distribution, drainage and power distribution networks

6. Assess and Analyze your Data! Processing the Exposure Database

7. Analysis Defined A detailed examination of anything complex in order to understand its nature or to determine its essential features: a thorough study Separation of a whole into its component parts (merriamwebster dictionary)

8. Exposure Data Base Each will be done per Hazard/CC Driver considering the Impact Chain Risk Parameters: • Hazard Information (incl. Probability) • Exposure • Vulnerability (Sensitivity & Adaptive Capacity • Severity of Consequence

9. Processing the CDRA Tables (CDRA STEP 4) Turn the Excel Data into tables Create Pivot Tables Create Pivot Charts (For visualization and Reporting) 1st task 2nd task 3rd task Key tasks Develop Dashboards (for summary and reporting) 4th task

10. 1: Turning your excel data into tables • Organize the headers • Once headers are organized, check the data set to ensure no cells are blank, and that no cells are merged • Create a table: • Select the data range (short cut: put cursor to the first cell of the table, press shift+ctrl+arrowdown then while still pressing shift+ctrl press arrow right) • Press “Ctrl+T” • Click my table has a header • Click ok

11. 2: Creating Pivot Table • After the 1st Task • Put the cursor in any part of the table • Using the “Insert” button, click insert Pivot table • Create the Pivot Tables for analysis using the Pivot Fields • Rows • Columns • Values • Filters • What is a Pivot Table? • Excel data processing tool built within excel to help organize, analyse, and summarize tables and databases • Allows users to apply criteria in processing information for reporting, monitoring, and decision making processes using the data supplied

12. Necessary Pivots Tables (Per Barangay – all hazards): • Risk Summary • Hazard and Exposure Summary • Severity of Consequence Summary • Vulnerability Summary • Sensitivity Summary • Adaptive Capacity Summary Baseline (Current)

13. What we need to find out: Risks and Exposure • What is the Risk Level (of category e.g. barangay, land use, etc)? (Where is the risk Highest?) • What are the rates/levels of risk factors (scores of: Sensitivity, AC, SevCon,LikeliOfOccur) (Where is it highest?) • Who and What is exposed; % of “subject” (hectares or m2,No, type, etc. City level and Barangay Level) Vulnerability Sensitivity • Why are they negatively affected? Show each factor assessed (per relevant category) Adaptive Capacity • What ACs are there? Show summary of each (per relevant category)

14. Summarize Findings CDRA STEP 5

15. Sample from CDRA Guidebook

16. Sample from CDRA Guidebook

17. Summarizing for Actions/Interventions

18. 3: Create Pivot Charts • After the 3rd Task… • Put the cursor in any part of the Pivot table • Click the “Analyze” button, click insert PivotChart • Choose appropriate chart (infographic) for the data being presented

19. Excel Chart Types • Line Chart: The line chart is one of the most frequently used chart types, typically used toshow trends over a period of time. If you need to chart changes over time, consider using a line chart. • Column Chart: Column charts are typically used to compare several items in a specific range of values. Column charts are ideal if you need to compare a single category of data between individual sub-items, such as, for example, when comparing revenue between regions. Source: https://www.dummies.com/software/microsoft-office/excel/10-excel-chart-types-and-when-to-use-them/

20. Excel Chart Types • Clustered Column Chart: A clustered column chart can be used if you need to compare multiple categories of data within individual sub-items as well as between sub-items. For instance, you can use a clustered column chart to compare revenue for each year within each region, as well as between regions. • Stacked Column Chart: A stacked column chart allows you to compare items in a specific range of values as well as show the relationship of the individual sub-items with the whole. For instance, a stacked column chart can show not only the overall revenue for each year, but also the proportion of the total revenue made up by each region. Source: https://www.dummies.com/software/microsoft-office/excel/10-excel-chart-types-and-when-to-use-them/

21. Excel Chart Types • Pie Chart: Another frequently used chart is the old pie chart. A pie chart represents the distribution or proportion of each data item over a total value (represented by the overall pie). A pie chart is most effective when plotting no more than three categories of data. • Bar Chart: Bar charts are typically used to compare several categories of data. Bar charts are ideal for visualizing the distribution or proportion of data items when there are more than three categories. For instance a bar chart could be used to compare the overall revenue distribution for a given set of products. Source: https://www.dummies.com/software/microsoft-office/excel/10-excel-chart-types-and-when-to-use-them/

22. Excel Chart Types • Area Chart: Area charts are ideal for clearly illustrating the magnitude of change between two or more data points. For example, you can give your audience a visual feel for the degree of variance between the high and low price for each month. • Combination Chart: A combination chart is a visualization that combines two or more chart types into a single chart. Combination charts are an ideal choice when you want to compare two categories of each individual sub-item. They are commonly used to create visualizations that show the difference between targets versus actual results. Source: https://www.dummies.com/software/microsoft-office/excel/10-excel-chart-types-and-when-to-use-them/

23. Excel Chart Types • XY Scatter Plot Chart: Scatter charts in Excel (also known as XY scatter plot charts) are excellent for showing correlations between two sets of values. For example an XY scatter plot can be used to illustrate the correlation between employee performance and competency, demonstrating that employee performance rises as competency improves. The x and y axes work together to represent data plots on the chart based on the intersection of x values and y values. • Bubble Chart: A bubble chart is a variation of an XY scatter plot. Just like the XY scatter plot, bubble charts show the correlation between two sets of data. The difference is the addition of a third dimension that is represented by the size of each bubble in the chart. This third dimension is typically used to show the relative impact of a quantitative data item. For instance, in addition to showing employee performance versus competency, you can have the size of each bubble represent years of service, allowing your audience to quickly get a sense of how years of service may affect the relationship between competency and performance. Source: https://www.dummies.com/software/microsoft-office/excel/10-excel-chart-types-and-when-to-use-them/

24. Samples (CDRA Charts)

25. 3rd Task (Created Pivot Chart)

26. 4: Develop Dashboard • After the 4th Task… • Insert new worksheet • Rename the sheet • Place the pivot charts in the dashboard • Make dashboard dynamic by creating slicers • Connect slicer/s to pivot tables • Ensure that tables are connected by sample clicking

27. 4th Task (Sample CDRA Urban Use Dashboard in Excel)

28. How to Create Dashboard Slicer/s • Select a chart in the dashboard • In the “Insert” tab select “slicer” • Choose slicer field and click ok • Position the slicer in the dashboard • Link the slicer to the pivot table to make the charts dynamic • In the options tab, click report connections (or right click the slicer) • Select the pivot table you want the slicer to connect to • Format the slicer as needed

29. Thank You!

30. WORKSHOP/HANDS-ON EXERCISE

31. Necessary Pivots Tables (all hazards for the 5 exposed elements): • Risk Summary • Hazard and Exposure Summary • Severity of Consequence Summary • Vulnerability Summary • Sensitivity Summary • Adaptive Capacity Summary Baseline (Current)

32. What we need to find out: Risks and Exposure • What is the Risk Level (of category e.g. barangay, land use, etc)? (Where is the risk Highest?) • What are the rates/levels of risk factors (scores of: Sensitivity, AC, SevCon,LikeliOfOccur) (Where is it highest?) • Who and What is exposed; % of “subject” (hectares or m2,No, type, etc. City level and Barangay Level) Vulnerability Sensitivity • Why are they negatively affected? Show each factor assessed (per relevant category) Adaptive Capacity • What ACs are there? Show summary of each (per relevant category)

33. (Exercise) Create Pivots for… • Risk Ranking (highest to lowest score, min-max-mean, etc.) • Severity of Consequence ranking (which category has the highest affected area and highest replacement cost) • Likelihood of Occurrence (count/frequency per probability per “category”) • Vulnerability score (ranking) • Sensitivity of area and people (who?, what?, where?) • Adaptive capacity (level/rate of AC of affected area, affected people, government/governance)