{"id":2051,"date":"2022-04-23T17:39:59","date_gmt":"2022-04-23T10:39:59","guid":{"rendered":"https:\/\/mintea.blog\/?p=2051"},"modified":"2022-04-23T17:41:34","modified_gmt":"2022-04-23T10:41:34","slug":"2051","status":"publish","type":"post","link":"https:\/\/mintea.blog\/?p=2051","title":{"rendered":"Free CLV Excel Template"},"content":{"rendered":"

Free CLV Excel Template<\/strong><\/p>\n

Free CLV Excel Template (free-clv-template-for-download)<\/a><\/p>\n

How to Use the Free Customer Lifetime Value (CLV) Template<\/strong><\/p>\n

There are four steps to follow on the spreadsheet. When you open the CLV template (after downloading the file from the above link) you should have a spreadsheet that looks like this\u2026<\/p>\n

\"image<\/p>\n

Step One: Enter Acquisition Costs PER New Customer<\/strong><\/p>\n

In this step, you need to enter two numbers in the gold cells. The first number needed is the\u00a0total spend on new customer acquisition<\/a>.<\/p>\n

Directly under that number, you need to input the number of new customers acquired. The new customer acquisition cost is then automatically calculated.<\/p>\n

As it says in the note on the side, if you already know the average acquisition cost, then simply enter it as the total promotional spend and then set the number of new customers to 1.<\/p>\n

Step Two: Select and Enter the Discount Rate<\/strong><\/p>\n

\"Customer<\/p>\n

Step two is an optional, but recommended, step in the calculation of customer lifetime value. Most firms will have an average return on their investments, or possibly use a hurdle rate to evaluate new investment opportunities \u2013 this rate should be used.<\/p>\n

If this is not available, usually as a rate of 10 to 20% is appropriate depending upon the investment return required. Please review articles on\u00a0discount rates<\/a>.<\/p>\n

\u00a0Step Three: Enter customer revenues and costs<\/strong><\/p>\n

\"Image<\/p>\n

As you can see from the above diagram, there are four rows to complete \u2013 the first is customer revenues, which is the total income received from the average customer across all products and services.<\/p>\n

The average customer product cost (underneath) is the variable cost required to provide those products and services to the average customer.<\/p>\n

Customer revenue less the average customer product costs automatically calculates the gross profit contribution per customer each year (in the first blue row).<\/p>\n

Underneath the gross profit contribution there are two more rows of costs. The first is for any loyalty\/retention costs and any up selling costs. Basically, these are the costs of marketing investments aimed at existing customers in order to grow and hold the business.Please refer to the article on\u00a0retention goals<\/a>.<\/p>\n

The final row is actually a cost saving \u2013 which is appropriate to use when the brand has supporting customers that refer new business (non-customers) \u2013 so word-of-mouth (WOM) actually saves the firm money by reducing\u00a0acquisition costs<\/a>. Please refer to the article on\u00a0word-of-mouth cost savings<\/a>.<\/p>\n

Step Four: Enter customer retention rate each year<\/strong><\/p>\n

\"Customer<\/p>\n

For the fourth step in using the free CLV template, you only need to enter the annual customer retention (or loyalty) rate. The\u00a0customer lifetime in years<\/a>\u00a0is then automatically calculated for you. It is more likely that a firm would know its retention or loyalty rate, rather than its lifetime period (in years) for customers.<\/p>\n

A formula can generally be used to calculate the second number. The formula is 1\/(1 \u2013 retention rate). For example, if the retention rate was 80%, then the formula would be 1\/(1-0.8) = 1\/0.2 = 5 years.<\/p>\n

In the spreadsheet calculation, customer retention rate is used as an estimate of probability of receiving the future customer cash flows (that is, revenues and costs).<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Free CLV Excel Template Free CLV Excel Template (free-clv-template-for-download) How to Use the Free Customer Lifetime Value (CLV) Template There are four steps to follow on the spreadsheet. When you open the CLV template (after downloading the file from the above link) you should have a spreadsheet that looks like this\u2026 Step One: Enter Acquisition … Continue reading Free CLV Excel Template<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1802,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[32,63,62,55,56,26,54,72],"class_list":["post-2051","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bookmarked-articles","tag-analytic","tag-clv","tag-crm","tag-customer-analytic","tag-customer-lifecycle","tag-data","tag-data-mining","tag-excel-template"],"_links":{"self":[{"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/posts\/2051","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mintea.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2051"}],"version-history":[{"count":5,"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/posts\/2051\/revisions"}],"predecessor-version":[{"id":2061,"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/posts\/2051\/revisions\/2061"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mintea.blog\/index.php?rest_route=\/wp\/v2\/media\/1802"}],"wp:attachment":[{"href":"https:\/\/mintea.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mintea.blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mintea.blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}