Please help me solve these excel questions and give valid formulas for each: Scenario modelling setup $($THE LAST IMAGE IS SHEETS TABLE WHICH IS HALF SOLVED$)$: 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 $1\mathrm{.}1$

SA$3(2024)$ Answers $0$

File Edit View Insert Format Data Tools Extensions Help

$\backslash$table$[[,$A$,$B$,$c$,$D$,$E$,$F$,$G$,$H$,$I,J$,,$L$,$m$,$N$,1],[1,$Baseline$],[2,$channel,device,visits,views,time,transactions,CR$,$revenue,rev$/$tran$,,$Factor,Level,Sum,per trans,$],[3,$affiliate,desktop,$14,832,41,740,36,436\mathrm{.}60,7,0\mathrm{.}05\%,$$$5,834,$$$833,,$Channel,affiliate,$$5,974,$$$328,],[4,$affiliate,mobile,$1,363,5,072,2,723\mathrm{.}90,2,,$$$140,,,,$cpc$,$$$239,406,,],[5,$cpc$,$desktop,$6,578,41,434,22,902\mathrm{.}30,184,,$$$207,685,,,,$cpm$,$$$782,682,,],[6,$cpc$,$mobile,$5,495,26,158,15,426\mathrm{.}30,56,,$$$31,720,,,,$organic,$$2,014,690,,],[7,$cpm$,$desktop,$4,510,26,435,15,251\mathrm{.}20,140,,$$$774,725,,,,$referral,$$406,651,,],[8,$cpm$,$mobile,$1,515,6,618,4,080\mathrm{.}50,10,,$$$7,957,,,$Total,,$$3,449,402,,],[9,$organic,desktop,$150,875,650,983,389,889\mathrm{.}20,1,863,,$$$1,908,871,,,,,,,],[10,$organic,mobile,$72,620,253,324,141,666\mathrm{.}10,291,,$$$105,819,,,,,,,],[11,$referral,desktop,$229,986,439,875,197,361\mathrm{.}30,306,,$$$400,702,,,$Device,desktop,$$3,297,817,,],[12,$referral,mobile,$27,630,75,441,40,383\mathrm{.}70,10,,$$$5,949,,,,$mobile,$$151,585,,],[13,,,515,404,,,2,869,,$$$3,449,402,,,$Total,,,,$],[14],[15,$Scenario$],[16,$channel,device,visits,views,time,transactions,CR$,$revenue,rev$/$tran$,,$Factor,Level,Sum,ROAS,$],[17,$affiliate,desktop,$14,932,41,740,36,436\mathrm{.}60,7,0\mathrm{.}05\%,$$$5,873,$$$833,,$Channel,affiliate,$$6,023,,],[18,$affiliate,mobile,$1,463,5,072,2,723\mathrm{.}90,2,,$$$150,,,,$cpc$,$$$243,140,,],[19,$cpc$,$desktop,$6,678,41,434,22,902\mathrm{.}30,187,,$$$210,843,,,,$cpm$,$$$800,385,,],[20,$cpc$,$mobile,$5,595,26,158,15,426\mathrm{.}30,57,,$$$32,298,,,,$organic,$$2,016,101,,],[21,$cpm$,$desktop,$4,610,26,435,15,251\mathrm{.}20,143,,$$$791,903,,,,$referral,$$406,846,,],[22,$cpm$,$mobile,$1,615,6,618,4,080\mathrm{.}50,11,,$$$8,482,,,$Total,,$$3,472,496,,1],[23,$organic,desktop,$150,975,650,983,389,889\mathrm{.}20,1,864,,$$$1,910,136,,,,,,,$P$],[24,$organic,mobile,$72,720,253,324,141,666\mathrm{.}10,291,,$$$105,965,,,,,,,],[25,$referral,desktop,$230,086,439,875,197,361\mathrm{.}30,306,,$$$400,876,,,$Device,desktop,,,$],[26,$referral,mobile,$27,730,75,441,40,383\mathrm{.}70,10,,$$$5,971,$$$595,,,$mobile,,,$],[27,,,,,,,,$$$3,472,496,,,$Total,,$$3,472,496,,]]$

Ignore this here and in the next screenshot also Scenario modelling setup: Step $1\mathrm{.}3\mathrm{.}3($CR$)$

To check whether you're

getting the right answer,

add the numbers in your

answers $($Q$2$:Q$5)$ up $($i$.$e$.$

sum them$)$ and you should

get:

$$1,063,009$

Note: this number has no

meaning, it's just a way to

check things, and

obviously is not perfect, i$.$e$.$

it won't definitely tell you if

all four numbers are

correct, but it will tell you if

at least one is incorrect.

If you want to check your

answers are actually all

correct, go

here, paste them in $($one