Please fill the EXCEL WORKSHEET below using the balance sheet(TABLE 1) , income statements (TABLE 2), and the production data (TABLE 3).

EXCEL SHEET

(1)Be careful about the negative sign since in accounting +/- signs are used for showing whether the money is coming in or going out. In formulas, that ask you to subtract something-- you'll get a wrong answer if you end up using two negative signs. For example, money in uncollectable accounts is expressed in negative, that does not mean you will subtract it again when calculating average collection period. Two negative signs will make it positive. Thus, you'll end up adding uncollectibles to receivables. A similar negative sign problem you might see in Time Interest Earned Ratio, which should be a positive number.

(2)In average collection period in days (water and valor) -multiply 365 with the entire ratio, i.e., keep it in the numerator. See this link to understand it clearly https://www.investopedia.com/terms/a/average_collection_period.asp. If you do it right, for 1974,you should get average collection period of around 86 days for water and 398 for valor. Remember, uncollectable accounts are already expressed with a negative sign.

(3) Total Asset Turnover is listed as a liquidity ratio in the table, but it should be under efficiency ratios

MEASURES AND RATIOS I. LIQUIDITY a. Current Ratio Current Assets / Current Liabilities b. Quick Ratio Current Assets - Inventories / Current Liabilities c. Average Collection Period in Days (Water) [Net Water Receivables (receivables-uncollectable accounts)]/ Net Water Sales] X 365 Days d. Average Collection Period in Days (Valor.) [Net Valor. Receivables (receivables-uncollectable accounts) / Valorization Income from Table 2] 365 Days e. Total Assets Turnover Gross Operating Revenues / Total Assets (Total Assets is sum of total fixed assets and total current assets) II. SOLVENCY a. Long Term Debt to Capitalization (Percentage) Long Term Debt / (Long Term Debt + Patrimony) (Percentage) b. Total Debt (Long Term Debt+Total Current and Accrued Liabilities) (Percentage) / Total Liabilities abd Other Credits (Percentage) c. Times Interest Earned Ratio Net Operating Revenues before Interest / Interest Charges and Commissions III. PROFITABILITY a. Net Profit Percentage (net income - adjustments from Table 2) / Total (Gross) Revenues (Percentage) b. Net Profit / Patrimony (Percentage) c. Net Profit / Total Assets (Percentage) IV. EFFICIENCY a. Volume of Water Produced / Total Assets (Percentage) b. Volume of Water Sold / Total Assets (Percentage) IV. EFFICIENCY a. Volume of Water Produced / Total Assets (Percentage) b. Volume of Water Sold / Total Assets (Percentage) c. Operating Ratio Total Operating Expenses / Gross Operating Revenues (Percentage) d. Operation and Maintenance Expenses as a percent of operating expenses (Percentage) e. Administration Expenses as a percent of operating expenses (Percentage) f. Volume of Water Sold / Water Produced (Percentage) The Panama Water Authority, December 31, 1974 General Balance Sheet (thousands of Balboas) 1 1 One Baiboa = One US Dollar 2 Each year a reserve is kept for uncollectable accounts. These reserves are negative numbers. To compute a net receivable figure suburact the reserves from the accounts receivable. 3 The water authority collects and keeps a portion of the property tax to subsidize water prices. 4 What the water authority called Patrimony is normally called Contributions in a publicly owned utility. This is equivalent to equity or proprietary capital in an investor owned utility. Table 1 (continued) Table 2 The Panamanian Water Authority CONSOLIDATED INCOME STATEMENT (Thousands of Balboas) Table 3 The Panamanian Water Authority WATER PRODUCTION DATA MEASURES AND RATIOS I. LIQUIDITY a. Current Ratio Current Assets / Current Liabilities b. Quick Ratio Current Assets - Inventories / Current Liabilities c. Average Collection Period in Days (Water) [Net Water Receivables (receivables-uncollectable accounts)]/ Net Water Sales] X 365 Days d. Average Collection Period in Days (Valor.) [Net Valor. Receivables (receivables-uncollectable accounts) / Valorization Income from Table 2] 365 Days e. Total Assets Turnover Gross Operating Revenues / Total Assets (Total Assets is sum of total fixed assets and total current assets) II. SOLVENCY a. Long Term Debt to Capitalization (Percentage) Long Term Debt / (Long Term Debt + Patrimony) (Percentage) b. Total Debt (Long Term Debt+Total Current and Accrued Liabilities) (Percentage) / Total Liabilities abd Other Credits (Percentage) c. Times Interest Earned Ratio Net Operating Revenues before Interest / Interest Charges and Commissions III. PROFITABILITY a. Net Profit Percentage (net income - adjustments from Table 2) / Total (Gross) Revenues (Percentage) b. Net Profit / Patrimony (Percentage) c. Net Profit / Total Assets (Percentage) IV. EFFICIENCY a. Volume of Water Produced / Total Assets (Percentage) b. Volume of Water Sold / Total Assets (Percentage) IV. EFFICIENCY a. Volume of Water Produced / Total Assets (Percentage) b. Volume of Water Sold / Total Assets (Percentage) c. Operating Ratio Total Operating Expenses / Gross Operating Revenues (Percentage) d. Operation and Maintenance Expenses as a percent of operating expenses (Percentage) e. Administration Expenses as a percent of operating expenses (Percentage) f. Volume of Water Sold / Water Produced (Percentage) The Panama Water Authority, December 31, 1974 General Balance Sheet (thousands of Balboas) 1 1 One Baiboa = One US Dollar 2 Each year a reserve is kept for uncollectable accounts. These reserves are negative numbers. To compute a net receivable figure suburact the reserves from the accounts receivable. 3 The water authority collects and keeps a portion of the property tax to subsidize water prices. 4 What the water authority called Patrimony is normally called Contributions in a publicly owned utility. This is equivalent to equity or proprietary capital in an investor owned utility. Table 1 (continued) Table 2 The Panamanian Water Authority CONSOLIDATED INCOME STATEMENT (Thousands of Balboas) Table 3 The Panamanian Water Authority WATER PRODUCTION DATA