How do you turn this into an LTV table that includes total annual Revenues and Gross Profit, Number and Percentage of rounds, revenues and profits from the membership program?

General Golfers Regular Members $99 13/5 3,240 $ 240 $ 120 $ 50 Club Members $5,900 140/24 10,660 8,500 $ 340,000 $975 $600 $ 34,000 AA Membership Ave. Rounds Total Rounds of Golf Played 2021 Green/cart fees (100% gross profit) Guest Green Fees (100% gross profit) Food and Beverage (est) (50% gross profit) Pro Shop $ (est) (50% gross profit) Events (est) (50% gross profit) Total Revenues Total Profits 2019 members (Year End) 2020 members (Year End) 2021 members (Year End) 2019 $ (est.) Revenues 2020 $ (est.) Revenues 2021 $ (est.) Revenues 2019 membership fees (est.) 2020 membership fees (est.) 2021 membership fees (est.) 2019 $ (est.) Profits 2020 $ (est.) Profits 2021 $ (est.) Profits % of Profits Total Profits 2021 $ 42,500 $ 30 $ 10,000 $ 20 $ 50.18 $559 $509 200 170 180 $111,800 $95,030 $ 426,550 $100,620 $ 19,800 $ 16,830 $ 17,820 $101,800 $ 86,530 $ 383,250 $ 91,620 40.0% 9.5% $ 960,420 $500 $90 $8,065 $7,470 68 58 65 $548,420 $467,770 $524,225 $ 401,200 $ 342,200 $ 383,500 $ 507,960 $ 433,260 $ 485,550 50.5% Club Club Regular 200 Regular 170 68 58 2020 Beginning # of players card members (from 2019) Retained members New members Year end # of members Retention rate 115 55 170 58% 55 3 58 81% 2021 Beginning # of players card members (from 2020) Retained members New members Year end # of members Retention rate 108 72 180 64% 47 18 65 81% Retention Rate Regular Member Club Members Year 1 0.5 0.75 Year 2 Year 3 0.5 0.6 0.75 0.8 Year 4 Year 5 0.6 0.7 0.8 0.8 Year 6-10 0.7 0.85 First, review the financial data and make a report. Include all of the following: Total annual Revenues and Gross Profit, Number and Percentage of rounds, revenues and profits from the membership program (5 pts) Calculate the current LTVs for Regular and Club Members You will need to determine the most logical number of years and justify it. (Provide the LTV tables) (10 pts) Calculate the changes in LTV (Include the LTV tables) if: (1.) Spending goes up by 10% and retention goes up by 5% (2.) Spending goes up by 20% and retention goes up by 10% Leaky Bucket How many new Regular and Club Members are needed each year to maintain the current number of members for each group. (3 pts) Summarize the current financial situation. (2 pts) General Golfers Regular Members $99 13/5 3,240 $ 240 $ 120 $ 50 Club Members $5,900 140/24 10,660 8,500 $ 340,000 $975 $600 $ 34,000 AA Membership Ave. Rounds Total Rounds of Golf Played 2021 Green/cart fees (100% gross profit) Guest Green Fees (100% gross profit) Food and Beverage (est) (50% gross profit) Pro Shop $ (est) (50% gross profit) Events (est) (50% gross profit) Total Revenues Total Profits 2019 members (Year End) 2020 members (Year End) 2021 members (Year End) 2019 $ (est.) Revenues 2020 $ (est.) Revenues 2021 $ (est.) Revenues 2019 membership fees (est.) 2020 membership fees (est.) 2021 membership fees (est.) 2019 $ (est.) Profits 2020 $ (est.) Profits 2021 $ (est.) Profits % of Profits Total Profits 2021 $ 42,500 $ 30 $ 10,000 $ 20 $ 50.18 $559 $509 200 170 180 $111,800 $95,030 $ 426,550 $100,620 $ 19,800 $ 16,830 $ 17,820 $101,800 $ 86,530 $ 383,250 $ 91,620 40.0% 9.5% $ 960,420 $500 $90 $8,065 $7,470 68 58 65 $548,420 $467,770 $524,225 $ 401,200 $ 342,200 $ 383,500 $ 507,960 $ 433,260 $ 485,550 50.5% Club Club Regular 200 Regular 170 68 58 2020 Beginning # of players card members (from 2019) Retained members New members Year end # of members Retention rate 115 55 170 58% 55 3 58 81% 2021 Beginning # of players card members (from 2020) Retained members New members Year end # of members Retention rate 108 72 180 64% 47 18 65 81% Retention Rate Regular Member Club Members Year 1 0.5 0.75 Year 2 Year 3 0.5 0.6 0.75 0.8 Year 4 Year 5 0.6 0.7 0.8 0.8 Year 6-10 0.7 0.85 First, review the financial data and make a report. Include all of the following: Total annual Revenues and Gross Profit, Number and Percentage of rounds, revenues and profits from the membership program (5 pts) Calculate the current LTVs for Regular and Club Members You will need to determine the most logical number of years and justify it. (Provide the LTV tables) (10 pts) Calculate the changes in LTV (Include the LTV tables) if: (1.) Spending goes up by 10% and retention goes up by 5% (2.) Spending goes up by 20% and retention goes up by 10% Leaky Bucket How many new Regular and Club Members are needed each year to maintain the current number of members for each group. (3 pts) Summarize the current financial situation. (2 pts)