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.

urgent2 pages follow instructionstimely grammer reliable

Sheet

1

Applicants placed per bus for

$20,000

Applicants placed per bus for

$10,000

able income

: 1st-time placements (not residual)

/|\ per bus taxable income /|\ per bus tax

Host site agreements per bus 9 $20,000

Purchase/outfitting per bus 9 $175,000

Description Quantity

Transition Year

Technology leasing per bus 9

$8,000

$ 72,000 $ 72,000 $ 72,000 $ 72,000 $ 72,000 $ 72,000 $ 72,000

Fuel/maint./cleaning per bus 9

$10,000

Supplies and printing per bus 9

$6,000

Insurance 9

$25,000

Driver compensation 9

$55,000

Placement spec. compensation 9

$65,000

Director compensation 1 $90,000 $90,000

$ 92,700 $ 95,481 $ 98,345 $ 101,296 $ 104,335 $ 107,465 $ 110,689 $ 114,009

Dir. assistant compensation 1 $50,000 $50,000

Project office+tech (7-9 buses) 1 $18,750 $18,750

Sup’v compensation (>3 buses) 1 $75,000 $75,000

Sup’v assistant compensation 1 $45,000 $45,000 $ 45,000

Budget analyst (>6 buses) 1 $70,000 $70,000

Description Quantity

Transition Year Op’l Yr 1 Op’l Yr 2 Op’l Yr 3 Op’l Yr 4 Op’l Yr 5 Op’l Yr 6 Op’l Yr 7 Op’l Yr 8

1

9

$ 694,138

1

Transition Year Op’l Yr 1 Op’l Yr 2 Op’l Yr 3 Op’l Yr 4 Op’l Yr 5 Op’l Yr 6 Op’l Yr 7 Op’l Yr 8

/|\ annual PV /|\ annual PV /|\ 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 Op’l Yr 6 Op’l Yr 7 Op’l Yr 8

$ (2,095,770)

$ (5,948)

																																						Cost-benefit Analysis: Template for Mobile Job Centers
Escalation rate (ex. technology)	3%
% still employed the next year	50%
# of Buses										9
	Host site agreements per bus			$20,000
	Purchase/outfitting per bus		$175,000
	Fuel/maint./cleaning per bus			$10,000
	Technology leasing per bus		$8,000
	Supplies and printing per bus		$6,000
	Insurance		$25,000
	Driver compensation		$55,000
	Placement spec. compensation		$65,000
	Director compensation			$90,000
Project office+tech (1-3 buses)	$7,500
Project office+tech (4-6 buses)	$15,000
	Project office+tech (7-9 buses)			$18,750
	Dir. assistant compensation			$50,000
	Sup’v compensation (>3 buses)			$75,000
	Sup’v assistant compensation			$45,000
	Budget analyst (>6 buses)			$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
next 3 (4-6 buses)	234	(operational year)
last 3 (7-9 buses)	156	(transition year)
$3,120,000	$99,840
$20K per placement*312 per bus*0.5 (average 1/2 year employed)	$20K*312*0.5=$3,120,000	*3.2% =$99,840
		/|\ per bus tax
One-Time Costs
		Description			Quantity	Unit cost					Transition Year
$ 180,000
$ 1,575,000
Recurring Costs
Trans. year unit cost	Op’l year unit cost				Op’l Yr 1				Op’l Yr 2				Op’l Yr 3				Op’l Yr 4				Op’l Yr 5				Op’l Yr 6				Op’l Yr 7				Op’l Yr 8
$4,000	$ 36,000								$ 72,000
$5,000		$ 45,000		$ 92,700		$ 95,481		$ 98,345		$ 101,296		$ 104,335		$ 107,465		$ 110,689		$ 114,009
$3,000	$ 27,000	$ 55,620	$ 57,289	$ 59,007	$ 60,777	$ 62,601	$ 64,479	$ 66,413	$ 68,406
$12,500	$ 112,500	$ 231,750	$ 238,703	$ 245,864	$ 253,239	$ 260,837	$ 268,662	$ 276,722	$ 285,023
$27,500	$ 247,500	$ 509,850	$ 525,146	$ 540,900	$ 557,127	$ 573,841	$ 591,056	$ 608,788	$ 627,051
$32,500	$ 292,500	$ 602,550	$ 620,627	$ 639,245	$ 658,423	$ 678,175	$ 698,521	$ 719,476	$ 741,060
$ 90,000
$ 50,000	$ 51,500	$ 53,045	$ 54,636	$ 56,275	$ 57,964	$ 59,703	$ 61,494	$ 63,339
$ 18,750	$ 19,313	$ 19,892	$ 20,489	$ 21,103	$ 21,736	$ 22,388	$ 23,060	$ 23,752
$ 75,000	$ 77,250	$ 79,568	$ 81,955	$ 84,413	$ 86,946	$ 89,554	$ 92,241	$ 95,008
$ 46,350	$ 47,741	$ 49,173	$ 50,648	$ 52,167	$ 53,732	$ 55,344	$ 57,005
$ 70,000	$ 72,100	$ 74,263	$ 76,491	$ 78,786	$ 81,149	$ 83,584	$ 86,091	$ 88,674
Recurring Benefits
Trans. year unit benefit	Op’l year unit benefit
“Seed” grant	$600,000	$300,000	$ 600,000	$ 309,000	$ 318,270	$ 327,818	$ 337,653	$ 347,782	$ 358,216	$ 368,962	$ 380,031
Employment benefit	$18,720	$74,880	$ 168,480		$ 694,138	$ 714,962	$ 736,411	$ 758,503	$ 781,258	$ 804,696	$ 828,837	$ 853,702
Residual employment benefit	NOTHING ENTERED IN THESE CELLS	2106	$ –	$ 1,072,443	$ 1,288,719	$ 1,422,193	$ 1,513,687	$ 1,584,245	$ 1,644,723	$ 1,700,734
Number of new/resdidual employees	annual new employees /|\	residual employees–>	1053.0	1579.5	1842.8	1974.4	2040.2	2073.1	2089.5	2097.8
Disc. factors: formulas divide by (1+disc. rate) ====>	5.00%	0.952	0.907	0.864	0.823	0.784	0.746	0.711	0.677
Present Value	Total
Present Value of Benefits	$ 768,480	$ 1,616,453	$ 1,909,909	$ 2,032,564	$ 2,071,852	$ 2,070,646	$ 2,049,970	$ 2,020,127	$ 1,986,162	$ 16,526,163
Present Value of Costs	$ 2,864,250	$ 1,832,079	$ 1,795,223	$ 1,759,162	$ 1,723,877	$ 1,689,349	$ 1,655,559	$ 1,622,490	$ 1,590,123	$ 16,532,111
Net Present Value		$ (2,095,770)	$ (215,626)	$ 114,686	$ 273,402	$ 347,974	$ 381,297	$ 394,411	$ 397,637	$ 396,039		$ (5,948)
Benefit/Cost Ratio=======================>	1.000									/|\ annual PV	/|\ Total PV
Entire project
Cumulative NPV through…	$ (2,311,396)	$ (2,196,710)	$ (1,923,308)	$ (1,575,333)	$ (1,194,036)	$ (799,625)	$ (401,988)

Sheet2

Sheet3

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:

Computation of intial benefit

Applicants placed per bus for first 3 (1-3 buses) 312

Applicants placed per bus for next 3 (4-6 buses) 234 (operational year) $20,000

Applicants placed per bus for last 3 (7-9 buses) 156 (transition year) $10,000

$3,120,000 $99,840

$20K per placement*312 per bus*0.5 (average 1/2 year employed) $20K*312*0.5=$3,120,000

*3.2% =$99,840

/|\ per bus taxable income: 1st-time placements (not residual) /|\ per bus taxable income /|\ per bus tax

Add’l taxable income for full year employed

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 benefit—but 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 (<0.5%) of the jobs left of those applicants placed in the first year. Add up all the people still working in operational year 8 who were placed in prior years, and we have almost exactly the same number of the workers due to residual employment as those placed within the year.

WHAT TO PAY ATTENTION TO

You’ll notice that the above parameters are entered in the blank (unshaded) cells within columns A through D and results are computed in the shaded cells within columns E through N. There are a tremendous number of dollar amounts produced, so how to select what’s important for memos?

1) In the orange area from column E through column N and rows 57-59, the present values of costs and benefits computed in rows above result from “discounting” (to account for the time value of money) by multiplication by the discount factors (in F54 through M54). You will notice that these discount rates cause the present value of costs in the cells of row 58 get progressively smaller as time goes by, in contrast to the escalated values above (in columns F though M of rows 36 through 46) where the amounts get larger with time. This is because the impact of the discount effect is more powerful than the escalation (inflation) effect, which is usually the case (who’s going to lend money at less than the rate of inflation?). We don’t see the same effect for the discounted benefits in row 57 because the employment effects grow actually over time due to the residual employment (see above), whereas costs don’t change.

2) One of the most important results is the Benefit-Cost Ratio. It was necessary to first do the present value computations (resulting in rows 57-59) because Benefit-Cost Ratio can only be calculated after discounting to get present value. The BCR is defined as the present value of benefits divided the present value of costs. Anything greater than one (> 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!