AutoSave oft H D MLR analytical exercises 20200228(1) (5) - Excel Search Sayma Kaiser SK - 0 Share 0 x Comments File Home Insert Draw P age Layout Formulas Data Review View Help 027 : * fic F G H I J K L M N | O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH ALA Model ---> ADA S=bREV The 12/31/2016 MLR balance sheet reported the allowance for doubtful accounts, after 2016 adjustments, to be $1,004 (thousands). A regresson model of the estimated required reserve at that balance sheet date produced the following 50% 2-sided confidence interval: LOWER LIMIT $1,296 ESTIMATED ALLOWANCE UPPER LIMIT $1,520 $1,744 Receivables eins. ursing. Pemestial dirritatis This result suggests that MLR's 12/31/2016 allowance is too low. The question is: Was it too low? Hontirralar in AIR ] ---- REQUIRED: Calculate MLR's 2016 end-of-year uncollectible A/R by calculating the required reserve using 2017 financial data. 2.609073957 Properties of APR TIL Obtain information from the 12/31/2017 MLR annual report on Form 10-K from www.sec.gov. See "Accessing the SEC database" in the Analytical Procedures folder Scroll down to the bottom entryl. 0.318376449 0.535389335 Curr 0.683310429 0.78413693 0.852862648 0.899707715 0.931638417 Overdy. 0.953403135 0.968238479 0.97835060 0.985243259 The formula on ROW 66 in Ratios, mir discussion case reserve ratio workbook, gives the actual outcome for MLR's estimate of uncollectivle receivables. Using that formula on the 2017 data AND the receivable aging proportions in columns k-o of this worksheet, determine the required reserve for 12/31/2017. Success in this exercise requires you to access the data, and to interpret properly the Row 66 formula and the data that apply to it. AR 12/31/2017 AR>1427122017 VO217 Convert the aging properties 0.993143161 Very overdue crubject taurito 0.995326694 0.996814565 0.997828732 0.998520013 0.998991206 0.949212382 0.999531303 0.999680525 0.999782239 0.999851569 0.999848826 Required Reserve 12/31/2017 NNN What is your conclusion about the adequacy of MLR's 12/31/2017 allowance? 0.9999310 Validate the 2016 allowance Warranty expenses + Ready 9 - H E O Type here to search Re x + 50% 8:54 PM 3/8/2020 s Validate the 2016 allowance Warranty expenses MILLER HEQUENT mums FELFED e Portomannsregion tough then with th An d for one region of your choosing De acordo garding the beness of MUR 2016 Your work should become and in good pr o m The RTON All work should be presedin his spreadsheet OU DOWN BE ETTER A RRANTY EXPENSE AutoSave oft H D MLR analytical exercises 20200228(1) (5) - Excel Search Sayma Kaiser SK - 0 Share 0 x Comments File Home Insert Draw P age Layout Formulas Data Review View Help 027 : * fic F G H I J K L M N | O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH ALA Model ---> ADA S=bREV The 12/31/2016 MLR balance sheet reported the allowance for doubtful accounts, after 2016 adjustments, to be $1,004 (thousands). A regresson model of the estimated required reserve at that balance sheet date produced the following 50% 2-sided confidence interval: LOWER LIMIT $1,296 ESTIMATED ALLOWANCE UPPER LIMIT $1,520 $1,744 Receivables eins. ursing. Pemestial dirritatis This result suggests that MLR's 12/31/2016 allowance is too low. The question is: Was it too low? Hontirralar in AIR ] ---- REQUIRED: Calculate MLR's 2016 end-of-year uncollectible A/R by calculating the required reserve using 2017 financial data. 2.609073957 Properties of APR TIL Obtain information from the 12/31/2017 MLR annual report on Form 10-K from www.sec.gov. See "Accessing the SEC database" in the Analytical Procedures folder Scroll down to the bottom entryl. 0.318376449 0.535389335 Curr 0.683310429 0.78413693 0.852862648 0.899707715 0.931638417 Overdy. 0.953403135 0.968238479 0.97835060 0.985243259 The formula on ROW 66 in Ratios, mir discussion case reserve ratio workbook, gives the actual outcome for MLR's estimate of uncollectivle receivables. Using that formula on the 2017 data AND the receivable aging proportions in columns k-o of this worksheet, determine the required reserve for 12/31/2017. Success in this exercise requires you to access the data, and to interpret properly the Row 66 formula and the data that apply to it. AR 12/31/2017 AR>1427122017 VO217 Convert the aging properties 0.993143161 Very overdue crubject taurito 0.995326694 0.996814565 0.997828732 0.998520013 0.998991206 0.949212382 0.999531303 0.999680525 0.999782239 0.999851569 0.999848826 Required Reserve 12/31/2017 NNN What is your conclusion about the adequacy of MLR's 12/31/2017 allowance? 0.9999310 Validate the 2016 allowance Warranty expenses + Ready 9 - H E O Type here to search Re x + 50% 8:54 PM 3/8/2020 s Validate the 2016 allowance Warranty expenses MILLER HEQUENT mums FELFED e Portomannsregion tough then with th An d for one region of your choosing De acordo garding the beness of MUR 2016 Your work should become and in good pr o m The RTON All work should be presedin his spreadsheet OU DOWN BE ETTER A RRANTY EXPENSE