can you plz show the excel formula and explain your answer and also i will have an example of the question

Question

Learning-Curve Problem A buyer does business with a supplier that uses a production process that historically demonstrates an 80 percent learning curve; that is, as production rates double, there is a 20 percent reduction in the average direct-labor hours required to produce a unit. Given this learning rate, a buyer hopes to capture this reduced labor requirement through a lower purchase price. Exhibit 12.7 outlines one use of the learning curve in purchasing. In this example, the buyer expects the per-unit price on a 600-unit order to lower from $228 to $170 because of learning. Whether the buyer actually receives a $170 unit price will probably be subject to negotiation. The supplier may argue that overhead did not change since the original order and should remain at $50 per unit. The supplier's profit is affected as both direct and overhead costs decline and profit remains at 20 percent of total costs. The buyer may coun- ter that material costs should decline because of larger volumes. The key point is that the buyer now has a price range for negotiation with the supplier. Direct labor Exhibit 12.7 Learning-Curve Problem XYZ Corporation is buying a new item produced by a process that historically demonstrates an 80 percent learning curve. A buyer has placed an order for 200 pieces and receives a quote of $228 per unit. The buyer has accumulated the following per-unit cost data: Material $90 (Five hours on average per unit at $10 per hour) (Assume 100 percent of direct labor) $50 Overhead $50 Total costs $190 (Difference between per-unit price and total costs, which equals 20 percent of total costs) Profit $38 Total per unit $228 (Quoted price) The buyer wants to place a second order for an additional 600 pieces, or a combined total order of 800. How much should the buyer expect to pay per unit given the expected benefit of the learning curve (which affects direct-labor requirements)? 1. Calculate the average labor hours for the entire combined order of 800 units: From the first order, 200 units required an average of 5 hours labor per unit. Therefore, 400 units should require only 80 percent as much as the original 200, or an average of 4 hours of labor per unit, given an 80 percent learning rate. 800 units should require an average of 3.2 hours of labor per unit (80 percent of 4 hours is 3.2 hours). One of the guidelines of learning curve is that labor costs decrease by a predictable rate each time production doubles. 2. Calculate the hours required for the total combined order of 800 units less the labor incurred for the original 200-piece order: 800 units x 3.2 average hours = unit = 2,560 total hours Less: 200 units x 5 average hours per unit = 1,000 (direct labor required for original 200-piece order) 1,560 total labor hours required for the next 600 units 3. Calculate the additional total and per-unit labor cost for the additional 600-unit order: 1,560 hours x $10 per direct labor hour $15,600 total additional labor cost $15,600/600 units - $26 per unit 4. Calculate the expected new per-unit price for the additional 600-piece order: Additional 600 pieces per-unit cost Material $ 90 (Remains unchanged, although higher quantities may reduce the per-unit material cast) Direct labor $ 26 Overhead $26 (Assume 100 percent of direct labor) Total costs Profit (20%) of total costs $ 28.40 Total per unit $142 $170.40 E F G H L M The table on the left provides direct-labor for a purchased item over increasing leverls of output. Learning-curve examples can become quite complex, especially when using logarithmic scalces to show the relatnioship between unis procudec and labor requirements. This simple example illustrates the effect on the average labor requirements because of a faily consistent rate of learning. B D 1 Simpler Learning Curve Data 2 3 Units Total Labor Hours Average Labor Hours Per Un Learing Rate 4 1 20 20 5 2 34 17 15.0% 6 4 58 14.5 14.7% 7 8 100 12.5 13.8% 8 16 168 10.5 16.0% 9 32 288 9 14.3% 10 64 493 7.703125 14.4% 11 Average Improvement: 14.7% 12 13 25 14 20 y=-2.931in(x) + 19.124 15 16 15 R2 = 0.9812 17 10 18 5 The supplier has an approximately 85% learning curve for this item, which means that as production doubles, the direct labor regired to produce a unit should decrease 15% on average. 19 0 O 10 20 30 40 50 60 70 20 21 22 23 Average Labor Hours Per Unit Average Labor Hours Per Unit Log- (Average Labor Hours Per Unit) 24 25 26 1 The buyer wants to place a third order for an additional 700 pieces, or a combined total order of 1500 2 units. 3 4 How much should the buyer expect to pay per unit given the expected benfit of the learning curve 5 (which affects direct-labor requirements)? 6 7 8 Order: 700 Labor Cost ($/hr): $ 10.00 9 Step 1: Calculate the average labor hours for the entire commbined order of 800 units. 10 First Order Labors Hours and Unit Cost 11 Units (less tl Total Labc Average l Learing Rate Total Cost ($) 200 $ 400 20.0% 800 20.0% 1600 20.0% 12 13 14 15 16 17 Step 2: Calcualte the additional total and per unit labor cost for the additional 600 unit order. 18 Items Cost ($) Notes 19 Material 20 Direct Labor Five hours on average per unit at $10 per hour 21 Overhead Assume 100% of direct labor 22 Total costs 23 Profit 20% of the total cost 24 Total per unit This is quoted price. 25