Question

Part 1: Goal Seek

Pampa Parts produces a single product, the NF-9. The product has a unit variable cost of $70 and annual fixed costs of $343,200. Pampa is subject to a 20 percent tax rate.

Suppose the NF-0 sells for $110 per unit. Using the Goal Seek function in Microsoft Excel, how many units of NF-9 must Pampa sell to earn an annual operating profit after taxes of $38,400?

Now, suppose Pampa expects to sell 8,150 units of NF-9 next year, but the price is uncertain. Using the Goal Seek function again, what must be the average price of one unit of NF-9 for Pampa to earn an annual operating profit after taxes of $38,400?

Attach an Excel spreadsheet with your model, showing your solution AND showing the values you entered in the Goal Seek program (see example below). Use TWO TABS for this problem…title one tab “Goal Seek - Units“ and one tab “Goal Seek – Price”. You should delete all other tabs.

Part 2: Solver

Layton Machining Company (LMC) manufactures two versions of a basic machine tool. One version is a standard model and one is a custom model, which requires some additional work and slightly higher-grade materials. The manufacturing process at LMC requires that each product go through two departments, Grinding and Finishing. The process in each department uses a single type of machine. Total machine capacity in Grinding is 50,000 hours, and in Finishing, total machine capacity is 30,000 hours. (Each department has multiple machines.) Total market demand is limited to 100,000 standard units and 120,000 custom units monthly. LMC is currently producing 90,000 standard units and 50,000 custom units each month. Cost and machine-usage data for the two products follow. I have given you the basic model…you need to finish with formulas in the appropriate cells and run Solver. Note that you have MULTIPLE constraints…you have time constraints on two different machines; you have market constraints on both products; and don’t forget you can’t make a negative number of a product. Please place this model on the third tab of your workbook and label the tab LMC – Solver. Below the model with your solution, please LIST ALL the constraint formulas you used (and don’t forget to answer part B!)

Standard $ 6.00 Custom $ 8.00 Price Less variable costs per unit Material Labor Overhead Contribution margin per unit $ $ $ 1.50 1.25 1.75 $ $ $ 2.00 1.50 2.50 Fixed costs Manufacturing Marketing and administrative Total $ $ 76,000 37,000 0.2 2 0.4 Grinding machine hours per unit Finishing machine hours per unit Grinding maching hours used Grinding machine hours available Finishing machine hours used Finishing machine hours available 50,000 30,000 Quantity produced Maximum demand Profit 100,000 120,000

1. What is the optimal production schedule for LMC? In other words, how many standard units and custom units should the company produce each month to maximize monthly profit?
2. If LMC produces at the level found in (A) above, how much will monthly profit increase over the current production schedule (90,000 Standard and 50,000 Custom)?

Answer 1

1) Following is the calculation in excel using goal seek

a)

2 Units 3 SP 4 VC 5 AFC Tax Rate 110 Total SP $70 Total VC 343,200 Annual Fixed Cost 20% Tax Amount Profit +B3*B2 +B4*B2 343,200 +(D3-(D4+05))*0.2 +D3-(SUM(D4:06))

А в E F G H 2 Units Goal Seek 3 SP 4 VC 110 Total SP $70 Total VC 343,200 Annual Fixed Cost 20% Tax Amount Profit Set cell: SD57 To value: 38400 By changing cell: SBS2 AFC 6 Tax Rate 343,200 (68,640) (274,560) OK | Cancel

| A 2 Units 3 SP $1,075,800 4 VC AFC 6 Tax Rate 9,780 110 Total SP $70 Total VC 343,200 Annual Fixed Cost 20% Tax Amount Profit $684,600 $343,200 $9,600 $38,400

9,780 units needs to be sold to get the profit of $38,400

b)

AB E F G H Units Goal Seek SP $0 SD$7 4 VC 5 AFC Tax Rate 8,150 Total SP $70 Total VC 343,200 Annual Fixed Cost 20% Tax Amount Set cell: To value: 38400 By changing cell: $B$31 $570,500 $343,200 ($182,740) ($730,960) OK Cancel Profit

А 3 Units SP VC 8,150 118 Total SP $70 Total VC 343,200 Annual Fixed Cost 20% Tax Amount Profit 5 AFC $961,700 $570,500 $343,200 $9,600 $38,400 Tax Rate

The SP per unit must be 118 to generate the profit of $38,400, if the estimated selling is 8,150

Please note:

SP = Selling Price

VC = Variable Cost

AFC = Annual Fixed Cost

2) Following is the solution in excel using solver

a)

G H SUM - X for =+SUMPRODUCT(B12:C12,B3:03)-B10-B11 2. A Standard Model Custom Model 3 Quantity Produced 4 Price 5 Less: Varaible Cost 6 Material 7 Labor 1.5 8 Overhead 2.5 9 Less Fixed Cost 10 Manufacturing 76,000 11 Marketing and Administrative 37,000 12 Net Profit |+B4-(SUM(B6:38)) +C4-(SUM(C6:C8)) 2 1.25 1.75 Total Profit =+SUMPRODUCT(B12:C12,B3:03)-B 10-B11 0.2 14 Constraints 15 Grinding 16 Finishing 17 Demand for Standard Model 18 Demand for Custom Model 0.1 0.3 +SUMPRODUCT(B15:C15,$B$3:$C$3) 0.4 +SUMPRODUCT(B16:C16,$B$3:$C$3) +SUMPRODUCT(B17017,$B$3:$C$3) +SUMPRODUCT(B18:C18,$B$3:$C$3) 50,000 30,000 100,000 120,000

F12 Solver Parameters A Set Objective: SF5121 Standard Model Custom Model To: Max Min O Value of: By Changing Variable Cells: $B$3:5CS3 1.5 1.25 1.75 Subject to the Constraints: $D$15:5D518 <= $E$15:SE$18 Add 2.5 Change Delete 3 Quantity Produced 4 Price 5 Less: Varaible Cost 6 Material 7 Labor 8 Overhead 9 Less Fixed Cost 10 Manufacturing 11 Marketing and Administrative 12 Net Profit 13 14 Constraints 15 Grinding 16 Finishing 17 Demand for Standard Model 18 Demand for Custom Model 76,000 37,000 2.0 1.5 Total Profit (113,000) Reset All Load/Save Make Unconstrained Variables Non-Negative 0.2 0.3 Select a Solving Method: GRG Nonlinear Options 0.4 50,000 30,000 100,000 120,000 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver 10 nroblems that are non-smooth

✓ 822 x 4 А 2 3 Quantity Produced fx в Standard Model 100,000 Custom Model 50,000 4 Price 1.5 1.25 1.75 1 5 Less: Varaible Cost 6 Material 7 Labor & Overhead 9 Less Fixed Cost 10 Manufacturing 11 Marketing and Administrative 12 Net Profit 13 14 Constraints 15 Grinding 16 Finishing 17 Demand for Standard Model 18 Demand for Custom Model 76,000 37,000 2.0 1.5 Total Profit 137,000 0.2 0.1 3 0. 0.4 3 5,000 30,000 100,000 50,000 50,000 30,000 100,000 120,000 10
Standard Model and Custom model must be produced 100,000 and 50,000 units respectively to generate a profit of $137,000

b)

А Standard Model Custom Model 90,000 50,000 2 3 Quantity Produced 4 Price 5 Less: Varaible Cost 6 Material 7 Labor Overhead 9 Less Fixed Cost 10 Manufacturing 11 Marketing and Administrative 12 Net Profit 1.5 1.25 1.75 1.5 2.5 76,000 37,000 2.0 1.5 Total Profit 122,000

The profit will be $122,000 if standard and custom model are produced 90,000 and 50,000 respectively.