Assignment

Customer Lifetime Value Calculation

This is an individual assignment. Copying someone else's work is cheating.

Read the scenario below and follow the instructions at the end. You'll use Excel to create your answers and will upload an Excel spreadsheet.

In $2012,$ Marty was very unhappy with her bank $-$ the United Bank of San Jose $-$ and in January switched her banking relationship to the San Francisco Community Bank $($SFCB$).$

SFCB$\text{'}$s marketing department estimates it costs $$1,075$ on average in advertising to make a prospect aware of the bank and "convert" them to a customer.

When Marty moved her accounts $($including her checking, savings and car loan$)$ to SFCB$,$ it cost $$485$ for bank personnel to open her accounts, set her up on the system and transfer her deposit accounts and car loan from her previous bank.

Each year, it costs SFCB $$18$ per month to send Marty statements, process her checks and post her loan payments. It also costs SFCB $$150$ per year because they pay Marty interest on her savings account. In addition, SFCB spends $$65$ per year to create and mail her tax filing documents. $($All banks are required to provide this information to customers so customers can file their income taxes.$)$

Marty pays $$30$ per month in fees for her checking account and $$565$ per month in interest on her car loan. Marty occasionally sends wire transfers and does foreign currency transactions that the bank charges her fees for, which average $$45$ per year.

Marty has been very happy with SFCB and has referred $3$ other people to the bank. The bank estimates each referral is worth $$1,025$ in profit to the bank. $($The bank doesn't pay Marty for her referrals.$)$

Use Excel to complete answer the questions following.

Calculate Marty's $5-$year "lifetime" customer value.

If Marty stayed with the bank for only two years, and made no referrals, what would her value to SFCB be$?$

Bonus Question: Because interest paid on loans is reduced as principal is paid down, what would Marty's $5-$year customer value be if the amount of interest she paid on her car loan decreased $20\%$ each year after the first year? $($If you correctly answer #$1$ and #$2$ and get this calculation right, you'll earn $10$ extra credit points.$)$