Please help me with this. Thank you
Play and Stay Manufacturing(P&S) has a factory that produces cabinetry for the RV and marine market. The company has other product lines. Materials and labor for the cabinets are determined by each job. To simplify the assignment, we will assume the following average costs The following information highlights P&S Manufacturing's cost structure for 2020. The materials include $2,800 for the wood and other materials on a per job basis. It requires 24 hours of labor on average for the cabinetry. The hourly rate is$11. The sales price will be set at a markup of 80%. e The company estimates that it will have 43,200 direct labor hours in total for the cabinets. It assumes 1800 units are sold on average per year. A breakdown of estimated yearly costs related to the cabinetry follows: Salaries-office & administrative Salaries for factory personal Office Rent Factory Rent Office utilities and Misc office expenses(based on units sold) Sales travel(based on units sold) Insurance - office Depreciation office equipment Depreciation for factory equipment Advertising Sales commissions(based on units sold) Factory Property taxes Maintenance for factory equipment S 500,000 S 350,000 $ 150,000 $ 30,000 $ 15,000 $ 24,000 $ 12,000 $ 40,000 $ 70,000 $ 20,000 $ 50,000 $ 25,000 S 80,000 Hint: you will need to distinguish between product vs. period costs and variable vs fixed costs. Please do not forget all costs for this company (not just the ones listed above in the table) General requirements: A schedule will be created for each of the following questions. Use a new worksheet (not workbook) to answer each question. You can combine Q6 and Q7 in one sheet for comparison purposes. All schedules should be included in excel. Your spreadsheets must contain formulas and referring numbers. Not using formulas will result in a minimum one letter-grade reduction Linking the schedules is required. Your spreadsheet must be professional and include the following items: report titles, formatted numbers, single and double underlines, and correct spelling. Every number should be labeled clearly so the reader understands what numbers represent. If you are unsure about the format, please review the textbook for a similar report or ask!!! Detailed Requirements: 1. Please identify and calculate totals for the following classifications on ALL costs listed on the first page: variable and fixed costs; product and period costs. The schedule should contain numbers for each area on a yearly basis. For the variable cost, also show them on a per unit basis. (must be calculated based on 1800 units above) 2. Determine the average cost of manufacturing one unit given the volume expected to be sold every year and assuming the MOH costs are allocated based on the direct labor hours per unit. Please show all calculations and round to the nearest dollar. Calculate the sales price based on your cost calculation. I would recommend that you calculate the MOH per unit first. You should calculate an Overhead rate 3. Prepare a Job Order Cost sheet for the following custom project: Materials 56,000 and 30 hours of labor. Calculate the sales price based on your cost calculations. Do not use these numbers for any other calculation. 4. What is the Contribution Margin (CM) in total and per unit dollars, and CM% for the sale of 1,800 units? Label each calculation so that it is clear to the reader what is being shown. These numbers should be linked to Q1. 5. Prepare a traditional Income Statement assuming a volume of 1,800 units. For the cost of goods sold, please use the. Bor unit cost you calculated in #2. You do not have to prepare any additional schedules. I would use a similar format to exhibit 16-8 on page 737 or from your lecture notes. I recommend that you list out all operating expenses (period costs) identified in Q1. Do not use just Selling and General Administrative Expenses for your categories. Points will be lost by not listing out all period costs. You can ignore interest and income tax expense. These numbers should be linked to Q1. 6. Prepare three CVP Income Statements using the following yearly volumes: 1,200, 1,800 and 2,500. Keep in mind how variable and fixed costs behave. The traditional income statement from 5 should be about the same net income as the 1,800 units for the CVP format. In addition, Q1, should agree to the total fixed costs and per unit variable costs for these schedules. (see PowerPoint from chat the week of the project for format) a) Calculate Break-even in units and sales S for the company b) Calculate units and sales S if the company wants a profit of $5,000,000. c) Margin of safety in dollars for 1,800 units. 7. Prepare a new CVP Income Statement that reflects the following proposed changes. The company is considering a new supplier and some additional factory costs to increase quality and production levels. The new supplier will reduce direct material costs by 5%. The fixed costs will increase costs by 45%. With the expected increase in quality, the company believes that it can support a 3% increase in sales. The volumes used should be 1,800, 2,500 units and maybe more volumes. 8. Assuming P&S Manufacturing is considering adding another product line - molded doors (keeping the cabinet production constant at 1,800 units). How many units should the company sell to make adding the additional line economically feasible/attractive? Adding this line will require hiring a different type of worker which costs the company $15/hour. P&S Manufacturing estimated each unit takes 10 hours of labor, and $850 direct materials. This line is estimated to incur additional fixed MOH $425,000 for the year, and variable costs/expenses per unit are the same as the cabinet product line. This product line does not add additional fixed period costs, but shares the fixed period costs with the cabinetry line. The company estimated sales price of a set of doors would be $1,850