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 Jewelry's 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%) $ 1.75 1.00 15 minutes $ 15.00 per hour 7.65% of wages 5% of wages 1 Melissa Bright Jewelry, Inc. 2 Raw material costs: 3 Quantity (oz.): 4 Per oz. 5 6 Raw material per unit: 7 8 Direct labor costs: 9 Time to manufacture one unit: 10 Direct labor hourly rate: 11 Employment tax 12 Benefits 13 14 Other monthly costs: 15 Factory rent 16 Insurance 17 Water 18 Plant manager salary (incl. taxes and benefit) 19 Administrative salary (incl. taxes and benefit) 20 Supplies 21 Janitor and custodian fees 22 Utility: 23 Historial utility costs 24 January 25 February 26 March 27 April 28 May 29 June 30 July 31 August 32 September 33 October 34 November 35 December 36 $ $ $ $ $ $ $ 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 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 6 $ 50 7 Requirement 1: 8 Per unit 9 Sales price o Direct material costs 1 Direct labor costs 2 Variable MOH 3 Total variable cost 4. Contribution Margin 5 6 Fixed MOH 7 8 9 0 1 2 3 4 Breakeven units 5 6 7 Requirement 2: 8 Required profit 60,000 9 0 1 2 Target production units 3 Requirement 3: Fixed unit of sales 2,500 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 below has the information of the costs of direct materials, direct labor, and other manufacturing expenses: Melissa Bright Jewelry's 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%) $ 1.75 1.00 15 minutes $ 15.00 per hour 7.65% of wages 5% of wages 1 Melissa Bright Jewelry, Inc. 2 Raw material costs: 3 Quantity (oz.): 4 Per oz. 5 6 Raw material per unit: 7 8 Direct labor costs: 9 Time to manufacture one unit: 10 Direct labor hourly rate: 11 Employment tax 12 Benefits 13 14 Other monthly costs: 15 Factory rent 16 Insurance 17 Water 18 Plant manager salary (incl. taxes and benefit) 19 Administrative salary (incl. taxes and benefit) 20 Supplies 21 Janitor and custodian fees 22 Utility: 23 Historial utility costs 24 January 25 February 26 March 27 April 28 May 29 June 30 July 31 August 32 September 33 October 34 November 35 December 36 $ $ $ $ $ $ $ 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 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 6 $ 50 7 Requirement 1: 8 Per unit 9 Sales price o Direct material costs 1 Direct labor costs 2 Variable MOH 3 Total variable cost 4. Contribution Margin 5 6 Fixed MOH 7 8 9 0 1 2 3 4 Breakeven units 5 6 7 Requirement 2: 8 Required profit 60,000 9 0 1 2 Target production units 3 Requirement 3: Fixed unit of sales 2,500 Target unit sales price