Please help
AutoSave ON BE ABC svu ExcelProject 3 - Last Modified: November 5 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X Cambria Insert v 10 . = = ab General WE 48- O 5 DX Delete Paste B 1 U A $ % ) V Ideas Conditional Format Formatting as Table Cell Styles .00 0 Sort & Filter Sensitivity Format Find & Select L26 4 x fx G H H 1 K L L M N 0 P R S U V w X Y 8 points possible A B D E F 1 Part 2 2 3 Selected Items from McEwan Industries' Financial Statements (Millions of Dollars) 4 5 Annual sales $150 6 Cost of goods sold $122 7 Inventories $17 8 Accounts receivable $51 9 Accounts payable $13 10 11 INPUTS 12 Days/year 365 13 Interest rate on NOWC loans (10%) 10% 14 15 a) Using the New Target Conversion Periods, find the reduction in NOWC, increase in FCF and reduction in 16 interest expenses. Assume no change in Sales and COGS. 17 18 19 New Targets for Conversion Periods Old (Actual) New Target 20 Inventory conversion period (ICP, days) 50.9 40.0 21 Average collection period (ACP, days) 124.1 40.0 22 Payable deferral period (PDP, days) 38.9 50.0 23 Cash Conversion Cycle (CCC, days) 136.1 30.0 24 Reduction in Cash Conversion Cycle: 25 26 Impact of Reduction in CCC Old (Actual) New Target 27 Annual sales: $150.00 28 Costs of goods sold (COGS): $122.00 29 Inventory: $17.00 30 Receivables: $51.00 31 Payables: $13.00 32 Net operating working capital: 33 NOWC = Inventory + Receivables - Payables 34 Interest expense due to NOWC: interest rate on NOWC loans *(NOWC) 35 36 Improvement in Selected Results 37 Reduction in NOWC: 38 Increase in free cash flow: 39 Reduction in interest expense: Start Here Part 1 Part 2 Part 3 Grading + 17 3 a + 100% AutoSave ON BE ABC svu ExcelProject 3 - Last Modified: November 5 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X LO Cambria 10 Insert v . Ai = = ab General WE 48- O 5 Paste I U B A == $ % ) 00 .00 0 Conditional Format Formatting as Table Cell Styles DX Delete F Format Ideas Sort & Filter Find & Select Sensitivity L26 4 x fx B D E E F G H 1 K L L M N 0 P Q a R s V w X Y 41 b) McEwan Industries sells on terms of 2/10, net 25. Suppose Total sales for the year are $150 million; 40% of the 42 customers pay on the 10th day and take discounts, while the other 60% pay, on average, 83 days after their 43 purchases. Assume 365 days in year for your calculations. 8 points possible 44 45 INPUTS 46 Discount% 2.00% 47 Discount period (in days) 10 48 Amount due (in days) 25 49 Total sales $150,000,000 50 Number of days in year 365 % of customers that take discount 51 and pay on discount day 40.00% % of customers that pay after 52 discount period 60.00% Average days after purchase by 53 nondiscount customers 83 54 55 What is the days sales outstanding? 56 Days sales outstanding (DSO) 57 58 What is the average amount of receivables? 59 Average receivables 60 61 What is the percentage cost of trade credit to customers who take the discount? 62 Cost to discount customers 63 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 64 customers paying late Effective cost to nondiscount 65 customers paying late on Day 66 67 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 68 (DSO) 69 Average receivablesxew 70 71 Start Here Part 1 Part 2 Part 3 Grading + 17 3 + 100% AutoSave ON BE ABC svu ExcelProject 3 - Last Modified: November 5 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X Cambria v Insert v 10 . Ai = = ab General WE 48- O Ou s Paste B I U Uv A $ % ) Conditional Format Formatting as Table Ideas DX Delete v F Format Cell Styles .00 0 Sort & Filter Find & Select Sensitivity A L26 x fx B D E F G H 1 J K L M N O P R s T U V w X Y 69 Average receivablesyew 70 71 72 73 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 76 Inputs: 77 Amount borrowed $10,000,000.00 78 Stated annual rate 10.00% 79 Months per year 12 80 81 Effective annual interest rate: 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 Start Here Part 1 Part 2 Part 3 Grading + 1 point possible LLLL 17 3 + 100% AutoSave ON BE ABC svu ExcelProject 3 - Last Modified: November 5 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X Cambria Insert v 10 . = = ab General WE 48- O 5 DX Delete Paste B 1 U A $ % ) V Ideas Conditional Format Formatting as Table Cell Styles .00 0 Sort & Filter Sensitivity Format Find & Select L26 4 x fx G H H 1 K L L M N 0 P R S U V w X Y 8 points possible A B D E F 1 Part 2 2 3 Selected Items from McEwan Industries' Financial Statements (Millions of Dollars) 4 5 Annual sales $150 6 Cost of goods sold $122 7 Inventories $17 8 Accounts receivable $51 9 Accounts payable $13 10 11 INPUTS 12 Days/year 365 13 Interest rate on NOWC loans (10%) 10% 14 15 a) Using the New Target Conversion Periods, find the reduction in NOWC, increase in FCF and reduction in 16 interest expenses. Assume no change in Sales and COGS. 17 18 19 New Targets for Conversion Periods Old (Actual) New Target 20 Inventory conversion period (ICP, days) 50.9 40.0 21 Average collection period (ACP, days) 124.1 40.0 22 Payable deferral period (PDP, days) 38.9 50.0 23 Cash Conversion Cycle (CCC, days) 136.1 30.0 24 Reduction in Cash Conversion Cycle: 25 26 Impact of Reduction in CCC Old (Actual) New Target 27 Annual sales: $150.00 28 Costs of goods sold (COGS): $122.00 29 Inventory: $17.00 30 Receivables: $51.00 31 Payables: $13.00 32 Net operating working capital: 33 NOWC = Inventory + Receivables - Payables 34 Interest expense due to NOWC: interest rate on NOWC loans *(NOWC) 35 36 Improvement in Selected Results 37 Reduction in NOWC: 38 Increase in free cash flow: 39 Reduction in interest expense: Start Here Part 1 Part 2 Part 3 Grading + 17 3 a + 100% AutoSave ON BE ABC svu ExcelProject 3 - Last Modified: November 5 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X LO Cambria 10 Insert v . Ai = = ab General WE 48- O 5 Paste I U B A == $ % ) 00 .00 0 Conditional Format Formatting as Table Cell Styles DX Delete F Format Ideas Sort & Filter Find & Select Sensitivity L26 4 x fx B D E E F G H 1 K L L M N 0 P Q a R s V w X Y 41 b) McEwan Industries sells on terms of 2/10, net 25. Suppose Total sales for the year are $150 million; 40% of the 42 customers pay on the 10th day and take discounts, while the other 60% pay, on average, 83 days after their 43 purchases. Assume 365 days in year for your calculations. 8 points possible 44 45 INPUTS 46 Discount% 2.00% 47 Discount period (in days) 10 48 Amount due (in days) 25 49 Total sales $150,000,000 50 Number of days in year 365 % of customers that take discount 51 and pay on discount day 40.00% % of customers that pay after 52 discount period 60.00% Average days after purchase by 53 nondiscount customers 83 54 55 What is the days sales outstanding? 56 Days sales outstanding (DSO) 57 58 What is the average amount of receivables? 59 Average receivables 60 61 What is the percentage cost of trade credit to customers who take the discount? 62 Cost to discount customers 63 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 64 customers paying late Effective cost to nondiscount 65 customers paying late on Day 66 67 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 68 (DSO) 69 Average receivablesxew 70 71 Start Here Part 1 Part 2 Part 3 Grading + 17 3 + 100% AutoSave ON BE ABC svu ExcelProject 3 - Last Modified: November 5 Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X Cambria v Insert v 10 . Ai = = ab General WE 48- O Ou s Paste B I U Uv A $ % ) Conditional Format Formatting as Table Ideas DX Delete v F Format Cell Styles .00 0 Sort & Filter Find & Select Sensitivity A L26 x fx B D E F G H 1 J K L M N O P R s T U V w X Y 69 Average receivablesyew 70 71 72 73 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 76 Inputs: 77 Amount borrowed $10,000,000.00 78 Stated annual rate 10.00% 79 Months per year 12 80 81 Effective annual interest rate: 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 Start Here Part 1 Part 2 Part 3 Grading + 1 point possible LLLL 17 3 + 100%
