Edit errors in spreadsheet from previous homework company.
Lueder’s Medical Clinic 2017–2018 Financial Statements Lueder’s Medical Clinic 2017–2018 Financial Statements Lueder’s Medical Clinic – Income Statement Year Ended December 31 Operating Revenue 2017 2018 Gross patient service revenue $775,000.00 $844,000.00 Less: Contractual allowances 465,000.00 505,200.00 Net patient service revenue 310,000.00 338,800.00 Other operating revenue 18,000.00 28,000.00 Total operating revenue 328,000.00 366,800.00 Operating Expenses 2017 2018 Salaries and wages 139,279.00 165,320.00 Employee benefits 13,041.00 14,062.00 Supplies expense 17,376.00 17,058.00 Purchased services 29,200.00 26,756.00 Other operating expenses 105,076.00 117,806.00 Provision for bad debts 18,440.00 11,500.00 Depreciation and amortization 17,650.00 12,500.00 Interest expense 2,750.00 1,819.00 Total Expenses $342,812.00 $366,821.00 Revenue over expenses from operations (14,812.00) (21.00) Nonoperating gains: Investment income 15,325.00 1,445.00 Net income (loss) $513.00 $1,424.00 Lueders Medical Clinic – Balance Sheet Year Ended December 31 Assets Current Assets 2017 2018 Cash and investments $12,000.00 $13,650.00 Patient accounts receivable, net allowance 75,675.00 66,135.00 Supplies inventory 30,100.00 19,340.00 Total current assets 117,775.00 99,125.00 Property and equip, net of accumulated depreciation 37,672.00 60,321.00 Total assets $155,447.00 $159,446.00 Liability and Equity Current Liabilities 2017 2018 Accounts payable $41,254.00 $65,450.00 Accrued salaries and expenses 15,200.00 7,600.00 Due to third-party payors 21,345.00 15,350.00 Total current liabilities 77,799.00 88,400.00 Long-term liabilities 12,355.00 15,000.00 Pension liabilities 11,124.00 5,500.00 Total Liabilities 101,278.00 108,900.00 Equity 54,169.00 50,546.00 Total liabilities and equity $155,447.00 $159,446.00 Lueder’s Medical Clinic 2017–2018 Financial Statements Lueder’s Medical Clinic – Income Statement Year Ended December 31 Lueders Medical Clinic – Balance Sheet Year Ended December 31 Assets Liability and Equity Horizontal & Vertical Analyses Lueders Medical Clinic Horizontal Analysis of Selected Accounts Accounts 2018 $2017 $ChangePercentNotes Accounts receivable 66,135.0075,675.00(9,540.00)-0.1261decline in debtors shows an aggressive collection strategy. Its encouraged Supplies inventory19,340.0030,100.00(10,760.00)-0.3575decline in inventory is unhelathy, may lead to halting operatins Property and equipment60,321.0037,672.0022,649.000.6012increase in assets, positive Bad debts11,500.0018,440.00(6,940.00)-0.3764decline in bad debts, shows an aggressive credit collection strategy Total contractual allowances505,200.00465,000.0040,200.000.0865increase in allowance is comnsurate with sales Net patient revenue338,800.00310,000.0028,800.000.0929increased revenue collection, positive Salaries and wages165,320.00139,279.0026,041.000.1870with increase in revenue, its positve Long-term liabilities15,000.0012,355.002,645.000.2141assets grew so is the expectation of assets Vertical Analysis: Fill in the correct information for each of the base amounts below. Lueders Medical Clinic Vertical Analysis of Selected Accounts Accounts Year 2018Year 2017IndustryNotes Amount $PercentAmount $Percent Accounts receivable66,135.000.41575,675.000.48745%well below industry averrage, commendable Supplies inventory19,340.000.12130,100.000.19410%well below industry average, not encouraged Net patient revenue338,800.001.000310,000.001.000100%within industry avergaes, ecouraged Accounts payable65,450.000.41041,254.000.26526%below industry average, ecouraged Salaries and wages165,320.000.488139,279.000.44911%below industry average, ecouraged Depreciation and amortization12,500.000.03717,650.000.05780%below industry average, ecouraged Long-term liabilities15,000.000.09412,355.000.0796%below industry average, ecouraged Net income1,424.000.004513.000.00210%below industry average, poor Base Amount for Balance Sheet:2018159,4462017155,447.00 Base Amount for Income Statement:2018338,800.002017310,000.00 Ratio Analysis Ratio Analysis Please show your work in the "Calculation" cells. Place your final ratios, properly expressed with units, in the bold outlined cells. RatiosCalculation2017Calculation2018Industry AverageNotes Liquidity RatiosCurrent Ratio11775/ 777991.5199,125.00/884001.121.29 in the case of this buisness the current ratio is 1.12 in 2017 and 1.51 in the 2018. basically a ratio above 1 s consisiderd good though its below industry verage Current Assets / Current Liabilities Quick Ratio (Acid Test)0.230.900.68 the company has a quick ratio of 0.9 in 2017 and 0.23 in 2018. Companies with an acid-test ratio of less than 1 do not have enough liquid assets to pay their current liabilities and should be treated with caution. If the acid-test ratio is much lower than the current ratio, it means that a company's current assets are highly dependent on inventory. (Current Assets - Supplies Inventory) / Current Liabilities(11775-30100)77799(99125-19340)/88400 Debt RatiosDebt to Total Assets59.00%45.30%0.4 the company has debt to assets ratio of 0.4% in 2018 and 0.59% in 2018. A higher debt ratio (0.6 or higher) makes it more difficult to borrow money. Lenders often have debt ratio limits and do not extend further credit to firms that are overleveraged. Total Debt / Total Assets75675/14344766,135.00/145796 Times Interest Earned0.00%0.00%2.5 (Net Income + Interest Charges) / Interest Charges(513+0)/0(1424+0)/0 in both financsial years 2017 and 2018, the company had a times earned intrest ratio of zero. the ratio is poor meaning the company has no money to pay its shareholders a dividend Asset Management RatiosInventory Turnover10.29 times17.51 times25 in 2017 the company has an inventory turnover of 17 times and 10 times in 2018. A high ratio implies either strong sales or insufficient inventory. The former is desirable while the latter could lead to lost business. Sometimes a low inventory turnover rate is a good thing, such as when prices are expected to rise (inventory pre-positioned to meet fast-rising demand) or when shortages are anticipated Net Patient Service Revenue / Supplies Inventory310000/30100338800/19340 Average Collection Period32.72 days31.14 days55.4 in 2017 the average collection period was 31 days while in 2018 the average collection period was 32 days. A lower average collection period is generally more favorable than a higher average collection period. A low average collection period indicates the organization collects payments faster. Accounts Receivable / Net Patient Service Revenues Per Day75675/($844,000/365)66135/($775,000.00/365) Fixed Assets Turnover8.22 times5.61 times5.2 in 2017 the company had an fixed assets turn over of 5.6 while in 2018 the turnover ratio was 8. .A higher turnover ratio is indicative of greater efficiency in managing fixed-asset investments, but there is not an exact number or range that dictates whether a company has been efficient at generating revenue from such investments. Net Patient Service Revenue / Fixed Assets 310000/37,672.00338800/60,321.00 Total Assets Turnover1.99 times2.12 times3 Net Patient Service Revenue / Total Assets310000/155,447.00 338800/159,446.00 in 2017, the company has totalturn over raio of 2.12 times while in 2018 it was 1.99 times. The higher the asset turnover ratio, the better the company is performing, since higher ratios imply that the company is generating more revenue per dollar of assets. Profitability RatiosProfit Margin on Revenue0.16%0.00%0.59 Net Income / Net Patient Service Revenue513/3100001424/338800 in 2017 and 2018 net profit over revenue ratio was 0. the the ratio is poor meaning the company is making less profit in comparison to its sales Return on Total Assets0.00%1.00%4.8 in 2017 the net income over assets ratio was 0.1% and in 2018 the ratio was 0%.The greater a company's earnings in proportion to its assets (and the greater the coefficient from this calculation), the more effectively that company is said to be using its assets. Net income / Total assets513/1554471424/159446 Return on Equity1.00%2.80%0.0975 Net income / Equity 513/54,169.001424/50,546.00 in 2017 the reyurn over equit ratio was 0.03 while in 2018 it was 0.01.A good rule of thumb is to target an ROE that is equal to or just above the average for the peer group. 1.50 Sheet1 2312.32 2123.28 66135 19340 ERROR:#NAME?