Melissa Bright Jewelry, Inc.
Raw material costs:
Quantity (oz.):
Per oz.	$ 4.10
Raw material per unit:	1.00
Direct labor costs:
Time to manufacture one unit:	15 minutes
Direct labor hourly rate:	$ 15.00	per hour
Employment tax	7.65%	of wages
Benefits	15%	of wages
Other monthly costs:
Factory rent	$ 9,400	fixed per month
Insurance	$ 1,250	fixed per month
Water	$ 400	fixed per month
Plant manager salary (incl. taxes and benefit)	$ 3,800	fixed per month
Administrative salary (incl. taxes and benefit)	$ 3,400	fixed per month
Supplies	$ 0.70	per unit
Janitor and custodian fees	$ 2,250	fixed per month
Utility:
Historial utility costs	production quantity	Charges
January	3,550	$ 59,048.78
February	3,360	$ 55,467.75
March	2,450	$ 58,777.42
April	2,640	$ 60,944.40
May	2,605	$ 59,530.31
June	2,740	$ 61,884.15
July	2,780	$ 67,156.67
August	2,450	$ 60,502.27
September	2,660	$ 49,769.92
October	2,450	$ 57,291.86
November	2,330	$ 55,171.75
December	2,100	$ 51,241.66
Requirement 1:
Per unit
Sales price	$ 50
Direct material costs
Direct labor costs
Variable MOH
Total variable cost
Contribution Margin
Fixed MOH
Breakeven units
Requirement 2:
Required profit	$ 74,000
Target production units
Requirement 3:
Fixed unit of sales	2,800
Target unit sales price

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 $50.

The Excel file above 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.10.
• Melissa Bright Jewelry, Inc. pays $15 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 15% 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.70.
• 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.

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.
2. If Melissa Bright Jewelry would like to have a monthly profit of $74,000, how many units should Melissa Bright Jewelry sell each month?
3. If Melissa Bright Jewelry can only sell 2,800 units per month and still wants to have $74,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).
4. Use same excel file with clear supports of your computations.

The regression method:

Use the following formula in excel:

o =slope(Y series, X series): this function returns the beta, or the variable cost per unit

o =intercept(Y series, X series): this function returns the intercept, or the fixed cost

o Select the data range of the charges as Y series. Select the production quantity as the X series.

Feel free to add rows to the worksheet if you think more rows are needed.