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 Manufacturings 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%. 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 $ 500,000 Salaries for factory personal $ 350,000 Office Rent $ 150,000 Factory Rent $ 30,000 Office utilities and Misc office expenses(based on units sold) $ 15,000 Sales travel(based on units sold) $ 24,000 Insurance - office $ 12,000 Depreciation - office equipment $ 40,000 Depreciation for factory equipment $ 70,000 Advertising $ 20,000 Sales commissions(based on units sold) $ 50,000 Factory Property taxes $ 25,000 Maintenance for factory equipment $ 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) Page | 2 QUESTIONS for Excel Part (45pts) 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 $6,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 per 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 $ for the company b) Calculate units and sales $ if the company wants a profit of $5,000,000. c) Margin of safety in dollars for 1,800 units. Page | 3 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. QUESTIONS for the Written Analysis (55pts) General requirement: Assume you are addressing some concerns by the board of directors by answering the following questions listed in the Detailed questions below. Remember, you are writing to the board, not just answering questions. Please use a memo format for the paper and address it to the Board of Directors. The structure of the paper should include an opening paragraph about the company in a word document. The discussion questions should be answered in the order listed. However, do not number the questions. Instead, the discussion should flow from one topic to the next and include as a minimum the questions identified. Although you should not construct the memo in a numbered-points format, you could consider adding subtitles for different areas of discussions, if appropriate. This helps the directors more easily identify the topics/concerns addressed in the paper. Do not discuss/explain your calculations. Answer the questions related to your calculations, and support your arguments with numbers when necessary. You should be thinking about all the concepts that you have learned from this course and their importance to a business owner or investor. I should finish reading your paper and see the concepts and some industry knowledge demonstrated through the discussion and the analysis. Finally, the closing paragraph(s) should summarize the company, industry and results. Plan to devote time to writing and reviewing the paper. There will be significant grade reductions for papers that are not well written (spelling, grammar etc.) I would recommend that the paper be approximately 5-7 pages (excluding any spreadsheet or reference sources you may include).