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 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.

$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.$)$