cvp analysis project

Step $\backslash$#$54)$ Calculate the weighted average contribution margin $($WACM$)$ per unit.

Check figure: WACM$/$unit $=\backslash$$$13\mathrm{.}96$

$5)$ Use the WACM$/$unit to calculate the TOTAL number of units needed to breakeven. THEN, calculate the number of EACH type of product needed to breakeven. $($Note: You start by calculating the Total and then break it down to be per product.$)$ Finally, calculate the sales revenue associated with this volume for EACH product, and then the sales revenue to breakeven in total.

Check figures: B$/$E Product $\backslash$#$1=63$; B$/$E Product $\backslash$#$2=37$

$6)$ Use the WACM$/$unit to calculate the total number of units needed to achieve Sue's target profit. THEN, calculate the number of EACH type of product needed to achieve the target profit. $($Note: You start by calculating the Total and then the per product.$)$ Finally, calculate sales revenue associated with this volume for EACH product, and then the sales revenue in total.

Check figures: B$/$E Product $\backslash$#$1=413$; B$/$E Product $\backslash$#$2=244$

$7)$ Calculate the MOS using June sales as the expected sales. Calculate the MOS in terms of sales revenue and as a percentage. Also calculate the current operating leverage factor $($round to the nearest $2$ decimal places$)$ and use it to determine the expected percentage change in operating income stemming from an expected change in sales volume.

Check figures: MOS$\backslash \%=71\mathrm{.}3\backslash \%$; Operating leverage factor$=1\mathrm{.}40$

$8)$ Change the name of worksheet to "Original Assumptions".

$9)$ Make sure you have cleaned up your worksheet using the formatting conventions listed above.

$10)$ Go to the "Advising client" worksheet and follow the directions found there. nce you have built the model, use it to answer Sue's questions about her business. eat each situation as a separate scenario. All comparisons should be made to the iginal assumptions.

Save a copy of your original model to a new worksheet called "supplier cost crease". Say the supplier is expected to increase the cost of the products by $\backslash (21\backslash \%\backslash ).$ hat is the new operating income? What is the new WACM$\backslash \%?$ What is the new OS$\backslash \%?$ Briefly explain your findings to the client.

Save a copy of your original model to a new worksheet called "new sales mix". Say e monthly sales volume is now expected to be $150$ "Launch$-$its" and $200$ "Treat$-$times" ame total units, but a different sales mix$).$ What is the new operating income? hat is the new WACM$/$unit$?$ Given this sales mix, how many units $($in total$)$ will Jake eed to sell to earn his target profit? Briefly explain your findings to the client.

Save a copy of your original model to a new worksheet called "alternative contract". y Jake's employee wanted to negotiate a different work contract: $\backslash$$$1,500$ per month us $\backslash (6\backslash \%\backslash )$ of revenue. Given his original sales volume and mix, how would this contract ave changed Jake's operating income? What is the new operating leverage factor? hat is the new expected percentage change in operating income if volume increases expected in the future? Briefly explain your findings to the client.

EXCEL HINT: To copy a cell from a different worksheet, put a $+$ in the cell where you want the number to go$,$ and then go back to the original worksheet, put your cursor on the cell, and then press enter.