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'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 cor 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. 1. Calculate Marty's 5-year "lifetime" customer value. 2. If Marty stayed with the bank for only two years, and made no referrals, what would her value to SFCB be? 3. 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.)