Melissa Bright Jewelry, Inc. is a jewelry manufacturer. It has a factory located in Hartford, CT. It imports raw materials and hires skilled labor to assemble the finished products. The manufacturing process involves metal welding which uses lots of electricity. The average sales price per unit of finished product is $60.

The Excel file below has the information of the costs of direct materials, direct labor, and other manufacturing expenses:

• Melissa Bright Jewelrys raw materials are purchased in oz. The per oz. purchase price is $4.20.
• Melissa Bright Jewelry, Inc. pays $16 per hour to its labor on the production line. In addition, it is required to pay the employment taxes at 7.65% of the wages and other benefits at 25% of wages. It takes 15 minutes (including break time) on average for one manufacturing labor to finish one piece of product.
• Other expenses include rent, insurance, and managerial salaries. The amount of these expenses are stable every month.
• The costs of supplies vary by production unit. The average cost per unit is $0.60.
• Utility expenses and the production units in the past 12 months are summarized in the table in the Excel file. The manager understands that the more units produced, the higher the electricity bill. But she does not know what the per-unit electricity cost is.

Requirements:

1. Calculate the number of break-even units per month. Use the regression method (see below) to measure the variable and the fixed electricity cost. (30%)
2. If Melissa Bright Jewelry would like to have a monthly profit of $75,000, how many units should Melissa Bright Jewelry sell each month? (30%)
3. If Melissa Bright Jewelry can only sell 2,800 units per month and still wants to have $75,000 profit per month, how much Melissa Bright Jewelry should sell for each unit? (Assume that the unit of sales will NOT be affected by the price increase). (20%)
4. Your deliverable should be an Excel file with proper formatting, clear layout, and clear supports of your computations. (20%)

\begin{tabular}{|c|c|c|c|} \hline 1 & Melissa Bright Jewelry, Inc. & & \\ \hline 2 & Raw material costs: & & \\ \hline 3 & Quantity (oz.): & & \\ \hline 4 & Per oz. & 4.20 & \\ \hline \multicolumn{4}{|l|}{5} \\ \hline 6 & Raw material per unit: & 1.00 & \\ \hline \multicolumn{4}{|l|}{7} \\ \hline 8 & Direct labor costs: & & \\ \hline 9 & Time to manufacture one unit: & 15 minutes & \\ \hline 10 & Direct labor hourly rate: & 16.00 & per hour \\ \hline 11 & Employment tax & 7.65% & of wages \\ \hline 12 & Benefits & 25% & of wages \\ \hline \multicolumn{4}{|l|}{13} \\ \hline 14 & Other monthly costs: & & \\ \hline 15 & Factory rent & 9,300 & fixed per month \\ \hline 16 & Insurance & 1,300 & fixed per month \\ \hline 17 & Water & 500 & fixed per month \\ \hline 18 & Plant manager salary (incl. taxes and benefit) & 4,000 & fixed per month \\ \hline 19 & Administrative salary (incl. taxes and benefit) & 3,200 & fixed per month \\ \hline 20 & Supplies & 0.60 & per unit \\ \hline 21 & Janitor and custodian fees & 2,000 & fixed per month \\ \hline 22 & Utility: & & \\ \hline 23 & Historial utility costs & production quantity & Charges \\ \hline 24 & January & 3,550 & 59,048.78 \\ \hline 25 & February & 3,360 & 55,467.75 \\ \hline 26 & March & 2,450 & 60,397.95 \\ \hline 27 & April & 2,640 & 62,640.23 \\ \hline 28 & May & 2,605 & 61,189.71 \\ \hline 29 & June & 2,740 & 63,614.89 \\ \hline 30 & July & 2,780 & 69,037.54 \\ \hline 31 & August & 2,450 & 62,181.49 \\ \hline 32 & September & 2,660 & 49,769.92 \\ \hline 33 & October & 2,450 & 58,883.43 \\ \hline 34 & November & 2,330 & 56,695.86 \\ \hline 35 & December & 2,100 & 52,640.26 \\ \hline \end{tabular} 37 Requirement 1: Per unit Sales price Direct material costs Direct labor costs Variable MOH 43 Total variable cost Contribution Margin Fixed MOH \begin{tabular}{|l|lr|} \hline Factory Rent & $ & 9,300 \\ \hline Insurance & $ & 1,300 \\ \hline Water & $ & 500 \\ \hline Plant manager salary (incl. taxes and benefit) & $ & 4,000 \\ \hline Administrative salary (incl. taxes and benefit) & $ & 3,200 \\ \hline Janitor and custodian fees & $ & 2,000 \\ \hline Supplies & & \\ \hline \end{tabular} Breakeven units Requirement 2: Required profit $75,000 Target production units [Please round it to two decimal points.] Requirement 3: Fixed unit of sales 2,800 Target unit sales price [Please round it to two decimal points.] \begin{tabular}{|c|c|c|c|} \hline 1 & Melissa Bright Jewelry, Inc. & & \\ \hline 2 & Raw material costs: & & \\ \hline 3 & Quantity (oz.): & & \\ \hline 4 & Per oz. & 4.20 & \\ \hline \multicolumn{4}{|l|}{5} \\ \hline 6 & Raw material per unit: & 1.00 & \\ \hline \multicolumn{4}{|l|}{7} \\ \hline 8 & Direct labor costs: & & \\ \hline 9 & Time to manufacture one unit: & 15 minutes & \\ \hline 10 & Direct labor hourly rate: & 16.00 & per hour \\ \hline 11 & Employment tax & 7.65% & of wages \\ \hline 12 & Benefits & 25% & of wages \\ \hline \multicolumn{4}{|l|}{13} \\ \hline 14 & Other monthly costs: & & \\ \hline 15 & Factory rent & 9,300 & fixed per month \\ \hline 16 & Insurance & 1,300 & fixed per month \\ \hline 17 & Water & 500 & fixed per month \\ \hline 18 & Plant manager salary (incl. taxes and benefit) & 4,000 & fixed per month \\ \hline 19 & Administrative salary (incl. taxes and benefit) & 3,200 & fixed per month \\ \hline 20 & Supplies & 0.60 & per unit \\ \hline 21 & Janitor and custodian fees & 2,000 & fixed per month \\ \hline 22 & Utility: & & \\ \hline 23 & Historial utility costs & production quantity & Charges \\ \hline 24 & January & 3,550 & 59,048.78 \\ \hline 25 & February & 3,360 & 55,467.75 \\ \hline 26 & March & 2,450 & 60,397.95 \\ \hline 27 & April & 2,640 & 62,640.23 \\ \hline 28 & May & 2,605 & 61,189.71 \\ \hline 29 & June & 2,740 & 63,614.89 \\ \hline 30 & July & 2,780 & 69,037.54 \\ \hline 31 & August & 2,450 & 62,181.49 \\ \hline 32 & September & 2,660 & 49,769.92 \\ \hline 33 & October & 2,450 & 58,883.43 \\ \hline 34 & November & 2,330 & 56,695.86 \\ \hline 35 & December & 2,100 & 52,640.26 \\ \hline \end{tabular} 37 Requirement 1: Per unit Sales price Direct material costs Direct labor costs Variable MOH 43 Total variable cost Contribution Margin Fixed MOH \begin{tabular}{|l|lr|} \hline Factory Rent & $ & 9,300 \\ \hline Insurance & $ & 1,300 \\ \hline Water & $ & 500 \\ \hline Plant manager salary (incl. taxes and benefit) & $ & 4,000 \\ \hline Administrative salary (incl. taxes and benefit) & $ & 3,200 \\ \hline Janitor and custodian fees & $ & 2,000 \\ \hline Supplies & & \\ \hline \end{tabular} Breakeven units Requirement 2: Required profit $75,000 Target production units [Please round it to two decimal points.] Requirement 3: Fixed unit of sales 2,800 Target unit sales price [Please round it to two decimal points.]