Table for Calculating the Profitability of an Online Game Store

We require a skilled data analyst to create a detailed Google Sheets table for calculating the profitability of an online game store specializing in sales of games, including denominations for PUBG, prepaid services, and gift cards. The table should consider various economic indicators, including quarterly revenue, development budget, product catalog, payment system commissions, aggregator commissions, approximate site traffic, and average purchase value.

Key Requirements:

Creation of a structured table for inputting initial data on revenues, expenses, average purchase value, and other economic indicators.
Development of formulas for the automatic calculation of net profit, taking into account all variable and fixed costs, as well as the average purchase value.
Inclusion of scenario analysis to assess profitability at different levels of traffic, conversion rates, and changes in average purchase value.
Provision of charts and diagrams for a visual representation of financial results and the dynamics of the average purchase value.
Ensuring the scalability of the table to add new products and change commissions without losing functionality.



- Adding an offer: 0.15 euro-valid for 4 year
- Withdrawals: depending on the chosen method on average: 1% + 1.5 euro.
- payment system: 3-5% for transaction

reference:
g2a.com
https://steam.kupikod.com/
https://gabestore.ru/top
https://www.seagm.com/ru-ru


Additional Variables to Include:

Advertising budget
Cost of acquiring a new customer
Number of new customers
Retention costs
Retention budget
Total marketing budget
Active customers (by the end of the period)
Number of inactive customers
Percentage of purchasing customers from the base
Number of purchases per customer
Total repeat orders
Percentage of repeat orders
Number of orders processed
Percentage of cancellations
Number of completed orders
Number of orders coming to the store from the website
Average purchase value
Total sales
Cost of goods sold (determined by markup)
Operational expenses
Fixed expenses
Total profit
Balance
Break-even point
Months to break-even
Cost of the online store in tenge
Initial marketing investments in tenge

Settings for Calculations:

Markup percentage (how much we add to the product cost)
Average purchase value in tenge
Quarterly growth of the average purchase value
Market volume (total number of interested people), target audience size
Word-of-mouth index (natural quarterly growth of popularity)
Percentage of repeat customers (per quarter)
Number of purchases per quarter for a repeat customer
Stimulated sales of the physical store as a percentage of online
Percentage of cancellations
Operational expenses (percentage of turnover)
Fixed monthly expenses
Monthly advertising expenses
Cost of attracting one visitor with SEO after reaching the top
Cost of attracting one visitor with CPC advertising, starting price (in the region of 10-30 tenge)
Cost per thousand banner impressions
Percentage of the YouTube budget
Percentage of the CPC budget
The rest on SEO

Financial Results:

Required investment volume in tenge
Payback period in months
Net profit for 2 years in tenge

Overall budget for online advertising

Average cost of acquisition
Number of new customers

YouTube Advertising:

Number of unique visitors
Conversion rate
Cost of acquiring a customer
New customers from search engines

Contextual Advertising and Other CPC:

CPC
Number of unique visitors
Conversion rate
Cost of acquisition
New customers from context

Banner Advertising Budget, Pay-Per-View:

Cost per thousand impressions
CTR
Cost per click
Incoming traffic
Conversion rate
Cost of acquisition
New customers through banners