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 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 $1.75.
• 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 5% 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.5.
• 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 to measure the variable and the fixed electricity cost. (30%)
2. If Melissa Bright Jewelry would like to have a monthly profit of $60,000, how many units should Melissa Bright Jewelry sell each month? (30%)
3. If Melissa Bright Jewelry can only sell 2,500 units per month and still wants to have $60,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%)

Melissa Bright Jewelry, Inc. Raw material costs: Quantity (oz.): Per oz. $ 1.75 Raw material per unit: 1.00 Direct labor costs: Time to manufacture one unit: Direct labor hourly rate: Employment tax Benefits 15 minutes $ 15.00 per hour 7.65% of wages 5% of wages $ $ $ $ $ $ 10,000 fixed per month 1,000 fixed per month 200 fixed per month 4,500 fixed per month 3,000 fixed per month 0.50 per unit 1,500 fixed per month $ Other monthly costs: Factory rent Insurance Water Plant manager salary (incl. taxes and benefit) Administrative salary (incl. taxes and benefit) Supplies Janitor and custodian fees Utility: Historial utility costs January February March April May June July August September October November December production quantity 3,550 $ 3,360 2,450 2,640 2,530 2,740 2,780 2,450 2,630 2,400 2,250 2,100 Charges 58,942.78 55,361.75 40,951.62 42,078.27 40,225.81 42,846.10 46,149.11 41,818.84 43,051.85 39,175.99 37,255.24 35,857.11 $ 50 Requirement 1: Per unit Sales price Direct material costs Direct labor costs Variable MOH Total variable cost Contribution Margin Fixed MOH Breakeven units Requirement 2: Required profit $ 60,000 Target production units Requirement 3: Fixed unit of sales 2,500 Requirement 3: Fixed unit of sales 2,500 Target unit sales price Melissa Bright Jewelry, Inc. Raw material costs: Quantity (oz.): Per oz. $ 1.75 Raw material per unit: 1.00 Direct labor costs: Time to manufacture one unit: Direct labor hourly rate: Employment tax Benefits 15 minutes $ 15.00 per hour 7.65% of wages 5% of wages $ $ $ $ $ $ 10,000 fixed per month 1,000 fixed per month 200 fixed per month 4,500 fixed per month 3,000 fixed per month 0.50 per unit 1,500 fixed per month $ Other monthly costs: Factory rent Insurance Water Plant manager salary (incl. taxes and benefit) Administrative salary (incl. taxes and benefit) Supplies Janitor and custodian fees Utility: Historial utility costs January February March April May June July August September October November December production quantity 3,550 $ 3,360 2,450 2,640 2,530 2,740 2,780 2,450 2,630 2,400 2,250 2,100 Charges 58,942.78 55,361.75 40,951.62 42,078.27 40,225.81 42,846.10 46,149.11 41,818.84 43,051.85 39,175.99 37,255.24 35,857.11 $ 50 Requirement 1: Per unit Sales price Direct material costs Direct labor costs Variable MOH Total variable cost Contribution Margin Fixed MOH Breakeven units Requirement 2: Required profit $ 60,000 Target production units Requirement 3: Fixed unit of sales 2,500 Requirement 3: Fixed unit of sales 2,500 Target unit sales price