Prepare a make or buy analysis. 3 Prepare a make or buy analysis - Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW vIEw Calibri ' 9r, 4:1 g _ I '3 :_ H .- F's - g - Alignment Number Conditional Formatas Cell Cells Editing T T Formatting' Table' Styles' T T Clipboard l'. ' F- Styles F25 A B C D E F G H 1 Alanco, Inc. manufactures a variety of products and is currently maunfactu ring all of its own component parts. 2 An outside supplier has offered to sell one of those components to Alanco. To evaluate this offer, the following 3 information has been gathered relating to the cost of producing the component internally: 4 Direct materials 4.00 Direct labor 6.00 Variable manufacturing overhead 2.00 Fixed manufacturing overhead, traceable' 5.00 Fixed manufacturing overhead, common but allocated 8.00 Total cost A Supplier price 2100 Units used per year 12,000 *Fixed manufacturing overhead, traceable is composed of two items: Depreciation of equipment [no resale value} 30% Supervisor salary ?0% 1. Assuming the company has no alternative use for the facilities now being used to produce the component, complete the following analysis to determine if the outside supplier's offer should be accepted. Per Unit Differential Cost 12,000 units nil { READY Altem pl{s) 5/5 23 Per Unit Differential Cost 12 000 units 24 Make Buy Make Buy 25 26 ' Cost of purchasmg I II 2? Direct materials 23 Direct labor 29' Variable manufacturing overhead 30 Fixed manufacturing overhead, traceable 3'1 Fixed manufacturing overhead. common 32 Total costs 33 3'1 Based on this analysis. write an if statement to determine if Alanco should make or but.r the component. 35 35 Alanco should the component 3? 33 39 40 Sheetl win 4 } READY Altem pills) 5/5 32 33 34 35 36 3? 33 39 40 J11 12 :1 3 dd 15 16 (1? 18 49 50 51 52 53 Compute the following liquidity ratios for 2016. Round to the required number of decimal places USING THE EXCEL. ROUND FUNCTION x}. Current ratio [rounded to one decimal place] Receivables turnover {rounded to two decimal places} Days to collect {rounded to the nearest whole day} Inventory turnover {rounded to two decimal places} Davs' sales in inventory {rounded to the nearest day} Compute the following Solvency Ratios for 2016. Again, USE THE EXCEL ROUND FUNCTION [fa]. Debt to assets [rounded to two decimal places} Times interest earned {rounded to one decimal place} Compute the following Protability Ratios for 2016. Again. USE THE EXCEL ROUND FUNCTION [hi]. Prot margin ratio (rounded to one decimal place} Gross margin ratio [rounded to one decimal place} i > Sheetl .+. READY Altem pt{s) to 1 mes days m days to 1 times percent percent 1. Compute and interpret nancial ratios that managers use to assess liquidity. 2. Compute and interpret nancial ratios that managers use for asset management purposes. 3. Compute and interpret nancial ratios that managers use for debt management purposes. Selected Financial Ratios Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW _ Li" A = % Paste ,2: , A v Alignment Number Conditional Format 35 CE\" Cells Editing _ ' ' Formatting' Table' Styles' ' ' Clipboard IT- Styles A1 fr Recent nancial statements for Zimmer, Inc. are as follows: B C | E lRecent nancial statements for Zimmer, Inc. are as follows: ll Zimmer Company Zimmer Company Comparative Balance Sheet Income Statement This Year This Year Asset: Current assets: Cash Sales {all on account} 5 4,980,000 Marketable securities Cost of goods sold 2388:000 Accounts receivable, net Gross margin 1.902900 1 2 3 4 5 6 7 B 9 l-' 0 Inventory Selling and administrative expenses H H Other current assets Operating expenses 985. 500 H N Total current assets Depreciation and amortization 110, 500 I-' I'D Plant and equipment. net General and administrative expenses 150 000 I-' .b Other assets Total selling and administrative expenses 1:546:000 p U1 Total assets Net operating income 446,000 liabilities and Stockholders' Equity Interest expense 50 000 Current liabilities: Net income before taxes 385,000 iIiIiI mum Accounts payable lnco me taxes 135:100 Shortterm bank loans Net inoome w Accrued payables NH OLD 21 Other current liabilities Additional information: 22 Total current liabilities Dividend per share paid in current year 23 Bonds u . able 10% Marloet price of shares at year end READY E] I+ 100% mm - _ 1. Compute and interpret nancial ratios that managers use to assess liquidity. 2. Compute and interpret nancial ratios that managers use for asset management purposes. 3. Compute and interpret nancial ratios that managers use for debt management purposes. Selected Financial Ratios Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW 3* ' ' E " 3 Callbri 1D A A _ % 7'" L53. E] H a I u - '- Ee] - ' __3 ' ft) , A , Alignment Number Conditional Format as Cell Cells Editing ' _ Paste ' ' Formatting' Table' Styles' ' ' Clipboard r. Font '7- Styles Al . Recent nancial statements for Zimmer, Inc. are as follows: B C I E Total liabilities Par value otcommon stock per share Stockholders' equity: Common stock Number of shares outstanding Retained earnings Total stockholders' equity Total liabilities and stockholders' equityI g'l Required: Compute the following ratios for the current year only: Gross margin percentage Current rat'lo [rounded to two decimal places} Acid-test ratlo (rounded to two decimal places} Accounts receivable turnover {rounded to two decimal places] Average collection period {rounded to the nearest wtiole day} Inventory turnover ratio [rounded to two decimal places] Average sales period [rounded to the nearest whole day] Debt-toequity rao {rounded to two decimal places} Times interest earned (rounded to two decimal places] Book value per share {rounded to two decimal places} Earnings per share {rounded to two decimal places} Dividend payout ratio [rounded to three decimal places] Dividend yield ratio {rounded to three decimal places} Price-earnings ratio [rounded to two decimal placsl READY @ El -I+ 100% mm - - \f23 24 Other budgeted cash disbursements in March 25 Equipment purchases $14,000 25 Dividends to be paid $2,000 27 28 Minimum cash balance to be maintained $10,000 29 March 1 cash balance $11,500 30 March 1 outstanding borrowings $0 31 March 1 interest due 50 32 33 The company has a line of credit available to bolster the cash balance as needed. 34 35 When preparing budgets, the companvir maintains their data on a separate sheet from the actual budget and schedules. 36 37 Click the Schedules and Cash Budget tab to prepare the following: 33 1. Schedule of expected cash collections for March. 39 2. Schedule of expected cash disbursements for inventory purchases for March. 40 3. Cash budget for March. Indicate in the nancing section antiI borrowing that will be needed in March. Assume that 41 any interest will not be paid until the following month. 42 43 44 45 Budget Data Schedules and Cash Budget Iii-ELI 4 y READY @ El -I+ 100% Anew-m - \f1. Determine the break-even point. 2. Compute the margin of safety and explain its significance. 3. Compute the degree of operating leverage at a particular level of sales and explain how it can be used to predict changes in net operating income. CVP Analysis - Excel ? 5 X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 36 Calibri 11 - A" A" % Paste BIU - - - A - Alignment Number Conditional Format as Cell Cells Editing Formatting * Table Styles Clipboard G Font Styles A vix v fx Given the following information complete a CVP analysis A B C D E F A 16 CM ratio 17 Variable expense ratio 18 19 2. Compute the break-even point 20 Break-even in unit sales units 21 Break-even in dollar sales 22 23 3. Compute the margin of safety 24 Margin of safety in dollars 25 Margin of safety percentage 26 27 4. Compute the degree of operating leverage 28 Sales 29 Variable expenses 30 Contribution margin 31 Fixed expenses 32 Net operating income 33 34 Degree of operating leverage 35 36 37 38 Sheet1 ... + READY -- + 100% Attempt(s) Hint Show Me\f13 19 Required: 20 1. Use appropriate Excel formulas to perform vertical analysis and complete the "Percent" columns 21 for both product lines above. Express each amount as a percentage of net sales. 22 Note: You should use absolute references in the divisors of all formulas. 23 2. Use the Excel IF function and the completed vertical analysis above to answer each of the 2'1 questions below: 25 25 Which product line has the highest gross prot 96? E 27 Which product line has the highest net income %? 23 29 3D 31 4 r Sheetl .+. 1 r READY Attem ps) \f1. Use ratios to analyze a company's liquidity and solvency. m B h ' C' ' Vb ' : Calculate Select Ratios - Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW I . . - - ... . Calibri ' : 99, 4:? 5 _ . _. .__- Alignment Number Conditional Formatas Cell Cells Editing Formatting' Talole' Styles' Clipboard ' Styles A1 . '- Condensed nancial statements for Ga mes Galore are summarized below: B Gross Prot 1,082,000 Operating Expenses 891,400 Income from Operations 190,600 Interest Expense 14,500 Income before Income Tax Expense 1?6,100 Income Tax Expense 4?,000 Net Income 129,100 Required: Compute the following liquidity ratios for 2016. Round to the required number of decimal places USING THE EXCEL. ROUND FUNCTION [fit]. Current ran'o [rounded to one decimal place] Receivables turnover [rounded to two decimal places} Days to collect {rounded to the nearest whole day} Inventory:r turnover {rounded to two decimal places} Davs' sales in inventoryI {rounded to the nearest day} Compute the following Solvenwr Ratios for 2016. Again, USE THE EXCEL ROUND FUNCTION [ht]. Debt to assets [rounded to two decimal places} Times interest earned [rounded to one decimal place} Comoute the following Protabilitv Ratios for 2016. Again. USE 1 r Sheetl If READY Attern pills)