Problem 1. The Shoemaker Family makes shoes. They want to produce a unique leather shoe for women; however, they want to evaluate whether they can make a profit. They want to find out how many pairs of shoes they must make to break even. The company has determined that it would cost $2,000 to set up production to manufacture the shoes. Accounting has provided you the raw data which has determined each pair would cost approximately $60 marginal cost to make from similar models. Marketing Research has determined that each pair would sell for $80 based upon similar models. From the raw data create the pivot table to determine exact average marginal cost and average selling price. A) Place the data in the appropriate data cells from the data above. B) Place the appropriate formulas given in column H to the appropriate place in column F. C) Using Excel, how large must the order where a break-even point is obtained? (cell C9) Use the data in the word problem to solve for the correct Production Quantity. D) Create the Chart on this worksheet. Using the Production Qty, the Revenue and Cost... create the scatter chart with lines to show the breakeven crossover point. Label your chart "Break Even". Highlight the Production Qty cell in column H where the crossover occurs.

Problem 1E. E) The Shoemaker family is second-guessing themselves from "estimates". They believe costs would be more accurate if only the sunray model was used. From the Problem 1 raw data table, create the pivot table to capture all the average marginal costs associated with the sunray model. Use this value to enter into the Marginal Costs associated for the break-even analysis. FOR THIS VALUE, Don't round up to whole dollar value...allow it to be a 2-decimal value. Get the Unit revenue from the pivot data as well. Use $2,000 for the Fixed cost. Determine the breakeven from this model's historic values. F) Using Excel, Chart possible outcomes to reflect a visualization and breakeven point of the analysis. (USE THE VALUES FROM THE PARAGRAPH ABOVE). Label your scatter chart "Break Even". Problem 2. Manhattan Island Associates is a Supply Chain software company based in Atlanta. They offer computer training seminars on their software systems. In the seminars each student works at a personal computer, practicing the particular activity that the instructor is presenting. Manhattan Island Associates is currently planning a two-day seminar on the use of Warehouse Management Systems. The projected fee for the seminar is $600 per student. The cost for the conference room,

instructor compensation, lab assistants, and promotion is $9600. Manhattan Island Associates spends $120 for the print of materials for each student. The sales department is forecasting 24 students. A) Place the data in the appropriate data cells. B) Place the appropriate formulas given in column H to the appropriate place in column F. C) In cell C11, What is the profit for the class if 24 is obtained? Put that value in C11. C) Using Excel, what size must the class be before a break-even point is obtained? Leave that value in Cell C9. D) Using Excel, Create the scatter Chart possible outcomes to reflect a visualization and breakeven point of the analysis. Label your scatter chart "Break Even". Highlight the value of total students where the breakeven crossover occurs. D) Manhattan Island Associates has forecasted an enrollment of 24 students for the seminar. How much profit will be earned if their forecast is accurate? You do the work. Put monetary value in Cell C11. Problem 3. Management of ToysRnt4U Company needs to decide whether to introduce a certain new novelty toy for the upcoming Christmas season, then it would be discontinued. The total cost required to produce and market this toy would be $500,000 plus $15 per toy produced. The company would receive $35 for each toy sold. A) Place the data in the appropriate data cells. B) Place the appropriate formulas given in column H to the appropriate place in column F. C) Using Excel, how large must the order where a break-even point is obtained? Use the data in the word problem to solve for the correct Production Quantity. D) Create the Chart on this worksheet. You should use the range 5,000 to 40,000 as your graph points (by 5,000 increments). Using the Production Qty, the Revenue and Cost... create the scatter chart with lines to show the breakeven crossover point. Label your chart "Breakeven". Highlight the Production Qty cell in column H where the crossover occurs. For your Revenue and Cost values, re-evaluate the Y axis to "Thousands"

Problem 4. A) Place the data in the appropriate data cells from Problem 3. The Management of ToysRnt4U Company needs to decide whether to introduce a certain new novelty toy for the upcoming Christmas season, then it would be discontinued. The total cost required to produce and market this toy would be $500,000 plus $15 per toy produced. The company would receive $35 for each toy sold. B) Place the appropriate formulas given in column H to the appropriate place in column F. C) Using the same Excel spreadsheet...recalculate/reformulate the problem, using the GoalSeek function of Excel. The steps to this problem are on the Worksheet.

Problem 5. Rockwall Industries is a technology subcontractor for the United States Navy. They are providing a new computer system to be installed throughout the Navy installations. However, the computer system requires a unique harness which attaches to legacy systems. The problem is that manufacturing harnesses is not a core competency of Rockwall Industries. Accountants form Rockwall Industries have calculated that if they were manufacture the harnesses in-house, each harness would cost approximately $35 (from data from previous builds) to manufacture with fixed costs being $225,000 to establish a set-up to produce. Rockwall has also subcontracted with Steadfast Harnesses in the past. Steadfast Harnesses has quoted each unit cost to be $50 if they were to be contracted for the job. In addition, Rockwall has determined that there would be a fixed one-time administration charge from the vendor of $50,000 to do business. A) Place the data in the appropriate data cells. B) Place the appropriate formulas given in column I to the appropriate place in columns B, C and D. C) Using Excel, how many harnesses must Rockwall make themselves to be feasible versus purchasing them from an external vendor? (Cell C6) Make sure this is a whole number!!!! The forecast is that Rockwall may need between 11,600 and 11,800 for them to be feasible to do in-house. What is the profit at this point if they do it in house? Make sure this is a whole number!!!! D) Create the Scatter Line Chart on this worksheet. Using the Production Qty, Manufacturing Cost vs. the Purchasing Cost... create the scatter chart with lines to show the breakeven crossover point. Label your chart "Make or Buy". Use the Production Quantity Range of 8,000 - 17,000 in 1,000 increments. Problem 6. Stone's Throw Hospital has a large demand for occupational therapists. The annual demand requires that the therapist see 20 patients a week for 48 weeks a year to keep up with the demand. The human resources manager has determined that they could hire a therapist that would make $200 for each therapy given to each patient. Furthermore, the hospital would have to generate a benefits package that would cost them $50,000 a year to hire the therapist. The HR manager also knows that she can work with a local contracting therapy company that would provide therapy for the demand of patients for $250/each patient, plus a contract/administration fee for $5,000. A) Place the data in the appropriate data cells. B) Place the appropriate formulas given in column I to the appropriate place in columns B and D. C) What is the annual Demand (Q)? Place in cell C6. Make sure this is a whole number!!!! D) After evaluating the model. Would contract or hire in-house? Place "In-House" or "Contract" in cell C7. E) Using the graph points, create the scatter line chart to show the cross-over point for the model. Relabel the chart as "Hire In" or "Contract".

excel link

mgt 3120 assignment 1.xlsx