1)Show how to solved it on excel what function is used. 2)Show how to solved it
ID: 1174899 • Letter: 1
Question
1)Show how to solved it on excel what function is used.
2)Show how to solved it on excel what function is used.
2 CASH CONVERSION CYCLE Sims Electric Corp. has beem striving for the last 5 years to improve its management of working capital. Historical data for the firm's sales, accounts receivable, inventories, and accounts payable are shown below a. Calculate Sims' days of sales outstanding and days of sales in inventory for each of the 5 years. Use a 365 day year b. Calculate Sims cash conversion cycle for each of the 5 years. Use a 365 day year. Evaluate Sims'overall management of its working 4 Note: Assume cost of goods sold is 70% of sales. 6 DATA Jan-06 Jan-08 Jan-10 8 Sales-Ne 9 Receivables 10 Accounts payable 11 Inventories 12 Cost of goods sold Sales 13 14 SOLUTION 3,475 538 447 293 7,759 $12,327 411 466 220 251 233 Eays of sales outstanding and days of sales in in for each of the 5 years. Use a 365 day year What has Sims 15 accomplisbed i its attermpts to better manage its investments in accournts receivable and in 16 17 Cost of goods sold 18 Days of sales outstanding 19Days of sales in inventory 20 Change in days of sales a 21 Change i days of sales in inventory w.r.t.year 1 utstanding w.r.t. year 23 b. Caleulate Sims'cash conversion cyele for esch of the 5 years. Use a 365 day year. Evahuate Sims' averall managememt of its working capital. 25| Days of payables 26 Cash conversion cycle 27 Change in cash conversion cycle w.rt.year 28 29Explanation / Answer
Jan'06 Jan'07 Jan'08 Jan'09 Jan'10 1) Sales-Net $2,873 $3,475 $5,296 $7,759 $12,327 Receivables 411 538 726 903 1486 Accounts Payable 283 447 466 1040 1643 Inventories 220 293 429 251 233 Cost of Goods/Sold 70% Days of Sales outstanding (Accounts receivable/Total sales)*Number of days days of sales in inventory Number of days/(Cost of goods sold/closing inventory) Days payable outstanding Accounts payable/(Cost of goods sold/number of days) Cash conversion cycle days inventory outstanding + days sales outstanding + days payable outstanding a) Calculate Sims' days of Sales outstanding and days of sales in inventory for each of the 5 years. Use a 365-day year.What has sims accomplished in its attempts to better manage its investments in accounts receivable and inventory? Cost of goods sold 2873*70% 3475*70% 5296*70% 7759*70% 12327*70% 2,011.10 2,432.50 3,707.20 5,431.30 8,628.90 Days of Sales outstanding (411/2873)*365 (538/3475)*365 (726/5296)*365 (903/7759)*365 (1486/12327)*365 52.22 56.51 50.04 42.48 44.00 52.22 days 56.51 days 50.04 days 42.48 days 44 days days of sales in inventory 365/(2011.1/220) 365/(2432.50/293) 365/(3707.20/429) 365/(5431.30/251) 365/(8628.90/233) 39.93 43.97 42.24 16.87 9.86 39.93 days 43.97 days 42.24 days 16.87 days 9.86 days Change in days of sales outstanding w.r.t year 1 NA (56.51-52.22) = 4.29 days (50.04-56.51) = -6.47 days (42.48-50.04) = -7.56 days (44-42.48) = 1.52 days Change in days of sales in inventory w.r.t year 1 NA (43.97-39.93) = 4.04 days (42.24-43.97) = -1.73 days (16.87-42.24) = - 25.37 days (9.86-16.87) = - 7.01 days b) Calculate Sims' cash conversion cycle for each of the 5 years. Use a 365-day year. Evaluate Sims' overall management of its working capital. Days of payables outstanding 283/(2011.10/365) 447/(2432.50/365) 466/(3707.20/365) 1040/(5431.30/365) 1643/(8628.90/365) 51.36 67.07 45.88 69.89 69.50 51.36 days 67.07 days 45.88 days 69.89 days 69.50 days Cash conversion cycle 52.22+39.93+51.36 56.51+43.97+67.07 50.04+42.24+45.88 42.48+16.87+69.89 44+9.86+69.50 143.51 167.55 138.16 129.24 123.36 Change in cash conversion cycle w.r.t year 1 NA (167.55-143.51) = 24.04 days (138.16-167.55) = -29.39 days (129.24-138.16) = -8.92 days (123.36-129.24) = -5.88 days Sims electric corp was subsequently able to reduce the cash conversion cycle by 5.88 days 2) Annual Interest rate 12% Minimum demand deposit 10% Loan $100,000 Months 3 Monthly Interest rate 1% 12%/12 Number of 3-month periods in a year 0.25 3/'12 Interest 3000 100000*1%*3 Demand deposit 10000 10%*100000 Amount available from the loan 97000 (100000-3000) APR 3.45% (3000/97000-10000)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.