Use Excel answer certain questions

Spreadsheet Data for Question 2 Year Production Value of Leisure Wage Profile A 220 Wage Profile B 100 220 300 190 100 300 350 220 100 350 400 100 250 400 450 280 100 450 500 310 100 500 510 100 340 510 520 100 370 520 400 530 110 540 530 430 1 10 540 550 460 120 550 555 490 130 555 560 520 140 560 565 550 150 565 570 580 180 570 550 610 200 550 530 640 250 530 500 670 300 450 500 700 20 325 450 350 730 350 350 7602. Paul is starting a new job with SuperKleen janitorial services. Provided he is not fired for shirking, Paul will stay with SuperKleen for twenty years. His productivity (assuming he does not shirk) and value of leisure over this period are given by the first two columns of the attached spreadsheet. SuperKleen is deciding between two wage lifetime profiles it might offer to Paul. These are labelled as wage profiles A and B in the attached spreadsheet. The discount rate is zero. a) What is the present value of Paul's lifetime productivity? What is the present value of the wages he receives from SuperKleen under profile A? Under profile B? What words might you use to describe the compensation policy embodied in each of these two schemes? b) When is Paul's efficient retirement date? If he is paid according to profile A will he retire voluntarily at that date? What if he is paid according to profile B? c) For each year Paul works at SuperKleen, use a spreadsheet to calculate the remaining present value of Paul's productivity at SuperKleen, i.e. what's the present value of output he has yet to produce (including the current year) at each date of his career. (Hint: start at Paul's last year with SuperKleen and work backwards). d) Do the same thing as in part (c), but for Paul's wages (in separate columns for the A and B wage profiles). In other words, at each date of his career, find the present value of the wages Paul has yet to receive from SuperKleen (if he stays on the job and doesn't shirk). Do this for the remaining present value of leisure too (this is what Paul gets if he stops working altogether at any particular date). e) If Paul shirks (for example he sleeps on the job) and is caught, he is fired. One option he has after being fired is to take an alternative job where (because the workers are easily monitored) shirking is not an issue. In this job he produces 50 units of output less each year than at SuperKleen. Add columns to your spreadsheet that calculate Paul's productivity each year on the alternative job, and his remaining present value of productivity on that job. Assume that, if he takes this job, the firm offering it just breaks even, i.e. over the course of his career there, he receives a wage profile equal in present value to his remaining productivity there.f) Now consider Paul's incentives to shirk in his job at SuperKleen. If he shirks in any given year, he experiences a gain in on-the-job leisure that year that is worth 50 to him. In that event, he is detected with probability p=.25 (set up your spreadsheet so you can input alternative values of p, and of the value of the extra leisure he gets from shirking). If he is detected, Paul is fired and takes his next best alternative (which is either the alternative job or leisure, i.e. early retirement, whichever is higher) for the rest of his career. So, overall, Paul's net gain to cheating consists of: . The extra on-the-job leisure of 50 (which he gets for sure) 3 Personnel Economics Exercise 4 . A (1-p) chance of losing nothing A p chance of the difference between his remaining present value of wages at SuperKleen and the remaining present value of his next best alternative to working at SuperKleen. In your spreadsheet, calculate Paul's net gain to shirking in each year of his career under wage profile A, and under wage profile B. (Hint: use excel's "max" function to pick the higher of two arguments). g) What are Paul's net gains from shirking in his 17" year at SuperKleen under wage profile A? Under profile B? Overall, which profile gives him the greater incentive to shirk? Comment on which stages of his work life where Paul is most tempted to shirk under each profile, and explain. h) Increase the on-the-job leisure Paul gets from shirking from 50 to 100. Does wage profile B still prevent him from shirking throughout his career? Now put this value back to 50 and reduce the firm's detection probability to . 1. Now does wage profile B prevent shirking? In each of the above cases, if profile B does not prevent shirking, how would you modify it to prevent shirking