build an excel model to answer these, pls show formulas so I can build the same model

Quip is a company that is disrupting the oral hygine industry with its simple yet effective approach. Officially launched in 2015, they were founded on the notion that toothbrush heads should be continually replaced to help correct bad brushing habits. Quip points out that most people do not brush long enough and do not replace their heads frequently enough-and so they set out to change this offering a subscription service for oral hygine A subscription with Qulp will provide customers with new heads, toothpaste, and batteries, which are mailed to customers automatically every 3 months for only $10 with free shipping. In their current business model, they have different starter kit options, which are a 3-month and a 12-month prepaid plan, for both adults and couples. Each kit has the option of a metal or plastic toothbrush body. Using the data and information provided on the worksheet Question 1 Input Data, please build a model to analyze Quip's business model. Quip estimates that 60% of their brush orders are for the 3 montn plan (and thus 40% are for the 12 month plan) and 70% of their brush orders are for plastic brushes (and thus 30% are for metal brushes). 1 (8 Points): Given the estimated breakdown of brush orders and the estimated brush demand, what is the unit cost by product category? Adult Starter Set - 3 mo. Plan (Plastic) Adult Starter Set - 3 mo. Plan (Metal) Adult Starter Set - 12 mo Plan (Plastic) Adult Starter Set - 12 mo. Plan (Metal) 2 (8 Points): Given the starter set pricing, and demand and unit cost by category, calculate the total revenue and total cost for each category. Revenue Cost Adult Starter Set-3 mo. Plan (Plastic) Adult Starter Set - 3 mo. Plan (Metal) Adult Starter Set - 12 mo Plan (Plastic) Adult Starter Set 12 mo. Plan (Metal) 34 Points): Given the refill demand and unit cost, calculate the total revenue and total cost for refills. Revenue Cost Refill 4 (4 Points): Calculate the total profit for Quip Total Profit 5(6 Points): Quip is unsure of demand for their refills. Let's analyze how changes to refill demand affect Quip's total profit. Refill Demand Total Profit 250,000 500,000 750,000 1,000,000 1,250,000 1,500,000 Quip Pricing Adult Starter Set - 3 mo. Plan (Plastic) $ Adult Starter Set - 3 mo. Plan (Metal) $ Adult Starter Set - 12 mo Plan (Plastic) Adult Starter Set - 12 mo. Plan (Metal) Refill Price $ 25.00 40.00 50.00 65.00 10.00 Refill 3 Quip Estimated Demand Brush Demand 750,000 0 Refill Demand 1,000,000 1 2 Unit Cost - Material and Shipping 3 Orders (by product) Adult Starter Set - 3 mo. Plan (Plastic) Adult Starter Set - 3 mo. Plan (Metal) Adult Starter Set - 12 mo Plan (Plastic) Adult Starter Set - 12 mo. Plan (Metal) 4 100,000 $ 25.75 $ 34.75$ 56.75 $ 65.75 $ 5 250,000 $ 24.13 $ 32.68 $ 52.85 $ 61.40 $ 6 500,000 $ 22.60 $ 30.72 $ 49.16 $ 57.28 $ 7 1,000,000 $ 21.10 $ 28.82 $ 45.50 $ 53.22 $ 3 2,000,000 $ 19.73 $ 27.06 $ 42.21 $ 49.54 $ 9 0 1 2 3 4 8.50 8.13 7.77 7.43 7.11 Let's first talk about subscriptions. There are two subscriptions (3 and 12 month), and each of these includes a starter box that includes the brush body, head, battery and toothpaste. The only difference between the two plans is that the 12 month plan includes 3 additional deliveries of a head, battery and toothpaste. Given that the starter box is the same regardless of subscription (3 month plastic starter box is the same as a 12 month plastic starter box), this means that the company can leverage economies of scale to reduce their production and shipping costs. So, if total demand for Quip brushes is 750,000 and 70% of that demand is for plastic brushes, then both the 3 month and 12 month subscriptions would be able to leverage the pricing associated with 525,000 units, or $22.60 for the 3 month plan and $49.16 for the 12 month plan. So then total production and shipping cost for the plastic 3 month plan would be $7,119,000 ($22.60*315,000)