Public Budgeting Please find study materials on cost-benefit analysis below. Follow the instructions and answer the questions on the instruction sheet following the data on the excel sheet

Cost-benefit Analysis: Template for Mobile Job Centers Escalation rate (ex. technology) % still employed the next year # of Buses Host site agreements per bus Purchase/outfitting per bus Fuel/maint./cleaning per bus Technology leasing per bus Supplies and printing per bus Insurance Driver compensation Placement spec. compensation Director compensation Project office+tech (1-3 buses) Project office+tech (4-6 buses) Project office+tech (7-9 buses) Dir. assistant compensation Sup'v compensation (>3 buses) Sup'v assistant compensation Budget analyst (>6 buses) 3% 50% 9 $20,000 $175,000 $10,000 $8,000 $6,000 $25,000 $55,000 $65,000 $90,000 $7,500 $15,000 $18,750 $50,000 $75,000 $45,000 $70,000 Computation of intial benefit Applicants placed per bus for first 3 (1-3 buses) 312 Add'l taxable income for full year employed Applicants placed per bus for next 3 (4-6 buses) 234 (operational year) Applicants placed per bus for last 3 (7-9 buses) 156 (transition year) $3,120,000 $20K per placement*312 per bus*0.5 (average 1/2 year employed) $20K*312*0.5=$3,120,000 /|\\ per bus taxable income: 1st-time placements (not residual) /|\\ per bus taxable income One-Time Costs Description Quantity Host site agreements per bus Purchase/outfitting per bus Unit cost 9 $20,000 9 $175,000 Recurring Costs Description Technology leasing per bus Fuel/maint./cleaning per bus Supplies and printing per bus Insurance Driver compensation Placement spec. compensation Director compensation Dir. assistant compensation Project office+tech (7-9 buses) Quantity 9 9 9 9 9 9 1 1 1 Trans. year unit cost $4,000 $5,000 $3,000 $12,500 $27,500 $32,500 $90,000 $50,000 $18,750 Op'l year unit cost $8,000 $10,000 $6,000 $25,000 $55,000 $65,000 $90,000 $50,000 $18,750 Sup'v compensation (>3 buses) Sup'v assistant compensation Budget analyst (>6 buses) 1 1 1 $75,000 $45,000 $70,000 $75,000 $45,000 $70,000 Recurring Benefits Trans. year Description Quantity unit benefit Op'l year unit benefit "Seed" grant 1 $600,000 $300,000 Employment benefit 9 $18,720 $74,880 Residual employment benefit NOTHING ENTERED IN THESE CELL 2106 annual new employees /|\\ Number of new/resdidual employees Disc. factors: formulas divide by (1+disc. rate) ====> 5.00% Present Value Present Value of Benefits Present Value of Costs Net Present Value Benefit/Cost Ratio=======================> 1.000 Cumulative NPV through... ome for full year employed $20,000 $10,000 $99,840 *3.2% =$99,840 /|\\ per bus tax Transition Year $ 180,000 $ 1,575,000 Transition Year $ 36,000 $ 45,000 $ 27,000 $ 112,500 $ 247,500 $ 292,500 $ 90,000 $ 50,000 $ 18,750 $ $ $ $ $ $ $ $ $ Op'l Yr 1 72,000 92,700 55,620 231,750 509,850 602,550 92,700 51,500 19,313 $ $ $ $ $ $ $ $ Op'l Yr 2 #NAME? 95,481 57,289 238,703 525,146 620,627 95,481 53,045 19,892 $ $ $ $ $ $ $ $ Op'l Yr 3 #NAME? 98,345 59,007 245,864 540,900 639,245 98,345 54,636 20,489 $ $ $ $ $ $ $ $ Op'l Yr 4 #NAME? 101,296 60,777 253,239 557,127 658,423 101,296 56,275 21,103 $ $ $ $ $ $ $ $ Op'l Yr 5 #NAME? 104,335 62,601 260,837 573,841 678,175 104,335 57,964 21,736 $ $ $ 75,000 $ 45,000 $ 70,000 $ 77,250 $ 46,350 $ 72,100 $ 79,568 $ 47,741 $ 74,263 $ 81,955 $ 49,173 $ 76,491 $ 84,413 $ 50,648 $ 78,786 $ 86,946 52,167 81,149 Transition Year Op'l Yr 1 Op'l Yr 2 Op'l Yr 3 Op'l Yr 4 Op'l Yr 5 $ 600,000 $ 309,000 $ 318,270 $ 327,818 $ 337,653 $ 347,782 $ 168,480 $ 694,138 $ 714,962 $ 736,411 $ 758,503 $ 781,258 $ - $ 694,138 $ 1,072,443 $ 1,288,719 $ 1,422,193 $ 1,513,687 residual employees--> 1053.0 1579.5 1842.8 1974.4 2040.2 1 0.952 0.907 0.864 0.823 0.784 Transition Year Op'l Yr 1 Op'l Yr 2 Op'l Yr 3 Op'l Yr 4 Op'l Yr 5 $ 768,480 $ 1,616,453 $ 1,909,909 $ 2,032,564 $ 2,071,852 $ 2,070,646 $ 2,864,250 $ 1,832,079 #NAME? #NAME? #NAME? #NAME? $ (2,095,770) $ (215,626) #NAME? #NAME? #NAME? #NAME? /|\\ annual PV /|\\ annual PV /|\\ annual PV /|\\ annual PV /|\\ annual PV /|\\ annual PV Transition Year Op'l Yr 1 Op'l Yr 2 Op'l Yr 3 Op'l Yr 4 Op'l Yr 5 $ (2,095,770) $ (2,311,396) $ (2,196,710) $ (1,923,308) $ (1,575,333) $ (1,194,036) $ $ $ $ $ $ $ $ Op'l Yr 6 #NAME? 107,465 64,479 268,662 591,056 698,521 107,465 59,703 22,388 $ $ $ $ $ $ $ $ Op'l Yr 7 #NAME? 110,689 66,413 276,722 608,788 719,476 110,689 61,494 23,060 $ $ $ $ $ $ $ $ Op'l Yr 8 #NAME? 114,009 68,406 285,023 627,051 741,060 114,009 63,339 23,752 $ $ $ 89,554 $ 53,732 $ 83,584 $ 92,241 $ 55,344 $ 86,091 $ 95,008 57,005 88,674 Op'l Yr 6 Op'l Yr 7 Op'l Yr 8 $ 358,216 $ 368,962 $ 380,031 $ 804,696 $ 828,837 $ 853,702 $ 1,584,245 $ 1,644,723 $ 1,700,734 2073.1 2089.5 2097.8 0.746 0.711 0.677 Op'l Yr 6 Op'l Yr 7 Op'l Yr 8 Total 2,049,970 $ 2,020,127 $ 1,986,162 $ 16,526,163 #NAME? #NAME? #NAME? $ 16,532,111 #NAME? #NAME? #NAME? $ (5,948) /|\\ annual PV /|\\ annual PV /|\\ annual PV /|\\ Total PV Op'l Yr 6 Op'l Yr 7 Op'l Yr 8 Entire project $ (799,625) $ (401,988) $ (5,948) $ Instructions for CBA Assignment Mobile Job Centers You're a budget analyst for the City of Baltimore and are informing Baltimore's leaders about the feasibility of implementing the Mobile Job Center project to increase employment among unemployed city residents who can't or won't utilize \"bricks & mortar\" employment centers. This assignment is different from the Cutback Scenario, because your spreadsheet is provided for you. There are 9 spreadsheets, select the one that matches the last digit of your student ID: For student ids ending in 1, CBA_Worked for Mobile Job Centers_1 bus.xlsx For student ids ending in 2, CBA_Worked for Mobile Job Centers_2 buses.xlsx For student ids ending in 3, CBA_Worked for Mobile Job Centers_3 buses.xlsx For student ids ending in 4, CBA_Worked for Mobile Job Centers_4 buses.xlsx For student ids ending in 5, CBA_Worked for Mobile Job Centers_5 buses.xlsx For student ids ending in 6, CBA_Worked for Mobile Job Centers_6 buses.xlsx For student ids ending in 7, CBA_Worked for Mobile Job Centers_7 buses.xlsx For student ids ending in 8, CBA_Worked for Mobile Job Centers_8 buses.xlsx For student ids ending in 9 or 0, CBA_Worked for Mobile Job Centers_9 buses.xlsx Once you have opened the spreadsheet you will immediately see parameters, that is, the factors that affect the results of the cost-benefit analysis. You're analyzing the project for your number of buses. For example, if your spreadsheet assumes one (1) bus, write your memo based on that assumption, ignoring the possibility of more buses. If your spreadsheet assumes nine (9) buses, ignore the possibility of fewer buses. These factors include both costs and benefits, as follows: Costs Host site agreements: $20,000 per bus Bus, to include outfitting (lasts 8-10 years): $175,000 per bus Fuel/maintenance/cleaning $10,000 per bus Technology leasing $8,000 per bus Supplies and printing $6,000 per bus Insurance $25,000 per bus Driver's compensation $55,000 per bus Placement specialist's compensation $65,000 per bus Director's compensation $90,000 Project office space/tech. (depending on size) $7,500 to $18,750 Director's assistant's compensation $50,000 Supervisor's compensation (if > 3 buses) $75,000 Supervisor's assistant's compensation (\" \" \") $45,000 Budget analyst (if > 6 buses) $70,000 Benefits Grants for pilot are stepped up for more buses: - $200,000 to start and $100,000 annually (1-3 buses) - $400,000 to start and $200,000 annually (4-6 buses) - $600,000 to start and $300,000 annually (7-9 buses) Tax revenue is figured as follows: Please note that the nearly $100,000 of new tax revenue per bus (due to added employment in the city) is assumed to decrease after the first 3 buses, because of diminishing returns. That is, more and more placement specialists are \"chasing\" fewer and fewer qualified and marketable applicants. You can gauge the level of diminishing returns at the level of buses you have by going to cell D51, which is the average tax revenue contribution estimated for a bus. For example, for 9 buses we're at 75% of the optimal contribution of the first 3 buses: figured as follows: (3 @ 100% + 3 @ 75% + 3 @ 50%) / 9 = 75% of the optimal contribution of ~$100,000 = ~$75,000. One more minor detail: that amount in E51 is so tiny because a) the buses don't start rolling for 6 months and the maximum additional income is $10,000 (only possible to be employed for a half-year, and that's only for those placed the first week the buses start rolling). That's only half of the applicants placed (vs. operational years), who earn just half of the salary [6 months' worth] per person: 1/2 of people placed * 1/2 of salary earned = 1/4 of taxes yielded, compared with operational years. There's only one more benefitbut a huge one, which is related to what we've just been talking about. The thing about employing people is that they continue to pay taxes as long as they have the job or another one. What I computed in the row entitled \"Residual employment benefit\" is the lingering effect in later years of jobs the Job Centers helped residents get in earlier years. The percentage of jobs kept year-to-year is assumed to be 50 percent (jobs lost = 50%)except for the transition year, which were all placed in the last 6 months and are assumed to be kept for the entire following year. That 50 percent \"residual employment\" factor means that by the end of the project, there's still a tiny fraction ( 1.00) represents a favorable financial result on paper. It is computed in Cell C60. 3) Incidentally, the Benefit-Cost Ratio (BCR) varies depending on the number of Job Centers. For 1 Job Center ... it's 1.08. For 2 Job Centers ... it's 1.17. For 3 Job Centers ... it's 1.22. For 4 Job Centers ... it's 1.14. For 5 Job Centers ... it's 1.12. For 6 Job Centers ... it's 1.10. For 7 Job Centers ... it's 1.07. For 8 Job Centers ... it's 1.03. For 9 Job Centers ... it's 1.00. 4) Another important result is based on the same two inputs: the present value of benefits and the present value of costs. In this case, we subtract instead of dividing: Net Present Value = PV of Benefits - PV of Costs. Any NPV greater than zero (> 0) means a favorable financial result on paper. It is computed for you in cell N59 (Total NPV = the sum of the yearly NPVs in E59 through M59). 5) After reviewing the results, you can write the memo. I'm not going back over the basics - you know the drill! I'm not holding you to two pages, so go a little longer if you need to. The format will be a little different for a cost-benefit analysis. However, it will include some of the fundamentals you already know. You'll need to provide context and an overview of the analysis, including the basic aim of CBA--quantifying financial advantage/disadvantage: comparing the costs and benefits of a project to augment the City's job placement efforts using buses. The comparison is made possible using projections of the jobs gained and the tax revenue estimated to result from the new (or better) jobs. When the memo's done, you should've conveyed the following information: a) Context. Brief description of the situation. What does the City expect to gain from this project? Feel free to borrow my description of the situation if that's helpful. b) Costs and benefits. Description of what cost-benefit analysis does in general and, in this instance, which of the costs and benefits readers should pay attention to, in enough detail that the analysis you're doing in the rest of the memo will make sense to the reader. Make sure to touch on the most important factors. You will find those itemized above. But I don't recommend making a \"laundry list\" of all the details of costs and benefits. Suitable brevity can be achieved by summarizing and (to the extent you're comfortable) approximating. Please be selective about what matters most! c) Results. Description of the quantitative results of your analysis. Out of the many (hundreds!) of amounts contained on the spreadsheet, focus on the key results. (Hint: the Benefit-Cost Ratio (BCR) is one of them and Net Present Value (NPV) is another!) You also should indicate how patient managers must be while waiting for the initial project deficits to turn positive. To illustrate, by examining the project-to-date results in row 62, you can see when the project recovers from the large shortfall in the transition year (where you'll find parentheses surrounding the amount in E62, meaning a negative number). Somewhere to the right of E62, the parentheses go away, meaning a positive amount. That year, when negatives first turn to positives in row 62, is termed the \"breakeven point.\" d) Sensitivity analysis. Description of the project's level of uncertainty and a sense of the risk(s) of pursuing the project in case events don't go according to plan. You should change one or more of the parameters, which are in B5 through B20 (except for the grant amounts, which are in C50 and D50). By altering the assumptions, you test how susceptible (changeable) the results you've described (BCR, NPV, breakeven point) are to potentially unrealistic assumptions. Even if they're the best estimates we have, managers won't readily trust the assumptions, because they know that the future (when the assumptions come true or not) is uncertain. For instance, would this project be a wise move if it only ran 6 operational years instead of 8? What if the drivers cost more or the less than the $55,000 compensation (salary plus benefits) we estimated? What if each of the initial buses (1-3) generates less than 6 new jobs per week, which is in C21 (6*52 = 312; but 5*52= 260; and 4*52=208)? These are just examples of the questions you might look at... you can make up other questions on your own. Mainly, I encourage you to look beyond the \"bottom line\" (BCR, NPV...) at the underlying numbers that produce them. One thing you shouldn't change is the number of buses, which is a fixed element of your project. e) Recommendation. The conclusion of your memo is a recommendation for the City to proceed or not to proceed with the Mobile Job Center(s) project. Your recommendation should be based, to an extent, on the quantitative results that you covered in the rest of the memo. However, you also can bring in other, qualitative issues that you think are important. Good luck