For the below case, need help with inputting the cost numbers for Number of units and unit cost per casting in the spreadsheet below.

RUSTIC BARN COMPANY

The Rustic Barn Company had encountered latent defects in some of its purchased rough metal barn frames. Being latent, the defects did not show up until after additional machining had taken place at rustic Barn's facility. This group of failures greatly irritated the manufacturing boss, who declared that "repairing those bad metal barn frames is eating up all our profits."When the defects were discovered, the rough metal barn frames had to be taken off the machine, the defect polished out and repair-welded, and the metal barn frame re-machined when possible. Even with this process, 15 percent of the incoming metal frames ended up as scrap. One thousand one hundred fifty (1150)raw metal frames had to be purchased and machined to produce 1000goodmachined ones. The rawmetal frames cost $500 each from either of the two suppliers. Worse than the costs associated with rework and high scrap rates were the continual changes in production scheduling necessitated by a machined metal frame not being available as scheduled for production.

These changes were costly because they required shop personnel to tear down the job they were working on and set up a new job. Marketing was constantly complaining about the firm's inability to meet delivery commitments for the finished barn products that incorporated the metal frames. Marketing claimed many sales were lost as a result of this failure. Joan Jones, the production manager, and Mary Thomas, the vice president for marketing, asked Bob Shorb, the supply manager, to investigate the costs involved in supplying finished machined metal barn frames. If finished metal barn frames were purchased, finding hidden defects would be the responsibility of the supplier. Such action would encourage the supplier to improve the metal barn frame quality. Rustic Barn Company would accept and pay only for finished, usable metal barn frames. The internal cost of machining each incoming rough metal barn frame and repair-welding and re -machining it, as necessary, was approximately $300per metal barn frame. This cost figure was based on $150 of direct labor and $150 of overhead. The cost of disrupted production schedules and operations was estimated at $100 per raw metal barn frame.

Rustic Barn estimated the costs to goon-site and work with Suppliers to correct their problems could be $20 per good metal barn frame. Marketing estimated "lost sales" costs to be $50 per good metal barn frame. Bob approached all his major suppliers of rough metal barn frames in an attempt to generate interest for the supply of finished machined metal barn frames. Only one supplier, Superior Steel, showed genuine interest. Of major concern to all the framing companies was the $150,000 investment necessary to set themselves up to machine the raw metal barn frames. Superior Steel was willing to invest in the required machines and guarantee delivery of the current demand of 1,000 units per year plus additional units necessary to meet future demand, provided Rustic Barn Company would contract with it as a sole source for the metal barn frames the next three years. The price per finished metal barn frame would be $1,000 the first year, with a potential annual increase or decrease in price tied to an appropriate economic index that would be mutually agreed to and identified in the contract.

Bob was faced with the problem of deciding whether to recommend (1) contracting with Superior Steel to buy finished metal barn frames, (2) continuing with the current process of buying rough metal frames and finishing them in-house, (3) working with current suppliers to reduce or eliminate defects or (4) developing other more attractive alternatives. The Rustic Barn machine shop was currently operating at 90 percent of capacity, so it was impossible to make a reliable estimate of what would happen in the next few months, let alone the next three years. The decision of whether to buy finished metal frames was of major dollar importance to Rustic Barn Company because the firm currently used at least 1,000 finished metal frames per year and anticipated that this usage would continue for each of the next five years at a minimum.

Rustic Barn Company - Make vs Buy Cost Analysis
			Scenario 1	Scenario 2	Scenario 3	Scenario 4	Scenario 5	Scenario 6
Cost Element	No. Units	Unit Cost Per Casting
Materials (raw castings)
Materials (finished castings)
Labor - Machining
Overhead - Machining
Production Disruption
Supplier Problem Support
Lost Sales
Machinery Investment
Total Costs:
Savings: