Please help with this question

Selected Items from McEwan Industries' Financial Statements (Millions of Dollars) Annual sales Cost of goods sold Inventories Accounts receivable Accounts payable $150 $122 $17 $51 $13 INPUTS Days/year Interest rate on NOWC loans (10%) 365 10% a) Using the New Target Conversion Periods, find the reduction in NOWc, increase in FCF and reduction in interest expenses. Assume no change in Sales and COGS. New Targets for conversion Periods Inventory conversion period (ICP, days) Average collection period (ACP, days) Payable deferral period (PDP days) Cash Conversion Cycle (CCC, days) Old (Actual New Target 50.9 40.0 124.1 40.0 38.9 50.0 136.1 Reduction in Cash Conversion Cycle: 30.0 Impact of Reduction in CCC Annual sales: Costs of goods sold (COGS): Inventory: Receivables: Payables: Net operating working capital: NOWC = Inventory + Receivables - Payables Interest expense due to NOWC: interest rate on NOWC loans *(NOWC) Old (Actual) New Target $150.00 $122.00 $17.00 $51.00 $13.00 Improvement in Selected Results Reduction in NOWC: Increase in free cash flow: Reduction in interest expenses b) McEwan Industries sells on terms of 2/10, net 25. Suppose Total sales for the year are $150 million; 40% of the customers pay on the 10th day and take discounts, while the other 60% pay, on average 83 days after their purchases. Assume 365 days in year for your calculations. 2.00% 10 25 $150,000,000 365 INPUTS Discount% Discount period (in days) Amount due (in days) Total sales Number of days in year % of customers that take discount and pay on discount day % of customers that pay after discount period Average days after purchase by nondiscount customers 40.00% 60.00% 83 What is the days sales outstanding? Days sales outstanding (DSO) What is the average amount of receivables? Average receivables What is the percentage cost of trade credit to customers who take the discount? Cost to discount customers What is the nominal and effective cost of trade credit to customers who do not take the discount and pay in 83 days? Nominal cost to nondiscount customers paying late Effective cost to nondiscount customers paying late on Day BRO What would happen to McEwan's accounts receivable ifit toughened up on its collection policy with the result that all nondiscount customers paid on the 25th day? New days sales outstanding (DSO) Average receivablesxew c) McEwan Industries borrows $10,000,000 for one year 1) @10% simple interest, paid monthly. What is the effective annual interest rate? 8 points possible Inputs: Amount borrowed Stated annual rate Months per year $10,000,000.00 10.00% 12 Effective annual interest rate: 2) McEwan Industries borrow $10,000,000 for one year @10% add-on interest. What is the effective annual interest rate? $10,000,000.00 10.00% 12 Amount borrowed Stated annual rate Payments per year Months loan will be outstanding Stated rate per month Total interest: Total loan: Monthly payment: Rate/month: Annual rate/12 (Stated rate/month)(Borrowed)(no. of months) Effective annual interest rate: ABC AutoSave ON n = 2 = ABC 6 Excel Project 3 - Saved Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X LO Cambria 10 . Insert v = = ab General 1. HH $ WE 48- O 5 DX Delete v Paste 1 B U A $ % ) V 00 .00 0 Ideas & Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select Sensitivity Format R59 4 x fx H H 1 L M N 0 P Q 0 R R S. U V w X Y A B D E F F G 82 83 84 2) McEwan Industries borrow $10,000,000 for one year @ 10% add-on interest. What is the effective annual 85 interest rate? 86 87 Amount borrowed $10,000,000.00 88 Stated annual rate 10.00% 89 Payments per year 12 90 Months loan will be outstanding 91 Stated rate per month Annual rate/12 92 Total interest: (Stated rate/month)(Borrowed)(no. of months) 93 Total loan: 94 Monthly payment: 95 Rate/month 96 97 Effective annual interest rate: 98 99 100 101 d) Which loan should McEwan Industries choose? 102 103 Choose Loan 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 Start Here Part 1 Part 2 Part 3 Grading + 17 2 point possible 1 point possible 3 + 100% Selected Items from McEwan Industries' Financial Statements (Millions of Dollars) Annual sales Cost of goods sold Inventories Accounts receivable Accounts payable $150 $122 $17 $51 $13 INPUTS Days/year Interest rate on NOWC loans (10%) 365 10% a) Using the New Target Conversion Periods, find the reduction in NOWc, increase in FCF and reduction in interest expenses. Assume no change in Sales and COGS. New Targets for conversion Periods Inventory conversion period (ICP, days) Average collection period (ACP, days) Payable deferral period (PDP days) Cash Conversion Cycle (CCC, days) Old (Actual New Target 50.9 40.0 124.1 40.0 38.9 50.0 136.1 Reduction in Cash Conversion Cycle: 30.0 Impact of Reduction in CCC Annual sales: Costs of goods sold (COGS): Inventory: Receivables: Payables: Net operating working capital: NOWC = Inventory + Receivables - Payables Interest expense due to NOWC: interest rate on NOWC loans *(NOWC) Old (Actual) New Target $150.00 $122.00 $17.00 $51.00 $13.00 Improvement in Selected Results Reduction in NOWC: Increase in free cash flow: Reduction in interest expenses b) McEwan Industries sells on terms of 2/10, net 25. Suppose Total sales for the year are $150 million; 40% of the customers pay on the 10th day and take discounts, while the other 60% pay, on average 83 days after their purchases. Assume 365 days in year for your calculations. 2.00% 10 25 $150,000,000 365 INPUTS Discount% Discount period (in days) Amount due (in days) Total sales Number of days in year % of customers that take discount and pay on discount day % of customers that pay after discount period Average days after purchase by nondiscount customers 40.00% 60.00% 83 What is the days sales outstanding? Days sales outstanding (DSO) What is the average amount of receivables? Average receivables What is the percentage cost of trade credit to customers who take the discount? Cost to discount customers What is the nominal and effective cost of trade credit to customers who do not take the discount and pay in 83 days? Nominal cost to nondiscount customers paying late Effective cost to nondiscount customers paying late on Day BRO What would happen to McEwan's accounts receivable ifit toughened up on its collection policy with the result that all nondiscount customers paid on the 25th day? New days sales outstanding (DSO) Average receivablesxew c) McEwan Industries borrows $10,000,000 for one year 1) @10% simple interest, paid monthly. What is the effective annual interest rate? 8 points possible Inputs: Amount borrowed Stated annual rate Months per year $10,000,000.00 10.00% 12 Effective annual interest rate: 2) McEwan Industries borrow $10,000,000 for one year @10% add-on interest. What is the effective annual interest rate? $10,000,000.00 10.00% 12 Amount borrowed Stated annual rate Payments per year Months loan will be outstanding Stated rate per month Total interest: Total loan: Monthly payment: Rate/month: Annual rate/12 (Stated rate/month)(Borrowed)(no. of months) Effective annual interest rate: ABC AutoSave ON n = 2 = ABC 6 Excel Project 3 - Saved Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X LO Cambria 10 . Insert v = = ab General 1. HH $ WE 48- O 5 DX Delete v Paste 1 B U A $ % ) V 00 .00 0 Ideas & Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select Sensitivity Format R59 4 x fx H H 1 L M N 0 P Q 0 R R S. U V w X Y A B D E F F G 82 83 84 2) McEwan Industries borrow $10,000,000 for one year @ 10% add-on interest. What is the effective annual 85 interest rate? 86 87 Amount borrowed $10,000,000.00 88 Stated annual rate 10.00% 89 Payments per year 12 90 Months loan will be outstanding 91 Stated rate per month Annual rate/12 92 Total interest: (Stated rate/month)(Borrowed)(no. of months) 93 Total loan: 94 Monthly payment: 95 Rate/month 96 97 Effective annual interest rate: 98 99 100 101 d) Which loan should McEwan Industries choose? 102 103 Choose Loan 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 Start Here Part 1 Part 2 Part 3 Grading + 17 2 point possible 1 point possible 3 + 100%