ANALYSIS OF REDECORATING COSTS Purpose: In the beginning of each year, the company prepares an annual budget for Redecorating costs by community. Now that it is the middle of the year, you have been asked by the head of Property Operations to evaluate whether the budget for the remainder of the year is reasonable by preparing a forecast of anticipated costs for the fourth quarter. One of our analysts had started the exercise, but since they have called out sick we need your help to finish. Our concern is that actual redecorating costs to be incurred for the fourth quarter will be well above budget such that action items will need to be taken immediately to reduce future expenditures. Background Redecorating costs relate to costs incurred to get apartment homes ready for new occupants. Typical costs include painting, carpet replacement, and appliance repairs If a resident elects to renew, redecorating costs are very minor (touch-up painting, carpet washing, and replacement of small appliances) If the existing resident moves out, and is replaced by a new resident, then redecorating costs are much higher (full repainting by contractors, complete carpet replacement, and possible major appliance replacement). How to estimate Redecorating costs: Redecorating costs are influenced by the number of residents that renew their leases vs. the number of new residents that move-in during a given period. The number of "renewals" and "move-ins" can be determined based on the change in occupied homes during the period: Change in occupancy from the beginning of the month to the end of the month = O + Occupied Homes at the beginning of the month 0 - Resident leases that expire (i.e. these resident will either renew or move-out) o + Renewal leases o + New Move-Ins o = Occupied Homes at the end of the month Total redecorating costs per period = (Renewal leases x redec cost per renewal) + (New move-ins x redec costs per move-in) Exercise Calculate a forecast of 4Q 2021redecorating costs for a 400 apartment home community located in Southern California and compare the result to the budgeted redecorating costs for that period, which is $100,000. To help you complete the exercise, we've provided an Excel workbook that has four different tabs: TAB 1: "BASELINE - Use this template as a starting point for your model. The columns are as follows: Period. Track costs on a monthly basis for October 2021 - December 2021 in order to arrive at the total for the quarter. Homes. Assume a community of 400 apartment homes Occupancy. Based on feedback from the residential services team it is believed the occupancy at the end of the 3rd Quarter will be 95%. Assumptions for occupancy during October, November and December are included on TAB 2: "OCCUPANCY DATA". For your benefit, the individual regions are defined on TAB 4: "REGION KEY" Change in occupied homes. The percentage increase or decrease in occupied homes multiplied by the total number of homes will equal the change in occupied homes for the period. Expirations. Relates to the number of leases that end during the period. Assumptions for future expirations is provided on TAB 3: "EXPIRATION DATA". Renewal %. Relates to the percentage of residents whose leases expire who will choose to stay at the community by renewing their leases. Assume that the renewal % during each month of the quarter is 55%. Renewals. The number of residents that renew their leases in the period. Move-Outs. The number of residents that elect not to renew their lease and therefore move-out. Move-Ins. The number of new residents that move into the community. By definition, Move-ins will equal the net increase or decrease in occupied homes plus the number of move-outs during the period. Total cost of redecorating. Our purchasing department has provided the following projections for redecorating costs (per Move-In and Renewal): Month October November December Redec Cost per Individual Move-in $2,200 $2,250 $2,300 Redec Cost per Individual Renewal $500 $550 $600 Please calculate the forecasted Redecorating cost for this community using this template and compare the amount to budget ($100,000 for the quarter) both in terms of a dollar and % variance. Also, please calculate the average cost per renewal and move-in. We will have time during your zoom interview to discuss your findings and think about next steps. This will be a report reviewed by senior management of the company, accordingly please make sure you focus on the presentation of your work as well as the accuracy of the analysis. A B D E F G H J K L M N o P 4 5 6 7 Starting Occupancy % Ending Occupancy % Change in occupied homes Cost per Cost per Total cost of Total cost of Renewals 8 Period Homes Renewal % Renewals Move-Outs Move-Ins Move-Ins Move-Ins 95% Total cost of Redecorating 0 0 Expirations 35 30 25 400 400 400 0 Renewals 500 550 55% 55% 55% 2200 2250 2300 0 0 0 0 0 600 9 Oct-21 10 Nov-21 11 Dec-21 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Baseline Occupancy Data Expiration Data Region Key + A B D E F G H 1 2 3 4 MA 5 NE 6 NY 7 NC 8 SC 9 PNW 10 11 12 13 14 10-31-21 94.0% 95.0% 95.5% 94.0% 93.5% 91.0% 11-30-21 95.0% 93.5% 95.3% 93.0% 92.0% 93.0% 12-31-21 96.0% 95.0% 94.8% 90.0% 95.5% 94.0% Baseline Occupancy Data Expiration Data Region Key A B D E F G H H J K L Expirations 25 35 bs 8 8 8 8 8 8 8 15 45 20 40 45 1 2. 3 Region 4 MA 5 NC 6 NE 7 PNW 8 NY 9 SC 10 MA 11 NC 12 NE 13 PNW 14 NY 15 SC 16 MA 17 NC 18 NE 19 PNW 20 NY 21 SC 22 MA 23 NC 24 NE 25 PNW 28 NY 27 SC 28 MA 29 NC 30 NE 31 PNW 32 NY 33 SC 34 MA 35 NC 38 NE 37 PNW 38 NY 39 SC 40 MA 41 NC 42 NE 43 PNW 44 NY 45 SC 46 MA 47 NC 48 NE 49 PNW 50 NY 51 SC 52 MA 53 NC 54 NE 55 PNW 58 INY 57 SC 58 MA 59 NC 80 NE 61 PNW 62 NY 63 SC 64 MA 65 NC 88 NE 67 PNW 68 NY 69 SC 70 MA 71 NC 72 NE 73 PNW 74 NY 75 SC 76 77 7 Month 1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3 4 4 4 4 4 4 5 5 5 5 5 5 6 6 6. 6 6 6 7 7 7 7 7 7 8 8 8 8 8 8 9 9 9 9 9 9 10 10 10 10 10 10 11 11 11 11 11 11 12 12 12 12 12 12 YR 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 20211 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 50 25 35 45 50 25 55 30 45 55 60 35 65 40 55 65 70 45 75 50 65 75 80 55 85 60 60 70 75 50 80 55 50 60 65 40 70 45 45 55 60 35 65 40 40 50 55 30 60 35 35 45 50 25 55 30 30 8888 8888888888888 45 20 50 25 Baseline Occupancy Data Expiration Data Region Key + B A 1 MA = Mid-Atlantic 2 NE = New England 3 NY = Metro Ny/NJ 4 NC = Northern California 5 SC = Southern California 6 PNW = Pacific Northwest 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Baseline Occupancy Data Expiration Data Ready Region Key + ANALYSIS OF REDECORATING COSTS Purpose: In the beginning of each year, the company prepares an annual budget for Redecorating costs by community. Now that it is the middle of the year, you have been asked by the head of Property Operations to evaluate whether the budget for the remainder of the year is reasonable by preparing a forecast of anticipated costs for the fourth quarter. One of our analysts had started the exercise, but since they have called out sick we need your help to finish. Our concern is that actual redecorating costs to be incurred for the fourth quarter will be well above budget such that action items will need to be taken immediately to reduce future expenditures. Background Redecorating costs relate to costs incurred to get apartment homes ready for new occupants. Typical costs include painting, carpet replacement, and appliance repairs If a resident elects to renew, redecorating costs are very minor (touch-up painting, carpet washing, and replacement of small appliances) If the existing resident moves out, and is replaced by a new resident, then redecorating costs are much higher (full repainting by contractors, complete carpet replacement, and possible major appliance replacement). How to estimate Redecorating costs: Redecorating costs are influenced by the number of residents that renew their leases vs. the number of new residents that move-in during a given period. The number of "renewals" and "move-ins" can be determined based on the change in occupied homes during the period: Change in occupancy from the beginning of the month to the end of the month = O + Occupied Homes at the beginning of the month 0 - Resident leases that expire (i.e. these resident will either renew or move-out) o + Renewal leases o + New Move-Ins o = Occupied Homes at the end of the month Total redecorating costs per period = (Renewal leases x redec cost per renewal) + (New move-ins x redec costs per move-in) Exercise Calculate a forecast of 4Q 2021redecorating costs for a 400 apartment home community located in Southern California and compare the result to the budgeted redecorating costs for that period, which is $100,000. To help you complete the exercise, we've provided an Excel workbook that has four different tabs: TAB 1: "BASELINE - Use this template as a starting point for your model. The columns are as follows: Period. Track costs on a monthly basis for October 2021 - December 2021 in order to arrive at the total for the quarter. Homes. Assume a community of 400 apartment homes Occupancy. Based on feedback from the residential services team it is believed the occupancy at the end of the 3rd Quarter will be 95%. Assumptions for occupancy during October, November and December are included on TAB 2: "OCCUPANCY DATA". For your benefit, the individual regions are defined on TAB 4: "REGION KEY" Change in occupied homes. The percentage increase or decrease in occupied homes multiplied by the total number of homes will equal the change in occupied homes for the period. Expirations. Relates to the number of leases that end during the period. Assumptions for future expirations is provided on TAB 3: "EXPIRATION DATA". Renewal %. Relates to the percentage of residents whose leases expire who will choose to stay at the community by renewing their leases. Assume that the renewal % during each month of the quarter is 55%. Renewals. The number of residents that renew their leases in the period. Move-Outs. The number of residents that elect not to renew their lease and therefore move-out. Move-Ins. The number of new residents that move into the community. By definition, Move-ins will equal the net increase or decrease in occupied homes plus the number of move-outs during the period. Total cost of redecorating. Our purchasing department has provided the following projections for redecorating costs (per Move-In and Renewal): Month October November December Redec Cost per Individual Move-in $2,200 $2,250 $2,300 Redec Cost per Individual Renewal $500 $550 $600 Please calculate the forecasted Redecorating cost for this community using this template and compare the amount to budget ($100,000 for the quarter) both in terms of a dollar and % variance. Also, please calculate the average cost per renewal and move-in. We will have time during your zoom interview to discuss your findings and think about next steps. This will be a report reviewed by senior management of the company, accordingly please make sure you focus on the presentation of your work as well as the accuracy of the analysis. A B D E F G H J K L M N o P 4 5 6 7 Starting Occupancy % Ending Occupancy % Change in occupied homes Cost per Cost per Total cost of Total cost of Renewals 8 Period Homes Renewal % Renewals Move-Outs Move-Ins Move-Ins Move-Ins 95% Total cost of Redecorating 0 0 Expirations 35 30 25 400 400 400 0 Renewals 500 550 55% 55% 55% 2200 2250 2300 0 0 0 0 0 600 9 Oct-21 10 Nov-21 11 Dec-21 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Baseline Occupancy Data Expiration Data Region Key + A B D E F G H 1 2 3 4 MA 5 NE 6 NY 7 NC 8 SC 9 PNW 10 11 12 13 14 10-31-21 94.0% 95.0% 95.5% 94.0% 93.5% 91.0% 11-30-21 95.0% 93.5% 95.3% 93.0% 92.0% 93.0% 12-31-21 96.0% 95.0% 94.8% 90.0% 95.5% 94.0% Baseline Occupancy Data Expiration Data Region Key A B D E F G H H J K L Expirations 25 35 bs 8 8 8 8 8 8 8 15 45 20 40 45 1 2. 3 Region 4 MA 5 NC 6 NE 7 PNW 8 NY 9 SC 10 MA 11 NC 12 NE 13 PNW 14 NY 15 SC 16 MA 17 NC 18 NE 19 PNW 20 NY 21 SC 22 MA 23 NC 24 NE 25 PNW 28 NY 27 SC 28 MA 29 NC 30 NE 31 PNW 32 NY 33 SC 34 MA 35 NC 38 NE 37 PNW 38 NY 39 SC 40 MA 41 NC 42 NE 43 PNW 44 NY 45 SC 46 MA 47 NC 48 NE 49 PNW 50 NY 51 SC 52 MA 53 NC 54 NE 55 PNW 58 INY 57 SC 58 MA 59 NC 80 NE 61 PNW 62 NY 63 SC 64 MA 65 NC 88 NE 67 PNW 68 NY 69 SC 70 MA 71 NC 72 NE 73 PNW 74 NY 75 SC 76 77 7 Month 1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3 4 4 4 4 4 4 5 5 5 5 5 5 6 6 6. 6 6 6 7 7 7 7 7 7 8 8 8 8 8 8 9 9 9 9 9 9 10 10 10 10 10 10 11 11 11 11 11 11 12 12 12 12 12 12 YR 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 20211 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 50 25 35 45 50 25 55 30 45 55 60 35 65 40 55 65 70 45 75 50 65 75 80 55 85 60 60 70 75 50 80 55 50 60 65 40 70 45 45 55 60 35 65 40 40 50 55 30 60 35 35 45 50 25 55 30 30 8888 8888888888888 45 20 50 25 Baseline Occupancy Data Expiration Data Region Key + B A 1 MA = Mid-Atlantic 2 NE = New England 3 NY = Metro Ny/NJ 4 NC = Northern California 5 SC = Southern California 6 PNW = Pacific Northwest 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Baseline Occupancy Data Expiration Data Ready Region Key +