B 1 1 M r Category Type 1 F 2 3 H Base case city Houston Comparison city Lubbock H Living Factor 6.30% 28.20% 13.40% -4.60% 0.00% 33.6.21% HC 5 Food & Groceries Housing Health care Miscellaneous Online Location irrelevant Transportation Utilities M o 3.10% Spending Totals not including taxes and retirement contributions City Lubbock Monthly expenses City Houston Month City Houston COLA Monthly expenses Category $1.200 H H $0 H $40 H1 1 2 3 6 7 a 9 10 11 Expense Categories for a typical month 12 13 14 If rent fill out column D for rows in green area 15 15 Apartment 17 Non-rent costs associated with apartment lease 18 Preferred parking and amenity focs 19 Renter's insurance 20 21 I own fill out coluinn D for rows in blue are 22 23 Today's market value of home Purchase price 25 Proncrty taxes 25 Homcowner's insurance 27 Landscape, maintance, HOA and condo fecs 29 Assumed annual appreciation rate above inflation, if any 29 30 Loan balance of primary mortgage 31 Monthly payment 32 Year's remaining 33 Amount of additional monthly payment if any Housing Auto Transportation Fitness, Exercise, Personal Care and Pets | Shopping Food and Dining Entertainment Insurance unrelated to Auto or Home Ucilities Other expenses 4 Houston Annual $14,880 $9.720 $1.560 $4,500 $6,000 $240 $1.200 $4,440 $0 $1,240 $810 $130 $375 $500 $20 $100 $370 SD Lubbock Lubbock Monit Annual $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 5 6 7 8 9 Monthly expenses $0 H $0 H $0 H $0 H $0 H $0 H Total $3,515 $2,510 $0 $0 10 $0 $0 H $0 $0 Actual Spending Totals including taxes and retirement contributions Houston Lubbock Totalline 10 $42,540 $0 Taxes Retirement contributions Total Actual II 12 13 14 C D F H 1 ] L M $0 0 G Total Actual 14 $0 $0.0 $0 $0 0 Optimal MaxiFi-recommended Spending Totals Fixed spending Discretionary spending 15 16 Toul 17 Financial behavior change in spending line 17 - line 14 Monthly Expenses $100 T $35010 $360 T $OIT B 33 Amount of additional monthly payment if any 34 35 Loan balance of a second mortgage or home equity loan 38 Monthly payment 37 Years remaining 39 Amount of additional monthly payment if any 39 20 41 Monthly actual spending categories 42 43 Auto/Transportation 44 Auto insurance 45 Auto loan payments 45 Gasoline commuter transportation 27 Rideshare fees 20 49 Fitness, Exercise, Personal Care and Pets 50 Fitness membership 51 Golf Tennis and other atletic fees on a la carte basis 52 Massage Spa 52 Hair and beauty 54 Other club membership 55 Pharmacy 56 Any other recreation not addressed 57 Pet walker and act day care 5e Other pet expenses 59 60 Shopping 61 Clothing-basic 62 Clothing special occasion 63 Home furnishings 64 General merchandise spending (Walmart. Target, etc.) 65 $80 M $0 M $0 M 550 M $0M $0) HC $0 M $OM $0 M $300 M $0.M $0 M $75 M D B 66 Food and Dining 67 Delivery service meals and groceries 68 Groceries 69 Restaurants and bars $100F $200 F $200 F 70 $0 M $0 O $200 71 72 Entertainment 73 Art museums, theater and movie theaters 74 Online services (Music, Apps, Content) 75 Streaming subscriptions 76 77 Insurance unrelated to Auto or Home 78 Health insurance premiums 79 Life insurance premiums 80 Umbrella liability insurance premiums $100 HC $0 0 $0 M 81 82 Utilities 83 Utilities: cable or satellite 84 Utilities: electric 85 Utilities: Internet 86 Utilities: land-line 87 Utilities: natural gas 88 Utilities: waste management and trash 89 Utilities: water and sewer 90 Utilities: wireless service 91 92 Categories not included in your budget 93 Add them here 94 Add them here 95 Add them here 96 Add them here 97 Add them here 98 Add them here $70 U $12010 $50 U $0 U $30 U $0 U $401U $60 O $0 $0 $0 $0 $0 $0 2. In this worksheet, you will see the beginnings of a worksheet that will estimate the expenses in two competing cities. The template has Houston and Lubbock. Below is a table of living factor differences between two cities across six living expense categories plus a miscellaneous category. The category type is the first letter of the category name. Living factor percentages come from Sperling's. G Category Type H Living Factor -6.30% -28.20% H HC M O Food & Groceries Housing Health care Miscellaneous Online/Location irrelevant Transportation Utilities 13.40% -4.60% 0.00% -38.60% -3.10% 3. To complete this workbook, your primary task is to complete column E beginning at row 16. Base case city Houston Comparison city Lubbock 10 11 Expense Categories for a typical month 12 13. 13 14 If rent: fill out column D for rows in green area 15 16 Apartment 17 Non-rent costs associated with apartment lease 18 Preferred parking and amenity fees 19 Renter's insurance City Houston Monthly expenses $1.200 H City COLA Lubbock Category Monthly expenses SOH $40 H 4. Each row in column E will be based on the base case information, e.g., Houston row column C multiplied by (I + cost-of-living factor), where the actual percentage used depends on the category type of expense. Example: The apartment cost in Lubbock will be C16* (1 - 0.282). The result is $862.60. 5. I want you to automate the inclusion of the cost-of-living factor for the calculate of the actual numbers in column E. To pull the correct living change from the table in the range F2:H8, use Excel's VLOOKUP() function. The apartment cost in Lubbock will have Excel syntax of the form something like =c16*(1 +vlookup (...)). 6. Make sure the formula appears in each cell in column E that is adjacent to a COLA category represented cell in column D. You can delete any cells in column E in which you see a result like #N/A or #Value. To verify, the total annual expenses for Lubbock should be $35,602 Case Study Trevor Stoneman is a 23-year old university graduate in sports management who has two new pending job offers as an "events coordinator. One job is located in Houston. The other in Boston. Trevor hails from Massachusetts but went to college in Texas. He never was a Tom Brady fan and has always loved the Cowboys. While Trevor has family in the Boston area he is indifferent to living in either city because of the economic opportunities in Texas. He does like the weather better in Texas, and expects it will be cheaper to live in Houston than Boston. Trevor needs your help in developing financial plans that can help him assess how his standard of living will differ between Houston and Boston. Trevor's job offer numbers are different. Event Planning 101 in Boston has offered Trevor an entry level salary of $82,000 with a 401(k) matching employee benefit retirement package. Event Planning 101 matches an employee up to 4% of annual salary and Trevor intends to contribute 4% to maximize the match. The Houston firm, Shade Tree Events, has offered Trevor a salary of $64,000 with a 401(k) plan that matches up to only 2% of annual salary. Like the Event Planning 101 job, Trevor would intend to maximize the match offered by ShadeTree. Trevor expects he would receive basic cost-of-living increases in salary so there would be 0% income growth assumed when constructing his financial plan in MaxiFi. Trevor needs your help in analyzing where he should live and wants to make his decision based on optimizing discretionary spending. Here are the additional details for Trevor needed to run the base case financial plan. Use Houston as the base case. Here are the details for Trevor. DOB, Max Age, Current Earnings, Retirement Age DOB: March 2, 1998 Max age: 95 Current earnings: $64,000 with 0% real income growth Retirement age: 69 Assume for Trevor: No applicable pension plans No applicable annuity Social Security Record We will assume Trevor has no past earnings subject to social security tax. You can mark this "complete." Trevor's Retirement Accounts Trevor will participate in an employer-based 401(k) plan at work in which he will contribute $1,280/annually in today's dollars and his employer will match with $1,280/annually in today's dollars. Retirement contributions will have 0% real growth. Currently Trevor has $0 in 401(k) assets. Like social security, Trevor intends on making withdrawals beginning at age 70 and ending at 95. You forecast Trevor will earn a 6.0% annual return over his lifetime.! Trevor's Home Trevor will rent an apartment at a cost of $1,200 per month and he has additional rental expenses of $40 (renter's insurance). Trevor's Regular Assets Trevor has a checking account with a current value of $5,000. Real Estate screen Set to Not applicable. 529 Accounts Set to Not applicable. Special Receipts screen Set to Not applicable. Reserve Funds Set to Not applicable. No reserve funds are going to be budgeted. Special Expenses Set to Not applicable Social Security Strategy. Set benefit filing date to month and year turns age 70 Settings and Assumptions Assure retirement account assets earn 6% and withdrawals begin at age 70 and end at age 95. Regular assets earn a 3% return. All other items within Settings and Assumptions remain at their default values Next Set of Tasks 1. Run the base case in MaxiFi for Trevor and have the Year at a Glance information handy. 2. After you have run the base case in MaxiFi, return to the Excel expense. 3. Go to Sperling's cost-of-living calculator to get living factor data to compare Houston and Boston. Set Houston as the "where do you live now" city. Pull the differences information into column H of the living factor table in the Excel worksheet. For housing use +85% for the difference in rent when in Boston v. Houston. 4. Return to MaxiFi. After you have both Houston and Boston expense numbers in your Excel workbook return to MaxiFi, go to "My Family" and add an Alternative Profile." Think about the MaxiFi-specific changes Trevor needs to make for Boston as an alternative profile. . Trevor lives in a different state Trevor's salary in Boston needs to be distinguished from his salary in Houston Trevor's contributions and his Boston employer's contributions need to be distinguished from Houston. Note that like salary, benefit contributions are matching inflation expectations so there will be a 0% increase in real contributions Housing costs need to be adjusted 5. Bring together your MaxiFi results for both Houston and Boston scenarios into your Excel workbook and summarize the optimal and actual spending. You will need to fill-in lines 12 thru 17 and the financial behavior change in spending cells for the annual information for both cities. ? Actual Spending Totals including taxes and retirement contributions Houston Boston Total line 10 $42.540 ? ? Retirement contributions 2 2 Total Actual ? 2 Taxes 11 12 13 14 Optimal MaxiFi-recommended Spending Totals Fixed spending Discretionary spending ? 15 16 > Total > 2 17 Financial behavior change in spending ? ? line 17 - line 14 Questions to Answer 1. What are Trevor's monthly expenses in Houston? In Boston? 2. What is Trevors base case living standard today if he elects Houston? In Boston? 3. By how much does Trevor have to change his current year spending to meet his optimal level of spending if he lives in Houston? 4. On living standard terms, is Houston or Boston better? What to Submit When you submit your Excel workbook to Canvas, through the Assignments tab, you will see a Submit Assignment link on the upper right-hand side once you open the assignment. You should upload your Excel file and in the "Comments text box type in your answer to the above questions for Trevor. Label your answers using the number of the corresponding question, e.g., la. monthly expenses in Houston; Ib. monthly expenses in Boston 2a. today's living standard in Houston 2b. today's living standard in Boston 3. your answer 4. your answer B 1 1 M r Category Type 1 F 2 3 H Base case city Houston Comparison city Lubbock H Living Factor 6.30% 28.20% 13.40% -4.60% 0.00% 33.6.21% HC 5 Food & Groceries Housing Health care Miscellaneous Online Location irrelevant Transportation Utilities M o 3.10% Spending Totals not including taxes and retirement contributions City Lubbock Monthly expenses City Houston Month City Houston COLA Monthly expenses Category $1.200 H H $0 H $40 H1 1 2 3 6 7 a 9 10 11 Expense Categories for a typical month 12 13 14 If rent fill out column D for rows in green area 15 15 Apartment 17 Non-rent costs associated with apartment lease 18 Preferred parking and amenity focs 19 Renter's insurance 20 21 I own fill out coluinn D for rows in blue are 22 23 Today's market value of home Purchase price 25 Proncrty taxes 25 Homcowner's insurance 27 Landscape, maintance, HOA and condo fecs 29 Assumed annual appreciation rate above inflation, if any 29 30 Loan balance of primary mortgage 31 Monthly payment 32 Year's remaining 33 Amount of additional monthly payment if any Housing Auto Transportation Fitness, Exercise, Personal Care and Pets | Shopping Food and Dining Entertainment Insurance unrelated to Auto or Home Ucilities Other expenses 4 Houston Annual $14,880 $9.720 $1.560 $4,500 $6,000 $240 $1.200 $4,440 $0 $1,240 $810 $130 $375 $500 $20 $100 $370 SD Lubbock Lubbock Monit Annual $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 5 6 7 8 9 Monthly expenses $0 H $0 H $0 H $0 H $0 H $0 H Total $3,515 $2,510 $0 $0 10 $0 $0 H $0 $0 Actual Spending Totals including taxes and retirement contributions Houston Lubbock Totalline 10 $42,540 $0 Taxes Retirement contributions Total Actual II 12 13 14 C D F H 1 ] L M $0 0 G Total Actual 14 $0 $0.0 $0 $0 0 Optimal MaxiFi-recommended Spending Totals Fixed spending Discretionary spending 15 16 Toul 17 Financial behavior change in spending line 17 - line 14 Monthly Expenses $100 T $35010 $360 T $OIT B 33 Amount of additional monthly payment if any 34 35 Loan balance of a second mortgage or home equity loan 38 Monthly payment 37 Years remaining 39 Amount of additional monthly payment if any 39 20 41 Monthly actual spending categories 42 43 Auto/Transportation 44 Auto insurance 45 Auto loan payments 45 Gasoline commuter transportation 27 Rideshare fees 20 49 Fitness, Exercise, Personal Care and Pets 50 Fitness membership 51 Golf Tennis and other atletic fees on a la carte basis 52 Massage Spa 52 Hair and beauty 54 Other club membership 55 Pharmacy 56 Any other recreation not addressed 57 Pet walker and act day care 5e Other pet expenses 59 60 Shopping 61 Clothing-basic 62 Clothing special occasion 63 Home furnishings 64 General merchandise spending (Walmart. Target, etc.) 65 $80 M $0 M $0 M 550 M $0M $0) HC $0 M $OM $0 M $300 M $0.M $0 M $75 M D B 66 Food and Dining 67 Delivery service meals and groceries 68 Groceries 69 Restaurants and bars $100F $200 F $200 F 70 $0 M $0 O $200 71 72 Entertainment 73 Art museums, theater and movie theaters 74 Online services (Music, Apps, Content) 75 Streaming subscriptions 76 77 Insurance unrelated to Auto or Home 78 Health insurance premiums 79 Life insurance premiums 80 Umbrella liability insurance premiums $100 HC $0 0 $0 M 81 82 Utilities 83 Utilities: cable or satellite 84 Utilities: electric 85 Utilities: Internet 86 Utilities: land-line 87 Utilities: natural gas 88 Utilities: waste management and trash 89 Utilities: water and sewer 90 Utilities: wireless service 91 92 Categories not included in your budget 93 Add them here 94 Add them here 95 Add them here 96 Add them here 97 Add them here 98 Add them here $70 U $12010 $50 U $0 U $30 U $0 U $401U $60 O $0 $0 $0 $0 $0 $0 2. In this worksheet, you will see the beginnings of a worksheet that will estimate the expenses in two competing cities. The template has Houston and Lubbock. Below is a table of living factor differences between two cities across six living expense categories plus a miscellaneous category. The category type is the first letter of the category name. Living factor percentages come from Sperling's. G Category Type H Living Factor -6.30% -28.20% H HC M O Food & Groceries Housing Health care Miscellaneous Online/Location irrelevant Transportation Utilities 13.40% -4.60% 0.00% -38.60% -3.10% 3. To complete this workbook, your primary task is to complete column E beginning at row 16. Base case city Houston Comparison city Lubbock 10 11 Expense Categories for a typical month 12 13. 13 14 If rent: fill out column D for rows in green area 15 16 Apartment 17 Non-rent costs associated with apartment lease 18 Preferred parking and amenity fees 19 Renter's insurance City Houston Monthly expenses $1.200 H City COLA Lubbock Category Monthly expenses SOH $40 H 4. Each row in column E will be based on the base case information, e.g., Houston row column C multiplied by (I + cost-of-living factor), where the actual percentage used depends on the category type of expense. Example: The apartment cost in Lubbock will be C16* (1 - 0.282). The result is $862.60. 5. I want you to automate the inclusion of the cost-of-living factor for the calculate of the actual numbers in column E. To pull the correct living change from the table in the range F2:H8, use Excel's VLOOKUP() function. The apartment cost in Lubbock will have Excel syntax of the form something like =c16*(1 +vlookup (...)). 6. Make sure the formula appears in each cell in column E that is adjacent to a COLA category represented cell in column D. You can delete any cells in column E in which you see a result like #N/A or #Value. To verify, the total annual expenses for Lubbock should be $35,602 Case Study Trevor Stoneman is a 23-year old university graduate in sports management who has two new pending job offers as an "events coordinator. One job is located in Houston. The other in Boston. Trevor hails from Massachusetts but went to college in Texas. He never was a Tom Brady fan and has always loved the Cowboys. While Trevor has family in the Boston area he is indifferent to living in either city because of the economic opportunities in Texas. He does like the weather better in Texas, and expects it will be cheaper to live in Houston than Boston. Trevor needs your help in developing financial plans that can help him assess how his standard of living will differ between Houston and Boston. Trevor's job offer numbers are different. Event Planning 101 in Boston has offered Trevor an entry level salary of $82,000 with a 401(k) matching employee benefit retirement package. Event Planning 101 matches an employee up to 4% of annual salary and Trevor intends to contribute 4% to maximize the match. The Houston firm, Shade Tree Events, has offered Trevor a salary of $64,000 with a 401(k) plan that matches up to only 2% of annual salary. Like the Event Planning 101 job, Trevor would intend to maximize the match offered by ShadeTree. Trevor expects he would receive basic cost-of-living increases in salary so there would be 0% income growth assumed when constructing his financial plan in MaxiFi. Trevor needs your help in analyzing where he should live and wants to make his decision based on optimizing discretionary spending. Here are the additional details for Trevor needed to run the base case financial plan. Use Houston as the base case. Here are the details for Trevor. DOB, Max Age, Current Earnings, Retirement Age DOB: March 2, 1998 Max age: 95 Current earnings: $64,000 with 0% real income growth Retirement age: 69 Assume for Trevor: No applicable pension plans No applicable annuity Social Security Record We will assume Trevor has no past earnings subject to social security tax. You can mark this "complete." Trevor's Retirement Accounts Trevor will participate in an employer-based 401(k) plan at work in which he will contribute $1,280/annually in today's dollars and his employer will match with $1,280/annually in today's dollars. Retirement contributions will have 0% real growth. Currently Trevor has $0 in 401(k) assets. Like social security, Trevor intends on making withdrawals beginning at age 70 and ending at 95. You forecast Trevor will earn a 6.0% annual return over his lifetime.! Trevor's Home Trevor will rent an apartment at a cost of $1,200 per month and he has additional rental expenses of $40 (renter's insurance). Trevor's Regular Assets Trevor has a checking account with a current value of $5,000. Real Estate screen Set to Not applicable. 529 Accounts Set to Not applicable. Special Receipts screen Set to Not applicable. Reserve Funds Set to Not applicable. No reserve funds are going to be budgeted. Special Expenses Set to Not applicable Social Security Strategy. Set benefit filing date to month and year turns age 70 Settings and Assumptions Assure retirement account assets earn 6% and withdrawals begin at age 70 and end at age 95. Regular assets earn a 3% return. All other items within Settings and Assumptions remain at their default values Next Set of Tasks 1. Run the base case in MaxiFi for Trevor and have the Year at a Glance information handy. 2. After you have run the base case in MaxiFi, return to the Excel expense. 3. Go to Sperling's cost-of-living calculator to get living factor data to compare Houston and Boston. Set Houston as the "where do you live now" city. Pull the differences information into column H of the living factor table in the Excel worksheet. For housing use +85% for the difference in rent when in Boston v. Houston. 4. Return to MaxiFi. After you have both Houston and Boston expense numbers in your Excel workbook return to MaxiFi, go to "My Family" and add an Alternative Profile." Think about the MaxiFi-specific changes Trevor needs to make for Boston as an alternative profile. . Trevor lives in a different state Trevor's salary in Boston needs to be distinguished from his salary in Houston Trevor's contributions and his Boston employer's contributions need to be distinguished from Houston. Note that like salary, benefit contributions are matching inflation expectations so there will be a 0% increase in real contributions Housing costs need to be adjusted 5. Bring together your MaxiFi results for both Houston and Boston scenarios into your Excel workbook and summarize the optimal and actual spending. You will need to fill-in lines 12 thru 17 and the financial behavior change in spending cells for the annual information for both cities. ? Actual Spending Totals including taxes and retirement contributions Houston Boston Total line 10 $42.540 ? ? Retirement contributions 2 2 Total Actual ? 2 Taxes 11 12 13 14 Optimal MaxiFi-recommended Spending Totals Fixed spending Discretionary spending ? 15 16 > Total > 2 17 Financial behavior change in spending ? ? line 17 - line 14 Questions to Answer 1. What are Trevor's monthly expenses in Houston? In Boston? 2. What is Trevors base case living standard today if he elects Houston? In Boston? 3. By how much does Trevor have to change his current year spending to meet his optimal level of spending if he lives in Houston? 4. On living standard terms, is Houston or Boston better? What to Submit When you submit your Excel workbook to Canvas, through the Assignments tab, you will see a Submit Assignment link on the upper right-hand side once you open the assignment. You should upload your Excel file and in the "Comments text box type in your answer to the above questions for Trevor. Label your answers using the number of the corresponding question, e.g., la. monthly expenses in Houston; Ib. monthly expenses in Boston 2a. today's living standard in Houston 2b. today's living standard in Boston 3. your answer 4. your