Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
Please help me solve these excel questions and give valid formulas for each: Scenario modelling setup ( THE LAST IMAGE IS SHEETS TABLE WHICH IS
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 Open your Google Sheet from the last lab, then make a new sheet on the bottom left, press the icon, then doubleclick on the sheet tab to rename it as "Part Then, from your original sheet, copy the data, ie cells A:L and paste them into the second row of Part ie click in A then paste. Then paste them again to row and make the result look like Slide Insert a column before revenue and calculate conversion rate: visits are analogous to impressions, and transactions are, obviously, conversions. To do this, rightclick on the column header to get a context menu, and choose Insert 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, eg 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. In cell Q type and in Q type Then, in Scenario, make visits a function of visits in Baseline Q: in C type $ and drag down copy that formula to the relevant cells. Remember that the $ character "anchors" so it does change when you copy the formula. Next., make Scenario CR equal to Baseline CR: in G type GQ$ and copy down. Then make Scenario transactions a function of Scenario visits and Scenario CR: in F type C G and drag down. Note we don't want to anchor the row this time; ensure you understand why. But revenue is unchanged, so we need to fix that. Make Scenario revenue a function of Scenario transactions & Baseline revtran 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. Now set Q to and Q to ; you should see the extra revenue from improving CR Check your results against Slide and add the extra content shown. Scenario modelling setup: Step SA Answers File Edit View Insert Format Data Tools Extensions Help tableABcDEFGHI,JLmNBaselinechannel,device,visits,views,time,transactions,CRrevenue,revtranFactor,Level,Sum,per trans,affiliate,desktop,$$Channel,affiliate,$$affiliate,mobile,$cpc$cpcdesktop,$cpm$cpcmobile,$organic,$cpmdesktop,$referral,$cpmmobile,$Total,,$organic,desktop,$organic,mobile,$referral,desktop,$Device,desktop,$referral,mobile,$mobile,$$Total,,,,Scenariochannel,device,visits,views,time,transactions,CRrevenue,revtranFactor,Level,Sum,ROAS,affiliate,desktop,$$Channel,affiliate,$affiliate,mobile,$cpc$cpcdesktop,$cpm$cpcmobile,$organic,$cpmdesktop,$referral,$cpmmobile,$Total,,$organic,desktop,$Porganic,mobile,$referral,desktop,$Device,desktop,,,referral,mobile,$$mobile,,,$Total,,$ Ignore this here and in the next screenshot also Scenario modelling setup: Step CR To check whether you're getting the right answer, add the numbers in your answers Q:Q up ie sum them and you should get: $ Note: this number has no meaning, it's just a way to check things, and obviously is not perfect, ie 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
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
Open your Google Sheet from the last lab, then make a new sheet on the bottom left, press the icon, then doubleclick on the
sheet tab to rename it as "Part Then, from your original sheet, copy the data, ie cells A:L and paste them into the second
row of Part ie click in A then paste. Then paste them again to row and make the result look like Slide
Insert a column before revenue and calculate conversion rate: visits are analogous to impressions, and transactions are, obviously,
conversions. To do this, rightclick on the column header to get a context menu, and choose Insert 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,
eg 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.
In cell Q type and in Q type Then, in Scenario, make visits a function of visits in Baseline Q: in C type
$ and drag down copy that formula to the relevant cells. Remember that the $ character "anchors" so it does
change when you copy the formula. Next., make Scenario CR equal to Baseline CR: in G type GQ$ and copy down.
Then make Scenario transactions a function of Scenario visits and Scenario CR: in F type C G and drag down. Note
we don't want to anchor the row this time; ensure you understand why.
But revenue is unchanged, so we need to fix that. Make Scenario revenue a function of Scenario transactions & Baseline
revtran 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.
Now set Q to and Q to ; you should see the extra revenue from improving CR Check your results against Slide
and add the extra content shown. Scenario modelling setup: Step
SA Answers
File Edit View Insert Format Data Tools Extensions Help
tableABcDEFGHI,JLmNBaselinechannel,device,visits,views,time,transactions,CRrevenue,revtranFactor,Level,Sum,per trans,affiliate,desktop,$$Channel,affiliate,$$affiliate,mobile,$cpc$cpcdesktop,$cpm$cpcmobile,$organic,$cpmdesktop,$referral,$cpmmobile,$Total,,$organic,desktop,$organic,mobile,$referral,desktop,$Device,desktop,$referral,mobile,$mobile,$$Total,,,,Scenariochannel,device,visits,views,time,transactions,CRrevenue,revtranFactor,Level,Sum,ROAS,affiliate,desktop,$$Channel,affiliate,$affiliate,mobile,$cpc$cpcdesktop,$cpm$cpcmobile,$organic,$cpmdesktop,$referral,$cpmmobile,$Total,,$organic,desktop,$Porganic,mobile,$referral,desktop,$Device,desktop,,,referral,mobile,$$mobile,,,$Total,,$
Ignore this here and in the next screenshot also Scenario modelling setup: Step CR
To check whether you're
getting the right answer,
add the numbers in your
answers Q:Q up ie
sum them and you should
get:
$
Note: this number has no
meaning, it's just a way to
check things, and
obviously is not perfect, ie
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started