Build an Income Statement in Excel based on the following hypothetical case data. Use it to answer the questions below. Upload it once you are finished putting the finishing touches on the Excel file.

Overview & Context

A manager at Glossier is trying to make decisions on her portfolio. She manages three products: Milky Jelly Face Wash, Generation G Lipstick, and the new Silky Hands Sanitizer. These products are in separate sub$-$industries within the overall beauty industry.

Because of the pandemic, the lip color category has taken a hit. Sales have declined for the past $2$ years. Most analysts agree lip color is going to shrink by $1\%$ next year. The pandemic shifted consumer preferences towards skincare. Hand care is expected to grow by $5\%$ next year and face skin care is expected to grow three times as fast as hand care.

Product Portfolio

Luxury Hand Sanitizer. For Glossier, hand sanitizer revenues were $$800$K$.$ Each costs Glossier$$s R&D team $$2\mathrm{.}99$ to produce and they sell each bottle for $$14.$ They ran an online advertising campaign that cost $$100$K$.$ Their biggest competitor is a brand called Touchland, that had $$1\mathrm{.}2$M in revenues, but they distribute through Sephora. Glossier and Touchland represent small shares of the overall $$hand care$$ market, but they represent the dominant players in their smaller $$luxury hand sanitizer$$ market: $33\%$ and $42\%$ each, respectively. They are each other$$s nearest largest competitors.

Ultra$-$Gentle Facewash. The face wash category is fractured. Glossier Milky Jelly $($$$12$M revenues$)$ competes most closely with Fresh Soy Face Cleanser $($$$11$M revenues$)$ and E$.$L$.$F$.$ Bounce Coconut Cleanser $($$$6$M in revenues$).$ Glossier Milky Jelly comes in two different bottle sizes: retailing for $$18$ per bottle $($full size, accounting for $75\%$ of revenues aka $$9$M$)$ and $$9$ per bottle $($mini size, accounting for $25\%$ of revenues aka $$3$M$).$ The per unit COGS for both the full and mini bottle is $$1\mathrm{.}55.$ To advertise this product Glossier took out ads in major beauty magazines in the amount of $$75$K per month for $12$ months. These three brands represent the dominant players in the ultra$-$gentle facewash category. Glossier$$ relative market share is $1\mathrm{.}1,$ while Fresh has a relative market share of $0\mathrm{.}9,$ and E$.$L$.$F has a relative market share of $0\mathrm{.}6.$

Trendy Lip Color. The market for color lip products is projected to be $$200$M in revenue, with closest competitors Kylie Lip Kits and Fenty Lip Paint with $14\%$ share each. Generation G Lip is projected to capture about $11\%$ of the market in revenues. Each tube of Generation G costs $$0\mathrm{.}65$ to manufacture and retails for $$18.$ Glossier ran $$200$K of billboards in each of $20$ top cities to advertise this product.

For all products, assume units sold $=$ units produced.

Your Task

$1.$ Organize the above information into an Excel file, showing Glossier's Income Statement.

a$.$ Headings and labels should be clear, well defined, and formatted to maximize readability.

b$.$ Number should be in the proper format.

$2.$ Use your Excel file to respond to the questions below.

$3.$ Upload your Excel file when you are finished putting the polishing touches on$.$ You will receive $0-4$ points, based on how accurately your Income Statement is completed as well as how readable$/$organized your chart Income Statement is$.$