Calculate the variable cost per unit and the total monthly fixed costs of each of the three products

Other Costs (Continued) - 7 industrial sewing machines at $3,000 each for a total of $21,000; depreciation for all 7 sewing machines is $350 per month ( 5 -year life, zero salvage value) - The casual and aerobic departments each use 2 maeffines. The running department uses the other 3 . - Utilities and insurance $900 per montb; al locate based on square footage - Scissors, threac - Rent: $1,200 per month; allocated based on square footage - Price tags: $250 for 2,500 ( $0.10 each) - Advertising: $2,000 per month - Office Supplies: $200 per month - Other business equipment: $150 per month Market Research Data You have conducted some market research for style and size of products you want to use to launch your business. The market research has indicated the following sales price ranges will be optimal for your area depending on style of products you choose to sell: - Running Shoes With pricing at $150 per shoe, you can expect to sell 15 shoes per day. With pricing at $160 per shoe, you can expect to sell 12 shoes per day. = With pricing at $175 per shoe, you can expect to sell 9 shoes per day. = - Aerobic Shoes With pricing at $120 per shoe, you can expect to sell 18 shoes per day. = With pricing at $135 per shoe, you can expect to sell 16 shoes per day.= With pricing at $140 per shoe, you can expect to sell 14 shoes per day.= Casual shoes With pricing at $80 per shoe, you can expect to sell 30 shoes per day.= With pricing at $90 per shoe, you can expect to sell 26 shoes per day.= With pricing at $97 per shoe, you can expect to sell 23 shoes per day.= Market Research Data (Continued) Additionally, you will need to compare your break-even points for the following target profits for each area of your business to determine your prices: - Running Shoes - Break-even $1,200 target profit each month $2,000 target profit each month - Aerobic Shoes - Break-even - $1,000 target profit each month - $1,700 target profit each month - Casual shoes - Break-even - \$800 target profit each month - $1,400 target profit each month Required 1. Calculate the variable cost per unit and total monthly fixed costs of each product. - Both the variable cost per unit and the total monthly fixed cost of each product should be rounded to the nearest 2 cents. (use the round function in excel) 2. Choose a selling price for each product and state why that selling price was chosen. 3. Using the selling price you chose, calculate the contribution margin per unit of each product. 4. Using the selling price you chose, calculate the monthly break-even point (in units) for each product. 5. Using the selling price you chose, calculate the monthly break-even points (in units) for the desired target profits each month. - All break-even amounts need to be rounded up to the nearest whole unit (use the roundup function in excel) Additional Requirements - You must set up a document in excel. This document must be one you have created. - Treat this document as a report that you would subr - You will set up 4 tabs in the excel document: - Tab 1: Information - This tab will list all pertinent information from the data excel formulas) BSG, Inc, just completed year 2 (2024) of selling shoes. You have decided that instead of buying the sneakers from another company, it may be cheaper to produce the sneakers. In addition to the running and aerobic sneakers, you'd also like to produce a casual sneaker. You have found a workshop space you can use for producing your products. While it would cut down on the sales volume, you'd like to customize each pair of shoes to the customer's specifications. Each shoe will have a standard price to make, which has already been calculated. In addition to this cost, the customer would be able to customize the shoelace color, ribbons and decals. These costs are in addition to the standard cost. After some research and planning, you have estimates for the various operating costs. The total square footage for the production facility is 2,100 square feet broken into three areas. The running shoes occupy 800 square feet, the aerobic shoes occupy 600 square feet and the casual shoes make up the rest. 700 Salary and Hiring Data Three running shoemakers, each will be paid $21.00 per hour and work 40 hours per week - Two aerobic shoemakers, each will be paid \$22.00 per hour and work 40 hours per week - Four casual shoemakers, each will be paid $20.00 per hour and work 40 hours per week - One receptionist, who will be paid $15.00 per hour and work 32 hours per week Other Costs - The initial base cost to produce 1 shoe is (note - when I mention shoe(s), I'm talking about 1 pair or 2 shoes) - Running Shoes $35 - Aerobic Shoes $30 - Casual Shoes $20 - In addition to the base cost - each pair of shoes requires shoelaces, polyesterylon ribbons and decals. - Shoelaces $3 per foot of shoelace - Running Shoes -1.5 feet of shoelace - Aerobic Shoes - 2 feet of shoelace - Casual Shoes -1 foot of shoelace - Polyesterylon ribbons - $9 per yard of ribbon - Running Shoes -4 yards of ribbon - Aerobic Shoes -3 yards of ribbon - Casual Shoes -2 yards of ribbon - Decals - $0.50 per Decal - 4 decals used per running shoe - 3 decals used per aerobic shoe