I need someone to do the Advising Client Tab so I can check my work. If you can answer this question in under an hour or even faster, you'll get high reviews. Thanks.

File Edit View Insert Format Data Tools Add-ons Help Last edit was 100% . $% 0 .00 123- Default (Ca... 11 G B C D E H analysis. IT executed properly, the client should be able to use this spreadsheet over and over, using different "what if" assumptions. 1 Business Description 2 3 After taking business classes, Jake, an avid dog-lover, decided to start selling unique pet supplies at trade shows. He has two products: 5 5 Product 1: "Launch-it"- a tennis ball thrower that will sell for $10. 7 8 20 21 23 Product 2: "Treat-time"- an automatic treat dispenser that releases a treat when the dog places his paw on the pedal. The treat dispenser will sell for $30. Costs: Jake has hired an employee to work the trade show booths. The work contract is $1,000 per month plus a commission equal to 10% of revenue. Jake will also spend $500 per month on trade show entry fees. Jake is purchasing the products from a supplier in Mexico Launch-its cost $1 each; Treat-times cost $7 each. Shipping and handling on the Launch-its will cost $2 each; Shipping and handling on the Treat-times, which are heavier, will cost $8 each. The shipping and handling costs will be paid by Jake, not the customer. Assume Jake expects to sell 200 Launch-its and 100 Treat-times during his first month of operations (June). 25 26 28 29 30 Jake's financial goal is to earn an operating income of $8,000 per month. He believes volume may grow at a rate of 5% a month. 31 32 33 34 -35 36 37 38 39 GO Business & Directions Starting file Advising client - -1 . > GO 1 2 0 Launch-it $ 10.00 Launch-it $ ASSUMPTIONS Product #1: Sales price per unit Variable costs per unit: lake's cost to purchase Shipping and handling Sales Commission Total variable cost per unit Product 1 Unit CM CM 9 Breakeven point in units in sales revenue 6.00 60% lake's Pet Supplies Pro Forma Contribution Margin income Statement For the month ending lune 30 us 7 1 00 2.00 1.00 4.00 S S s S 250 2,500.00 Total 10 11 Sales Minus Variable Casts Equals Contribution Martin Minus Fixed costs Equals Operating Income Product Product 2 $ 2,000.00 3,000.00 S 800 DO 5 1,100.00 5 S 1,200.00 $ 1.200.00 5 Target profit volume: in units in sales revenue Monthly volume fioa.no 2.400.00 12 + 200 1,583 15,833.00 S 18 $ 1000 Treat-time S 30.00 WACM % Product #2: Sales price per unit Variable costs per unit: lake's cost to purchase Shipping and Handling Sales Commission Total variable cost per unit Treat time $ 17 $ 5 $ s 12.00 40% Product #2 Unit CM 8 Breakeven point in units -in sales revenue 7.00 B:00 3.00 18.00 79 Total Calculation of Weighted average CM per unit Products Product 22 S 1015 305 $ LES 15 S f.OS 125 125 3,750.00 S 10 DO 21 Monthly volume 22.00 11.00 100 Target profit volume in units in sales revenue WACM/unit 792 23,750.00 $4 s $4 $8.00 Fixed costs per month: 1 Employee Trade Show Fee Total fixed costs per month $ $ 5 1,000.00 500.00 1,500.00 5 7 Product Multiproduct Breakeven paint: in units Sales revenue at breakeven Product 125 $1253.33 Total 188 $313. 8 Target profit per month $8,000.00 $1880.00 Expected change in volume (%) S% Multiproduct Target profit point in units Sales revenue at target profit Product 1 792 $7916.67 Product 12 395 $11875.00 1188 $1979167 Business & Directions Starting file Advising client Grading Rubric - $8.00 Multiproduct Breakeven point: -in units Sales revenue at breakeven Product #1 125 $1253.33 Total Product #2 63 $1880.00 188 $3133.33 Multiproduct Target profit point: in units Sales revenue at target profit Product #1 792 $7916.67 Product #2 396 $11875.00 Total 1188 $19791.67 Margin of Safety (in $) $1867 Margin of Safety % 38% Operating Leverage Factor 2.67 7 Expected % change in operating income (%) 13.35% $ .!... GD Y-3 H X Once you have built the model, use it to answer Jake's questions about his business. Treat each situation as a separate scenario. All comparisons should be made to the original assumptions. EXCEL HINT: To copy a cell from a different worksheet.putain the cell whers you want the number to go, and then go back to the original worksheet, put your cursor on the cell, and then pressenter, NEW ORIGINAL Change 1. Save a copy of your original model to a new spreadsheet called "supplier cost increase". Say the supplier is expected to increase the cost of the products by 20% What is the new operating income? What is the new WACM%? What is the new MOSS? Briefly explain your findings to the client. Operating income Brief explanation WACM percenta MOS% Operating income Brief expl 2. Save a copy of your original model to a new spreadsheet called "new sales mix". Say the monthly sales volume is now expected to be 175 "Treat-tim and 125 "Launch-its" (same total units, but a different sales mix). What is the new operating income? What is the new WACM/unit? Given this sales mix, how many units (in total) will Jake need to sell to earn his target profit? Briefly explain your findings to the client. WACM/unit Units to earn tar et profit Operating income Brief explanation: Operating leverage factor 3. Save a copy of your original model to a new spreadsheet called "alternative contract". Say Jake's employee wanted to negotiate a different work contract: $1,500 per month plus 5% of revenue. Given his original sales volume and mix, how would this contract have changed Jake's operating income? What is the new operating leverage factor? What is the new expected percentage change in operating income il volume increases as expected in the future? Briefly explain your findings to the client. Expected % change in op inc Business & Directions - Starting file - Advising client - Grading Rubric