Management homework help. 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 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?
- 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.
- 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.
- 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. - 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).
- 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:
- 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.
- 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!
- 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.”
- 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.
- 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!
