Use Excel to do this. Please show how it looks like and show the formulas . You

Question

Use Excel to do this. Please show how it looks like and show the formulas. You may use Ctrl ~ in Excel to show formulas)

SCR Manufacturing, LLC makes a variety of parts for the Machine Tool Industry. One of these parts (MIR 144) does not seem to be contributing to profits. The selling price of the part is $27.85, the variable cost is $19.00 and the Fixed Cost for this product is $152,000 per year. In the past fiscal year, 14,500 of this product was produced and sold.

A.Construct a correct, flexible and documented spreadsheet model that will allow varying inputs and resulting revenue, costs and profit. Check the base case above to see if the company is losing on this part.

B.Using the model and Goal Seek, find the break even quantity.

C.Construct a data table and chart showing profit vs. quantity.

D.The owner is trying to determine where to put her efforts in attempting to increase the profitability for MIR 144. Should she increase sales volume by 20% or reduce the variable cost by 15%. Using the model, determine which method would increase profitability more. Show your calculations that helps to answer the question.

Explanation / Answer

a)

Since, the Profit/Loss value is negative if checked for the above case, thus the company is losing on this part.

b) Using Goal Seek,

the break even quanity is 17175 units per year.

c) For the purpose of a more detailed graph, I have added more quanity values:

Using columns Quanity and Profit for the graph:

d)

PART QUANTITY SP VC FC (ANNUAL) REVENUE COST PROFIT/LOSS MIR 144 14500 $     27.85 $     19.00 $ 1,52,000.00 $ 4,03,825.00 $ 4,27,500.00 $ -23,675.00 Formulas used: Revenue = Quanity * SP Cost =(VC* Quantity) + FC Profit/Loss = Revenue - Cost

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.