Scenario modelling setup: Step $1$

Open your Google Sheet from the last lab, then make a new sheet $($on the bottom left, press the $+$ icon, then double$-$click on the

sheet tab to rename it as "Part $2").$ Then, from your original sheet, copy the data, i$.$e$.$ cells A$1$:L$12,$ and paste them into the second

row of Part $2,$ i$.$e$.$ click in A$2$ then paste. Then paste them again to row $16$ and make the result look like Slide $6.$

Insert a column before revenue and calculate conversion rate: visits are analogous to impressions, and transactions are, obviously,

conversions. To do this, right$-$click on the column header to get a context menu, and choose Insert $1$ column left. Then insert a

column after revenue and calculate revenue per transaction. Note: when you create a new column, it may be in the wrong format,

e$.$g$.$ as $\%$ when you want $ or just a number. So if things look weird, ensure the format is appropriate.

We're going to calculate what would happen if we could change conversion rate, and also if we could get more visits. To do this, we

need to make transactions a function of visits and CR$,$ and revenue a function of transactions and revenue per transaction.

$3\mathrm{.}1.$ In cell Q$8,$ type $100$ and in Q$9$ type $1.$ Then, in Scenario, make visits a function of visits in Baseline $+$ Q$8$: in C$17$ type

$=C3+Q$$$8$ and drag down copy that formula to the relevant cells. Remember that the $ character "anchors" $12,$ so it does

change when you copy the formula. Next., make Scenario CR equal to Baseline CR: in G$17$ type $=$G$3*$Q$$9$ and copy down.

Then make Scenario transactions a function of Scenario visits and Scenario CR: in F$17$ type $=$ C ${?}^{****}$ G$17$ and drag down. Note

we don't want to anchor the row this time; ensure you understand why.

$3\mathrm{.}2.$ But revenue is unchanged, so we need to fix that. Make Scenario revenue a function of Scenario transactions & Baseline

rev$/$tran$.$ Based on what you just did, you should not need instructions on how to do this. Do that, and you will see the extra

revenue from those extra visits, and hence transactions.

$3\mathrm{.}3.$ Now set Q$8$ to $0$ and Q$9$ to $1\mathrm{.}5$ ; you should see the extra revenue from improving CR$.$ Check your results against Slide $7,$

and add the extra content shown. Scenario modelling setup: Step $2$

You may have noticed there is big mistake in Slide $6$: the totals per channel and per device are wrong. This is because in Part $1,$ we

calculated sums over entire columns using the column:column notation. Now, we want to sum only over specific rows, i$.$e$.$ rows

$3$:$12$ for Baseline and $17$:$26$ for Scenario. So fix that by editing the formulas, e$.$g$.$ in M$3$ changing $=$sumif$($A:A$,$ L$3,$ H:H$)$ to

$=$ sumif$(A$$$3$:$A$$$12,L3,H$$$3$:$H$$$12$ and copying down.

Once you've done that, you will see that the totals in Baseline are the same as in your Part $1$ sheet, and that the Scenario channel

and device totals are greater, reflecting the improved conversion rate. Now we can calculate the lift, i$.$e$.$ improvement over

baseline, for channel and device totals.

Finally, in Q$10$ type N$.$ Then in Scenario CR$,$ in the Mobile rows, make CR half the CR of the row above if $Q10$ contains Y$.$ For

example, in $"$Y$",$ G$19W$$$9.$ Then change $Q10$ to $Y$ and observe the lift.

Now you're ready to address a marketing problem: You're working for the Marketing Manager of the Google Merchandise Store. Her

boss has put the hard word on her that her job is on the line unless she can improve the profitability of the store. She is considering asking

her boss for extra budget to enable extra profits. But she's not sure whether she will get that, and so want to know how she could improve

with no extra budget, but telling her LPO staff to up their game. She's asked her colleagues about their experience of getting extra money

and also their experience of range of improvement possible by concentrating on LPO. Based on this, she thinks she'll get $$100,000$ or

nothing. If nothing, then it's possible to increase CR by $20\%.$ Alternatively they could try to close the gap between Desktop and Mobile CR$.$

Additionally, if she gets the $$100,000$ to spend on search, display and affiliate, she knows that the cost of an extra hit on the eCommerce

store varies, so needs to calculate profit from extra visitors, not just revenue. So in the Costs table, calculate the Total cost of

How to find the ROAS in the "Scenario" table from the image provided as I have been trying but I dont know how to and the first percentage $(60\mathrm{.}23\%)$ was given by the professor but we need to find the formula to find others as well $($red blanks$)$ and please solve my table as alot of my answers are coming wrong.