Using Data Analytics $to$ Analyze Data $1$ Data analytics can $be$ used $to$ determine optimal pricing. Problem Visa operates Visa's Cookies and $is$ experimenting with pricing for a four$-$pack $of$ cookies she buys and resells $to$ convenience stores. She has tracked how many people buy the cookies $in$ a month $at$ various price levels $in$ order $to$ determine the optimal pricing. Monthly sales volume, costs and possible pricing amounts are provided here. Monthly sales volume, price, and costs All Scenarios Scenario $1$ Scenario $2$ Scenario $3$ Scenario $4$ Scenario $5$ Scenario $6$ Scenario $7$ Quantity $2001751501251007550$ Selling price $ $5\mathrm{.}00$ $ $7\mathrm{.}50$ $ $10\mathrm{.}00$ $ $12\mathrm{.}50$ $ $15\mathrm{.}00$ $ $17\mathrm{.}50$ $ $20\mathrm{.}00$ Product cost $ $3\mathrm{.}25$ per unit Commissions $0\mathrm{.}25$ per unit Admin costs $400\mathrm{.}00$ per month Instructions There are three parts $to$ this problem. Use Excel $to$ perform the following. $a.$ Prepare comparative traditional income statements for each $of$ the seven scenarios. Use conditional formatting $to$ flag the lowest and highest operating income. $b.$ Discuss the behavior $of$ the income statement components. Explain why the maximum operating profit level does not occur $at$ the maximum units sold. $c.$ Scenario $3$ and Scenario $5$ both generated the same revenue. Explain why the operating incomes for these scenarios differ?

Using Data Analytics $to$ Analyze Data

$1$ Data analytics can $be$ used $to$ determine optimal pricing.

Problem

Visa operates Visa's Cookies and $is$ experimenting with pricing for a four$-$pack $of$ cookies she buys and resells $to$ convenience stores.

She has tracked how many people buy the cookies $in$ a month $at$ various price levels $in$ order $to$ determine the optimal pricing.

Monthly sales volume, costs and possible pricing amounts are provided here.

Instructions

There are three parts $to$ this problem. Use Excel $to$ perform the following.

$a.$ Prepare comparative traditional income statements for each $of$ the seven scenarios. Use conditional formatting $to$ flag the

lowest and highest operating income.

$b.$ Discuss the behavior $of$ the income statement components. Explain why the maximum operating profit level does not occur $at$

the maximum units sold.

$c.$ Scenario $3$ and Scenario $5$ both generated the same revenue. Explain why the operating incomes for these scenarios differ?

Student Work Area

$a.$ Monthly income statements under seven scenarios

Scenario $1$ Scenario $2$ Scenario $3$ Scenario $4$ Scenario $5$ Scenario $6$ Scenario $7$

Revenue

Cost $of$ goods sold

Gross margin

Selling and administrative expenses

Operating income

$b.$ Response $to$ part $b$

$c.$ Response $to$ part $c$