1 - PPD554 - Lab 4 - Projecting Outcomes - INSTRUCTIONS - STUDENTS(1)
docx
keyboard_arrow_up
School
University of Nevada, Las Vegas *
*We aren’t endorsed by this school
Course
301
Subject
Industrial Engineering
Date
Feb 20, 2024
Type
docx
Pages
11
Uploaded by Ok3k3Antonio
PPD 554 Foundations of Policy Analysis
LAB #4: Projecting Outcomes – Modeling – DUE Week 9 (10/19)
Convert to PDF and submit on Blackboard (Turnitin) **PART 1 & 2 Optional**
Students will complete five lab workshops during class live sessions over the semester. The lab workshops will cover various methodological topics and allow students to apply and practice the theories and concepts covered in
the course. The teaching team will guide and monitor progress while helping the class to engage with and examine critical points to the activity. Students may work in partnership to complete the analysis, but they must write up the assignments and submit them individually.
Preparation for Lab #4
Instructional Media on Extrapolation in Session 10 folder on Blackboard.
Coast City Case on Blackboard in Lab #4 on Blackboard (same case as Lab #3)
Simple Modeling Principles
1)
A model is a simplified representation of a complex phenomenon. In policy analysis, this complex phenomenon
usually exists in a social environment where measures, causality, and impacts can be unclear. Thus, starting with a basic model that deconstructs and identifies the most critical aspects of the phenomenon can help in understanding what will happen once a policy option is implemented.
2)
For the reasons stated in #1, models can play a helpful role in informing decision-making. They are used to frame problems and inform decisions by estimating
, predicting
, and comparing
. 3)
While advanced statistical models exist, and increasingly more with the emergence of Big Data, this lab will focus on: 1) the essential components of a model, 2) how to find estimates from existing data, 3) a simple cost-effectiveness analysis, and 4) considering uncertainty with sensitivity analysis (optional).
4)
You will use the Excel file (.xlsx) attached to Lab #4 in Blackboard to document the information you gather on assumptions and to conduct your calculations and analysis. You will submit this Excel file (.xlsx) as your Lab #4 assignment on Blackboard.
5)
A practical principle for modeling is to start simple and then work to “complicate” it.
1
PPD 554 Foundations of Policy Analysis
PART ONE – Preparing a Simple Cost-Effectiveness Analysis (15 points)
Cost-Effectiveness Analysis: A cost-effectiveness analysis addresses more than the efficiency question in its considerations. The method is particularly useful when the benefits of a policy option are difficult to monetize. The method keeps the outcome in units that are not merely monetary while also aggregating the costs associated with producing the outcome. A cost-effectiveness ratio has two components:
Aggregate costs associated with producing an outcome
An estimate of a program outcome that is measurable
The cost-effectiveness ratio is helpful because it allows the analyst to compare the relative costs between
policy outcomes. However, to accurately compare cost-effectiveness ratios,
the units of measurement for each policy option's outcome needs to be the same
.
COMPLETE THE FOLLOWING STEPS TO DETERMINE THE AGGREGATE COSTS:
a)
Read the program description of the program you will be modeling for this lab exercise. Driver Safety Program Description
The City Planning Department, in conjunction with the Bicycle Coalition, has agreed to create a Driver Safety
Education Program for Coast City. The education program will be aimed at training drivers that frequently visit the busy strip of Central Ave. and increase their awareness of the presence of bicyclists and educate them
on safe driving practices to share the road with bicyclists.
Operation Details
The driver safety program will utilize the existing office space of the Bicycle Coalition and its existing office equipment. The room where the training would be run has a maximum capacity of 16 people.
The Bicycle Coalition will need to hire a full-time program coordinator to design and run the program.
The program would be a one-time 2-hour class. Training materials and refreshments would be provided.
The program would offer a $15 voucher for a free meal in one of the businesses on Central Ave.
The program has the ability to run 3 times a day, 4 days a week.
Program Coordinator will dedicate 1 month of time toward getting the program off the ground.
b)
Construct a cost analysis that estimates the economic costs of implementing and managing the driver safety program. Cost analysis for programs generally consider economic costs rather than just financial costs when constructing a model. Financial costs
consider only monetary expenditures for resources, such as salaries, rent, and supplies. Economic costs
include these items as well as the value of resources that are used but are not be paid for, such as volunteer time, donated supplies, caregiver care.
1
1
https://www.cdc.gov/policy/polaris/economics/program-cost.html
2
PPD 554 Foundations of Policy Analysis
STEP 1
– Open the Excel workbook (.xlsx) file associated with Lab #4 on Blackboard. Click on the “Part 1 – Assumptions” spreadsheet. A helpful practice in modeling is to keep your assumptions on a separate spreadsheet so that you can easily modify your assumptions without changing the structure of your model.
STEP 2
– The assumptions spreadsheet has been partially completed with all the major cost considerations listed in the “COST” column. There are generally 4 categories of costs to consider for a program:
One-time or up-front costs
are costs that occur only once, usually at the beginning of the program. Sunk costs are different from this category of costs in that sunk costs have already been experienced and are therefore unnecessary to include. Examples: planning, research, pilot projects, and computer software.
Ongoing investments costs
are expenditures that are useful to the program for a while (generally longer than a year). Examples: land, buildings, facilities, equipment, and vehicles.
Recurring costs
are costs associated with operations and/or maintenance and are experienced continuously, usually at regular intervals. Examples: salaries/benefits, wages, materials and supplies, and overhead costs.
Opportunity costs
are costs associated with using a resource for one option instead of another. Example: potential rental income, potential productivity. More on Opportunity Costs
:
Measures like Net Present Value (NPV) are specific calculations that help address opportunity costs and will be covered in future courses (PPD 560). For your final project, opportunity costs are addressed by the fact that you are considering multiple best options. If you find it helpful to consider other cost or revenue data related to an opportunity cost, consider this information in a
separate section that can discuss this more. Do not include opportunity costs in the actual costs, or it will distort your analysis.
Excel Tip:
Create a drop-down list to populate each cell in the “TYPE OF COST” column. To do so, highlight all the cells that will have the drop-down list. Go to “Validation” under the “Data” menu. Select
“List” by the “Allow” field. In the “Source” field, type in the following: "One-time up-front, Ongoing investment, Recurring, Opportunity Cost". Categorize each cost item as either: One-time up-front, Ongoing investment, Recurring, or Opportunity Cost. STEP 3
– Read through the URLs in the “Source” column. Use the information to complete the “ESTIMATE & METHOD OF VALUATION” column for the unfilled cells. Insert a numerical value into the “VALUE” column as well. This will be the value that you incorporate into your cost analysis model. Remember you do not need a numerical value for opportunity costs in this section since they should be considered separately.
STEP 4
– Copy the “Cost Analysis Template” to create a new spreadsheet in your workbook. Rename the spreadsheet “Part 1 – Cost Analysis”. Add the cost items of the program into the cost analysis model. 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
PPD 554 Foundations of Policy Analysis
Be sure to add each item in the correct category of costs, (i.e. One-time up-front, Ongoing investment, Recurring, or Opportunity Cost). Calculate the costs for Year 1 using basic arithmetic functions in Excel. Assume a target of 84 program participants per week in the first year, and then 60 program participants per weeks in the years that follow. Assume a 3% annual salary increase.
Excel Tip:
When utilizing the values in your assumptions spreadsheet in your cost analysis model, insert the cell value (
Spreadsheet!:Column:Row
) instead of typing the actual value. This ensures that any changes that are made to your assumptions can be easily incorporated into the cost analysis. STEP 5
– Estimate costs for the next 5 years. Again, assume a target of 84 program participants per week
in the first year, and then 60 program participants per weeks in the years that follow.
Excel Tip: Use the “$” before the Column variable to keep the value absolute in the formula. Then, when
you copy and paste a formula from one cell into another cell, the other values will change relative to the column position but the $Column will remain the same. c)
Select an outcome for your cost-effectiveness analysis & calculate the cost-effectiveness ratio.
The first step towards selecting an outcome is to remember what the primary and secondary goals of the program are. In this case, the program was developed with two goals in mind. The first goal is to enroll as many participants into the driver safety program as possible. The second goal is to reduce the number of accidents that occur on the busy part of Central Ave. Assume you identified a reliable study which concluded a similar training program resulted in 1 reduced accident per 1000 training participants. The selected outcome for effectiveness should be measured the same way every time, especially if you are going to use this outcome to compare between different policy options. For example, one program outcome might be the number of women receiving prenatal care. Be specific about what is being measured. How many visits does a patient need to attend to be counted? What happens if a patient misses one of the visits? What if the patient does not complete all the steps? NOTE: To help us consider how to create measurable outcomes, review the S and M sections of the SMART guidelines in the Appendix.
STEP 1
– Select an outcome that represents one of the major goals of the program and is measurable. Add a row below “TOTAL COST TO CITY” that describes the cost per outcome you have selected. STEP 2
– Calculate the cost-effectiveness ratio for each year by using basic arithmetic in Excel.
d)
Repeat the steps for a cost analysis of a program that centers around hiring a cycling coordinator and reducing access for bicyclists during the most congested 4 hours of the day. Operation Details:
Implemented by Planning Department and Police Dept.
4
PPD 554 Foundations of Policy Analysis
Data would be collected by a new Cycling Coordinator to identify the busiest 4 hours on the busy strip of Central Ave. Bicyclists would then be prohibited during those 4 hours. Cycling Coordinator would only
work on project for one year.
New signage would need to be erected indicating the times of the new regulation.
More enforcement would need to be stationed in the area during the 4 hours to ticket offending bicyclists. See the assumptions spreadsheet to view the cost items and estimates associated with this option. Follow the same steps as the driver safety program. e)
Using the most productive cost-effectiveness ratio for the driver safety program, how many accidents would the Cycling Coordinator need to prevent to make it AS EFFECTIVE as the driver safety program? Show your answer in the cost analysis spreadsheet by highlighting the answer. PART TWO – Application to Final Paper (5 points) **LAB OPTION – PAPER MANDATORY**
Create a new spreadsheet of assumptions. Select one of the policy options that you are considering for final paper. Give a short description of the policy option at the top of the spreadsheet to help us understand the goals and operations (inputs) of the policy option.
Complete the COST, TYPE OF COST, and SOURCE columns of your assumptions spreadsheet.
5
PPD 554 Foundations of Policy Analysis
IN-CLASS OPTIONAL EXERCISES:
Create a line graph to show how the cost-effectiveness ratio improve over time for the Driver Safety Program option. Search through Excel’s help menu to help you with this if you do not know how to do it. Reference: https://support.microsoft.com/en-us/office/video-create-a-chart-4d95c6a5-42d2-4cfc-aede-0ebf01d409a8
When making projections, there may be uncertainties about the future that can affect our analysis. Thus, to consider how critical these uncertainties are, we can conduct a sensitivity analysis where we vary an assumption (input) and analyze how much that will alter the results of our model.
Try a sensitivity analysis on the cost of the salary of the full-time program coordinator. Use a scroll bar (see Form Controls) to vary the salary cost. How does it impact the cost-effectiveness ratio? How is does it impact the number of accidents that the cycling coordinator option needs to prevent?
Don’t forget to activate the Developer Tab to create a scroll bar. Activate the Developer Tab by going to Preferences -> Ribbon & Toolbars -> Developer Tab. Create a scroll bar by clicking on the icon. Then right-
click on the scroll bar to format the control.
Try completing the COST column and the TYPE OF COST column for a third option where street parking is eliminated, a protected bike path is installed, and a nearby parking lot is constructed. Operation Details:
Implemented by Planning Department and Dept of Transportation.
A protected bike lane would be installed on the busy strip of Central Ave.
Street parking would be removed and a new parking facility would be constructed on a nearby block (7 min walk away) adding 20 new parking spaces.
City would have the option of adjusting parking fees in the parking lot.
The parking lot would be monitored by pay machines with one staff person working to address problems that arise.
6
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
PPD 554 Foundations of Policy Analysis
APPENDIX A: Microsoft Excel terminology
Excerpt from: https://www.windowscentral.com/microsoft-excel-definitions-common-terms
2
Workbook —
The workbook refers to an Excel spreadsheet file. The workbook houses all of the data that you have entered and allows you to sort or calculate the results. A workbook that is available to be viewed and edited by multiple users on a network is known as a Shared Workbook.
Worksheet —
Within the workbook is where you'll find documents called worksheets. Also known as spreadsheets, you can have multiple worksheets nestled in a workbook. Tabs at the bottom of the of the screen will indicate which of your worksheets you are currently working on. This is also known as an active worksheet or active sheet.
Cell —
A cell is a rectangle or block housed in a worksheet. Any data that you want to enter into your worksheet must be placed in a cell. Cells can be color coded, display text, numbers and the results of calculations, based on what you want to accomplish. An Active Cell is one that is currently opened for editing.
Columns and Rows —
Columns and Rows refer to how your cells are aligned. Columns are aligned vertically while rows are aligned horizontally.
Column and Row headings —
These headings are the lettered and numbered gray areas found just outside of columns and rows. Clicking on a heading will select the entire row or column. You can also alter the row height or column width using the headings.
Workspace —
Much like worksheets in a workbook, a workspace allows you to open numerous files simultaneously.
Ribbon —
Above the workbook is a section of command tabs called the Ribbon. A multitude of options are found behind each tab of the ribbon
Cell Reference —
A cell reference is a set of coordinates that identifies a specific cell. It's a combination of letters and numbers. A5, for example, would point to the cell located where column A and row 5 intersect.
Cell Range —
A Cell range is a collection of cells that have been identified as a group based on a variety of criteria. By using a colon (:) between cell references, Excel can determine the range, also known as an array. A range in a row, for example, could look like A1:C1, telling the formula to look at the cells in a row between A1 and C1, while B4:D9 would tell the formula to look at all cells in a box bounded by columns B and D and rows 4 and 9. A 3-D reference refers to a range that encompasses more than one worksheet in the same workbook.
Merged Cell —
When two or more cells are combined, it's become what is known as a merged cell.
Template —
A template is a formatted workbook or worksheet designed to help users fulfill a specific need in Excel. Examples of this include stock analysis, process map, and calendar.
Operator —
Operators are symbols or signs that indicate which calculation must be made in an expression. Operators do not necessarily refer to simple mathematical types; comparison, text concatenation or reference operators also exist.
Formula —
A sequence inside a cell that is used to produce a value. It must begin with an equal (=) sign. This could be a mathematical equation, cell references, functions or operator. A formula is also known as an expression.
Formula Bar —
Nestled between the ribbon and workbook, the Formula Bar will display the contents of an active cell. In the case of formulas, the formula bar will display all components of the formula.
Function —
Functions are formulas that are pre-built into Excel. They are designed to help simplify potentially complex formulas in a worksheet.
Error Code —
Error Codes appear if Excel finds a problem with a provided formula.
2
Blundell, R. (2017, June 22). Microsoft Excel definitions for common terms. Windows Central
. Retrieved from: https://www.windowscentral.com/microsoft-excel-definitions-common-terms
7
PPD 554 Foundations of Policy Analysis
Cell Formatting —
This is the act of changing the in which cell data is displayed in the spreadsheet. When you format cells, only the visual appearance of the cells is changed; the value within the cells remain constant.
Conditional Formatting —
Formatting is applied only when the cell meets determined criteria such as duplicate values or values above or below a threshold.
Filter —
Filters are rules that you can employ to decide which rows in a worksheet to display. These filters can use data such as conditions or values.
Freeze Panes —
Freezing Panes allows you to select specific columns and/or rows to remain visible on the worksheet, even if you are scrolling, such as header cells that label a column.
AutoFill —
This enables you to effortless copy data to more than one cell.
AutoSum —
This feature will add up the numbers you have entered in your sheet and displays the total in a cell of your choosing.
AutoFormat —
This is an automated format application to cells that match pre-determined criteria. This could be
as simple as font alignment and size.
Data Validation —
This feature helps to prevent incorrect data from being entered into your worksheet. This most commonly used to create drop-down lists for common terms. Data validation promotes consistency and accuracy in the data to be entered.
Pivot Table —
This is a data summarization tool most commonly used to sort, average to sum up data automatically. The information is pulled from one table while the results are displayed in another. Pivot Tables makes it easy to retrieve specific information from a large source of data.
Pivot Chart —
This type of chart provides a visual aid for pivot tables. By providing graphical representations of
the pivot table data, the user can provide a level of interactivity with the data.
Pivot Area —
The pivot area is a point on the worksheet where you would drag a Pivot Table field in order to reorganize how a report is displayed.
Source Data —
This is the information used to create your pivot table. It can either exist within the worksheet or from and an external database.
Values Area —
In a pivot table, Value areas are identified as the cells that contain the summary information.
Item —
These are sub-categories of fields in your pivot table. If you have a field that is marked State, the items could be Alabama, Alaska and so on.
8
PPD 554 Foundations of Policy Analysis
APPENDIX B: Drafting Metrics for Criteria with SMART Guidelines
Excerpt from: University of California’s SMART Goals: A How to Guide
3
pp. 4-6.
S – Specific When setting a goal, be specific about what you want to accomplish. Think about this as the mission statement for your goal. This isn’t a detailed list of how you’re going to meet a goal, but it should include an answer to the popular ‘w’ questions:
Who – Consider who needs to be involved to achieve the goal (this is especially important when you’re working on a group project).
What – Think about exactly what you are trying to accomplish and don’t be afraid to get very detailed.
When – You’ll get more specific about this question under the “time-bound” section of defining S.M.A.R.T. goals, but you should at least set a time frame.
Where – This question may not always apply, especially if you’re setting personal goals, but if there’s a location or relevant event, identify it here.
Which – Determine any related obstacles or requirements. This question can be beneficial in deciding if your goal is realistic. For example, if the goal is to open a baking business, but you’ve never baked anything before, that might be an issue. As a result, you may refine the specifics of the goal to be “Learn how to bake in order to open a baking business."
Why – What is the reason for the goal? When it comes to using this method for employees, the answer will likely be along the lines of company advancement or career development.
“S” actions may include: Oversee Update Write Coordinate Upgrade Process Supervise Develop Provide Manage Create Maintain Plan Implement Reconcile Support Evaluate Direct Transition Produce Administer
Note that this list does not include verbs like “improve,” “reduce,” or “increase” (e.g. “Improve
customer service” or “reduce cost.” These imply the direction that you want a result to move in, but don’t do much to explain the role or specific action that you will take to accomplish this change.
M – Measurable 3
University of California (2017). “Smart Goals: A How to Guide”, University of California. Retrieved from: https://www.ucop.edu/local-human-
resources/_files/performance-appraisal/How%20to%20write%20SMART%20Goals%20v2.pdf
9
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
PPD 554 Foundations of Policy Analysis
What metrics are you going to use to determine if you meet the goal? This makes a goal more tangible because it provides a way to measure progress. If it’s a project that’s going to take a few months to complete, then set some milestones by considering specific tasks to accomplish. Milestones are a series of steps along the way that when added up will result in the completion of your main goal.
As the “M” in SMART states, there should be a source of information to measure or determine whether a goal has been achieved.
The M is a direct (or possibly indirect) indicator of what success for a particular goal will look like.
Sometimes measurement is difficult and managers and employees will need to work together to identify the most relevant and feasible data sources and collection methods.
Data collection efforts needed to measure a goal can be included in that goal’s action plan.
Even if a perfect, direct measurement source is not immediately feasible for a given goal, the discussion about the desired end result (why this goal is important) and what the measurement options are (what success might look like) is an important and valuable part of performance planning.
Measurement methods can be both quantitative (productivity results, money saved or earned, etc.) and qualitative (client testimonials, surveys, etc.).
A – Achievable This focuses on how important a goal is to you and what you can do to make it attainable and may require developing new skills and changing attitudes. The goal is meant to inspire motivation, not discouragement. Think about:
how to accomplish the goal,
if you have the tools/skills needed,
if not, consider what it would take to attain them.
R – Relevant Relevance refers focusing on something that makes sense with the broader business goals. For example, if the goal is to launch a new program or service, it should be something that’s in alignment with the overall business/department objectives. Your team may be able to launch a new program, but if your division is not prioritizing launching that type of new programs, then the goal wouldn’t be relevant. T – Time-Bound Anyone can set goals, but if it lacks realistic timing, chances are you’re not going to succeed. Providing a target date for deliverables is imperative. Ask specific questions about the goal deadline and what can be accomplished within that time period. If the goal will take three months to complete, it’s useful to 10
PPD 554 Foundations of Policy Analysis
define what should be achieved half-way through the process. Providing time constraints also creates a sense of urgency.
11