Business Description

After earning her Associate's degree in business, Carla, an avid artist and innovator, decided to start selling unique DIY art kits at trade shows. She has two products:

Product $1$: "Mosaic Table Top"$-$ a kit that includes colored tiles in different geometric shapes that can be glued together to create a mosaic table top that will sell for $$120.$

Product $2$: "Stained Glass Design"$-$ a stained glass starter kit and supplies to make stained glass windows and wall decor that will sell for $$200.$

Costs: Carla has hired an employee to work the trade show booths. The work contract is $$1,400$ per month plus a commission equal to $10\%$ of sales price. Carla will also spend $$560$ per month on trade$-$show entry fees. Carla is purchasing the products from a supplier in Panama. Product $1$ costs $$70\mathrm{.}00$ each; Product $2$ costs $$110$ each. Shipping and handling on Product $1$ will cost $$10\mathrm{.}00$ each; Shipping and handling on Product $2,$ which are heavier, will cost $$18\mathrm{.}00$ each. The shipping and handling costs will be paid by Carla, not the customer. Assume Carla expects to sell $120$ of Product $1$ and $80$ of Product $2$ during her first month of operations $($April$).$ The sales mix is $60\%$ Product $1$ and $40\%$ Product $2.$

Carla's financial goal is to earn a target operating income of $$7,200$ per month.

Directions

You have been hired by Carla to build a CVP model that will help her understand the impact of business conditions on her operating income. $($See "Original Assumptions" worksheet.$)$ In your model, all of the original assumptions will be listed in one area of the spreadsheet $($blue shaded box titled "Assumptions"$).$ All other calculations in the model will reference the assumptions $($blue box$)$ such that if any assumption changes, the effect will ripple through the entire model. To accomplish this goal, you will use FORMULAs, rather than numbers, in every other cell in the worksheet. In other words, the only place you will type numbers is the blue assumptions box. The cells that must be completed in this assignment are outlined in red.

FORMATTING

$-$ Round all UNITS to the nearest whole unit. Use the "decrease decimals" button on your tool bar rather than the Rounding function.

$-$ Show all MONETARY amounts as dollars and cents. Round to the nearest cent. $($$x$.$xx$).$ Use the "decrease decimals" button rather than the rounding function.

$-$ Show all percentages as $\%,$ not as decimals. $($x$\%,$ not $.$xx$)$

$-$ Right justify all cells $($numbers should be to the right side of the cell, not in the middle or left$)$

$1)$ Complete the assumptions $($blue box$)$ based on the data about Carla's business. Identify and list all variable costs separately and all fixed costs separately.

$2)$ Complete the Product Analysis $($yellow boxes$)$ assuming Carla ONLY sells either Product #$1($Mosaic$)$ OR Product #$2($Stained Glass$).$

Check figures: B$/$E Product #$1=70$ units; B$/$E Product #$2=38$ units

$3)$ Complete the Contribution Margin Income Statement for the month of April $($green box$).$ HINT: On product line income statements such as this, the fixed costs are only listed in the total column. Make sure you also show the totals for all other line items. Finally, calculate the overall CM $\%$ for the company.

Operating income $=$ $$5,560$ CM $\%=25\%$

$4)$ Calculate the Weighted Average Contribution Margin $($WACM$)($in orange box$).$

Check figure: WACM$=$ $$37\mathrm{.}60$

$5)$ Use the WACM to calculate the TOTAL number of units $($BE bundle$)$ needed to breakeven $($TOTAL column in the lavender box$).$ THEN, calculate the number of EACH type of product needed to breakeven. Finally, calculate the sales revenue associated with this volume for EACH product, and then add those to get the sales revenue at breakeven in total.

B$/$E Product #$1=31$; B$/$E Product #$2=21.$

$6)$ Use the WACM to calculate the total number of units needed to achieve Carla's target profit $($TOTAL column in the lavender box$).$ THEN, calculate the number of EACH type of product needed to achieve the target profit. Finally, calculate sales revenue associated with this volume for EACH product, and then add those to get the sales revenue in total.

Check figures: B$/$E Product #$1=146$; B$/$E Product #$2=97$

$7)$ Make sure you have cleaned up your worksheet using the formatting conventions list

Copy your original model "Original Assumptions $"$to a new spreadsheet and name that spreadsheet "What if Analysis". All the information from the "Original Assumptions" remains the same except for three changes:

$(1)$ now the monthly sales volume is expected to be $182$ of Product $1$ and $78$ of Product $2.$ So$,$ the new sales mix is $70\%$ Product $1$ and $30\%$ Product $2.$

$(2)$ Carla$$s purchasing price for Product #$1$ increases by $$6\mathrm{.}00.$

$(3)$ The monthly trade show fee decreases by $$100.$

What is the new operating income? What is the new WACM? Given this sales mix, how many units $($in total$)$ will Carla need to sell to earn her target profit

$$

$$