Learning Objectives: Spreadsheet Modeling and Analysis o What if analysis - Using Goal seek to determine Break-Even O A Spreadsheet for Outsourcing Decision o A spreadsheet Implementation of Profit model o Modeling Net Income on a Spreadsheet 1. Create an excel spreadsheet for the following: Suppose a manufacturer can produce a part for $ 10.00 with a fixed cost of $ 5000.00. The Manufacturer can contract with supplier in Asia to purchase the part for $12.00, which includes transportation. Create a simple spreadsheet to answer the following (Create a Tab for each 1a, 1b, and 1c): a. If the anticipated production volume is 1,200 units, compute the total cost of manufacturing and the total cost of outsourcing. What is the best decision? b. Find the Break-even volume using a formula. c. Find the breakeven using Goal seek. Set as like the example below: Outsourcing Decision Model Data $50,000 3125 3179 1000 = Manufactured in-house Fixed cost Unit variable cost Purchased d from supplier Unit cool Production volume Model Total manufacturing coat Total purchased Cost difference Manufacture - Purchase Best Decision 5175,000 $179.000 Marbetar 2. Babaloo Technology Inc. is a new software company that develops and markets productivity software for municipal government applications. In developing their income statements the following formulas are used Gross profit = sales - cost of sales Net operating profit= gross profit - administrative expense-selling expense Net income before taxes = net operating profits -interest expense Net income = earnings before taxes - taxes Net sales are expected to be $1,250,000. Cost of sales is estimated to be $ 300,000. Selling expenses have a fixed component that is estimated to be $90,000.00 and a variable component that is estimated to be 8% of net sales. Administrative expenses are $ 50,000.00. Interest expenses are $8,000.00 The company is taxed at a 50% rate. Develop a spreadsheet model to calculate the net income. (Create a tab 2) 3. Create the necessary formulas to calculate the mortgage payment on a house purchase. a. You will be financing $500,000 b. Your yearly interest rate is 4% c. This is a 30-year loan. Use PMT for your calculations. Create a tab 3 Example: Using a different loan amount of $ 800,000 Loan Amount 800,000.00 4.25% Annual Interest Rate Monthly interest Rate 0.35% 30 Number of Years Number of Payment 360 Payment 153,935.52) 4. Perform goal seek on the above, use you could only afford payments of $2,000.00 a month what would you need the interest rate to be? Write your answer in a new tab 4, but do not save the goal seek results. Your answer should be the original interest rate, File Home Insert Draw Page Layout Formulas Data Review View Automate Help Open in Desktop App Calibri - A A BIUROA 2 Wrap Text Merge & Center General $ % 98-99 Paste Conditional Format As Styles Tabler Cipboard Font Alg 23 B C D F H 1 E 3 For a montly payment of $ 2000.00 the annual interest rate would need to be: 1 Loan Amount 2 3 Annual Interest Rate 4 Monthly interest Rate 5 6 Number of Years 7 Number of Payment 8 9. Payment 0 1 2 3 -4 5 16 17 18 SH