\begin{tabular}{|l|l|l|l|l|} \hline Profit pn/(1+D)n & & & & \\ \hline CLV per Customer & & & & \\ \hline \end{tabular} 1. \begin{tabular}{|l|l|} \hline 4 & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline 4 & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Q1. How much do you need to deposit now in order to have $5,000 in 5 years with an interest rate of 4% Q2. Suppose that you receive a series of five monthly payments of $4000 starting one month from toda) What is the present value of the offer if the interest rate is 10% compounded annually? Q 3. Create an Excel spreadsheet using Figure 6.1 (p.137) in the assigned reading. Create the appropriate Excel function to calculate the regular CLV. (5 points) Q4. Simplified CLV \begin{tabular}{|l|l|} \hline Revenue & \\ \hline Contribution Margin & \\ \hline Average annual cost of mailing & \\ \hline Response rate of the catalog & \\ \hline Acquisition Cost & \\ \hline Gross margin & \\ \hline Retention rate & \\ \hline Discount Rate & \\ \hline S-CLV & \\ \hline \end{tabular} Q5. \begin{tabular}{|l|r|r|r|r|} \hline CLV & & & & \\ \hline Year & & 1 & 2 & 3 \\ \hline Acquisition Costs & & & & \\ \hline Average Revenues & & & & \\ \hline Average Costs & & & & \\ \hline Expected NCF & & & & \\ \hline Expected NCF* pn/(1+D)n & & & & \\ \hline CLV & & & & \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline Retention Rate & \\ \hline Annual discount rate & \\ \hline \end{tabular} Q6. \begin{tabular}{|l|r|r|r|r|} \hline & 0 & 1 & 2 & 3 \\ \hline Total Number of Customers & 100,000 & 120,000 & 140,000 & 160,000 \\ \hline New Customers & & 40,000 & 50,000 & 62,000 \\ \hline Retention Rate & & & & \\ \hline Average Orders per Year & & 1.8 & 2.5 & 3 \\ \hline Average Order Size & & $90 & $95 & $100 \\ \hline Total Revenue Per Customer & & & & \\ \hline Individual Cost of Sales Rate & & & 65% & 60% \\ \hline Individual Cost of Sales Per Customer & & & & \\ \hline Contribution Margin & & $5,500,000 & $1,200,000 & $840,000 \\ \hline Total Marketing Costs(M) & & & & \\ \hline Marketing Cost Per Customer & & & & \\ \hline Profit (=Expected NCF) & & & & \\ \hline \end{tabular} \begin{tabular}{|r|r|r|r|r|r|r|} \hline & & & & & & \\ \hline \end{tabular}