| EGEE-401 FINAL PROJECT - BUSINESS PLAN |
| Names of Group Members who contributed his/her fair share to the project: |
| | Possible Range |
| FACTS | Low | High |
| Semi Company (makes one tool model only) |
| 550,000,000 | | | $ Annual Revenue |
| 4,500,000 | | | $ Tool Price |
| 40% | | | % Market Share |
| 60 | | | Seconds Processing time per Wafer |
| 200 | | | Hours MTBF of complete tool |
| 4 | | | Hours MTTR to repair complete tool |
| 26,000 | | | $ Scrap/y due to tool manfunctions |
| 5 | | | Number of actuators per tool |
| 7 | | | Years life of tool, product, and loan duration |
| 8% | | | % MARR Minimum Attractive Rate of Return (same for Motion) |
| 8% | | | % Interest on interest-only loan by Semi to Motion |
| 78% | | | % Engineering Cost Factor to qualify and design-in a new component (as a % of annual demand of the existing component) |
| 5000 | | | $ Unit Price of Old Actuator |
| 10,000 | | | Hours MTBF of Old Actuator |
| 5 | | | Years (Term of interest-only capital loan to Motion Co) |
| 80% | | | % of capital covered by loan |
| Motion Company (Makes superior wafer-lift actuator) |
| 1 | | | Seconds: New Actuator Processing-Time Savings per Wafer |
| 26,000 | | | Hours: MTBF of new actuator |
| 11% | | | % reduction in the probability of tool malfunction leading to scrapped wafers |
| 950,000 | | | $ Capital Equipment Required |
| 7 | | | Years useful life of Capital Equipment |
| 1,900 | | | $/Actuator Materials Cost |
| 2,100 | | | $/Actuator Labor Cost |
| 1,400,000 | | | $ Other Direct Cost (building & utilities) |
| 5% | | | % Indirect Costs/Revenue |
| 40% | | | % Tax Rate |
| 5 | | | Weeks-of-Sales Cash-Asset Requirement |
| 4 | | | Weeks-of-Sales Receivables-Asset Requirement |
| 3 | | | Weeks-of Cost-of-Goods-Sold Finished-Goods-Inventory Asset Requirement |
| 5 | | | Weeks-of-Materials-Expense Materials-Inventory Asset Requirement |
| 4 | | | Weeks-of-Materials-Expense Accounts-Payable Liability |
| ECONOMIC VALUE |
| Value per actuator of throughput improvement |
| | 1% | 2% | % Processing Time Savings |
| | 50,000 | 100,000 | $ Value of throughput improvement per tool |
| | 15,000 | 30,000 | $ Value of throughput improvement per actuator |
| Value per actuator of Tool Availability improvement |
| | 95% | 100% | %Availability of tool with old actuator = MTBF/(MTBF+MTTR) |
| | 200 | 220 | Hours MTBF of tool with new actuators = ((Current MTBF of Tool)^-1 - 4*(MTBF of Old Actuator)^-1 + 4*(MTBF of New Actuator)^-1)^-1 |
| | 95% | 100% | %Availability of tool with new actuator = MTBF/(MTBF+MTTR) |
| | 800 | 1,500 | $ Savings per actuator =(%Down-time reduction)(Tool Price)/(Number of Actuators) |
| Value per actuator of scrap reduction |
| | 15,000 | 25,000 | Value of scrap reduction over the life of a tool |
| | 3,000 | 6,000 | $ Savings per actuator |
| Cost for Semi to change and requalify the design with the new actuator |
| | 80 | 150 | Number tools produced per year (Semi Annual Revenue)/(Tool Price) |
| | -250 | -1,000 | $ Cost of engineering design change to incorporate new actuators =(Egr Cost Factor)(Old Actuator Price)(A/P,i,Life) |
| Net Economic Value to Semi (per actuator) by switching to the new actuator |
| | 15,000 | 30,000 | Sum of above values |
| Actuator Demand |
| 611 | 300 | 900 | Actuator Units/y = [Tools/y Production Rate](Actuators/tool) = [(Semi Revenue)/(Tool Price)](Actuators/Tool) |
| ECONOMIC VALUE PRICING |
| | 17,000 | 35,000 | Price per actuator that would capture all economic value for Motion = (Net Economic Value to Semi) + (Price of old actuator) |
| | 4,000 | 10,000 | Price/actuator that gives Motion Return-on-Assets = MARR (i.e. lowest possible price) (Use "Goal Seek" to temporarily set A135 = MARR by changing A67 ) |
| | 3,000 | 10,000 | Price per actuator currently paid by Semi |
| 9,500 | 4,000 | 35,000 | Price per actuator proposed to Semi (Manually enter your price) |
| INCOME STATEMENT - MOTION COMPANY (forecast for each year) |
| OPERATING REVENUE |
| | 4,000,000 | 16,000,000 | $ Total Operating Revenue (Actuator Demand)(Price/Actuator) |
| OPERATING EXPENSES |
| Direct Cost of Goods Sold |
| | 900,000 | 1,200,000 | $ Labor Cost |
| | 900,000 | 1,200,000 | $ Material Cost |
| | 1,000,000 | 2,000,000 | $ Other Direct Cost |
| | 100,000 | 200,000 | $ Depreciation of Capital Equipment |
| | 3,000,000 | 4,600,000 | Total Cost of Goods Sold |
| Gross Profit |
| | 700,000 | 13,000,000 |
| Indirect Costs |
| | 200,000 | 1,200,000 | Selling, General, & Administrative |
| | 100,000 | 300,000 | Interest expense on debt to Semi |
| | 300,000 | 1,200,000 | Total Indirect Costs |
| Net Income Before Taxes |
| | 600,000 | 13,000,000 |
| Taxes |
| | 150,000 | 6,000,000 |
| NET PROFIT |
| | 200,000 | 7,000,000 |
| BALANCE SHEET - MOTION COMPANY (On First Day of Operation) |
| ASSETS |
| Current Assets |
| | 300,000 | 1,300,000 | Cash |
| | 300,000 | 1,300,000 | Accounts Receivable |
| | 250,000 | 400,000 | Inventory (Finished Goods) |
| | 50,000 | 150,000 | Inventory (Materials) |
| | 900,000 | 4,000,000 | Total Current Assets |
| Fixed Assets |
| | 700,000 | 1,200,000 | Equipment |
| Total Assets |
| | 1,600,000 | 5,000,000 |
| LIABILITIES & EQUITY |
| Current Liabilities |
| | 50,000 | 100,000 | Accounts payable |
| Long-term Liabilities |
| 1,000,000 | 1,350,000 | 3,500,000 | Debt to Semi |
| | 1,400,000 | 4,000,000 | Total Liabilities |
| Equity |
| | 200,000 | 800,000 | Stock |
| - 0 | | | Retained Earnings (None) |
| | 200,000 | 800,000 | Total Equity |
| Total Liabilities and Equity |
| | 1,600,000 |
| METRICS |
| Measures of Financial Health |
| | 500,000 | 3,500,000 | Working Capital |
| | 1.0 | 40 | Current Ratio (must be >2 |
| | 1.0 | 40 | Acid Test Ratio (must be >1) |
| | 1% | 250% | % Return on Assets (Must be > MARR) |
| | 1% | 1200% | % Return on Equity (Must be > MARR) |
| | 1.0 | 60 | Interest Coverage (Must be >3) |
| | 10.0% | 80% | Gross Margin |
| | 1.0% | 50% | Net Profit Ratio (Margin on Sales) |
| Pre-tax IRR to Semi (from loan and net benefits) |
| Benefit to Semi | | | Year |
| | (3,000,000) | (1,350,000) | 0 | Loan amount |
| | 200,000 | 13,000,000 | 1 | Loan payment + Economic Benefits [Econ Benefits = (Max Price - Actual Price)(Annual Qty)] |
| | 200,000 | 13,000,000 | 2 | Loan payment + Economic Benefits [Econ Benefits = (Max Price - Actual Price)(Annual Qty)] |
| | 200,000 | 13,000,000 | 3 | Loan payment + Economic Benefits [Econ Benefits = (Max Price - Actual Price)(Annual Qty)] |
| | 200,000 | 13,000,000 | 4 | Loan payment + Economic Benefits [Econ Benefits = (Max Price - Actual Price)(Annual Qty)] |
| | 200,000 | 13,000,000 | 5 | Loan payment + Economic Benefits [Econ Benefits = (Max Price - Actual Price)(Annual Qty)] |
| | 200,000 | 13,000,000 | 6 | Loan payment + Economic Benefits [Econ Benefits = (Max Price - Actual Price)(Annual Qty)] |
| | 3,000,000 | 13,000,000 | 7 | Loan payment + Economic Benefits + Loan Payoff |
| | - 0 | 75,000,000 | Net Present Value =NPV(A137:A144) |
| | 7% | 1000% | IRR |
| | 500,000 | 20,000,000 | Annual Profit if Motion could sell to all Semi competitors = (Motion Profit)/(Semi Market Share) |
| | 5,000,000 | 170,000,000 | Value of Patent = PV(Profits from serving the whole market over the 15 year patent life) |
abdlaz3
17-BusinessPlanFinancialsWorksheet20180321.xlsx
