ESBD_File_349380_Financial Worksheet v2.2.xlsx
XLSX spreadsheet 193 KB Posted
- Solicitation number
- RFQ1388-SJ
- Issued by
- Travis County, Austin City, Texas
About this file
This document is a financial worksheet related to a state and local contract opportunity from the Texas General Land Office (GLO) and Veterans' Land Board. The GLO is requesting Statements of Qualifications (SOQs) from qualified engineering service providers to assist with constructing, repairing, or rebuilding property or infrastructure in support of Community Development Block Grants for Disaster Recovery (CDBG-DR) and/or Mitigation (CDBG-MIT) programs. The contract opportunity covers multiple jurisdictions in the state of Texas, including Travis County and the City of Austin. The worksheet provides instructions for inputting selected financial statement data for three balance sheets and two income statements, which should be completed in addition to the other solicitation requirements.
The financial worksheet analyzes various liquidity, solvency, profitability, and accounts receivable ratios to assess the financial health and performance of potential vendors. It also includes an Altman Z-score analysis to evaluate the risk of financial distress. Vendors are expected to meet certain thresholds for these financial metrics in order to be considered qualified for the contract opportunity.
View the file
Other files
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
WORKSHEET
| Texas General Land Office and Veterans' Land Board |
| Financial Statement Components Worksheet |
| Instructions: Input the selected financial statement data for three Balance Sheets and two Income Statements. Input into the green cells; output is shown in blue. This worksheet should be completed in addition to the requirements listed in the Solicitation. |
| Company Name |
| Most Recent Year of Financial Statements |
| Are the most current financial statements for a partial year? If so, which quarter? |
| Industry |
Balance Sheet
| ASSETS | |||
| Cash & Equivalents | |||
| Accounts Receivable, Customers (net) | |||
| Accounts Receivable, Progress Billings | |||
| Accounts Receivable, Current Retention | |||
| Costs and Estimated Earnings in Excess of Billings | |||
| Inventory | |||
| Other Current | |||
| CURRENT ASSETS | $ - | $ - | $ - |
| Fixed Assets | |||
| Gross | |||
| Accum. Deprec. | |||
| NET | $ - | $ - | $ - |
| Joint Ventures & Investments | |||
| Intangibles (net) | |||
| Other Non-Current | |||
| NON-CURRENT ASSETS | $ - | $ - | $ - |
TOTAL ASSETS $ - $ - $ -
| LIABILITIES | |||
| Accounts Payable - Trade | |||
| Accounts Payable - Retention | |||
| Billings in Excess of Costs and Estimated Earnings | |||
| Notes Payable - Short Term | |||
| Line of Credit | |||
| Income Taxes Payable | |||
| Current Portion - Long-Term Debt | |||
| Other Current | |||
| CURRENT LIABILITIES | $ - | $ - | $ - |
| Long Term Debt | |||
| Deferred Taxes | |||
| All Other Non-Current | |||
| NON-CURRENT LIABILITIES | $ - | $ - | $ - |
TOTAL LIABILITIES $ - $ - $ -
| Equity (Stock at Par, APIC, Treasury Stock, Other) | |||
| Retained Earnings | |||
| TOTAL L & E | $ - | $ - | $ - |
Income Statements
| $ | % | $ | % | % | ||
| Sales, Net | 100.0% | 100.0% | 100.0% | |||
| Cost of Goods Sold | ||||||
| GROSS MARGIN | $ - | $ - | $ - |
| Other Revenue | |||
| Operating Expenses | |||
| Depreciation/Amortization Expense | |||
| EBIT | $ - | $ - | $ - |
| Interest Expense | |||
| EBT | $ - | $ - | $ - |
| Income Tax | |||
| NET INCOME | $ - | $ - | $ - |
Drop Downs
| Architecture/Engineering Professional Services |
| Auditing/Accounting Professional Services |
| Construction |
| Consulting |
| Information Technology |
| Legal Professional Services |
| Other |
| Retail (Any Physical Inventory Sales) |
| Q1 |
| Q2 |
| Q3 |
RRISQ
| RRISQ | |
| Review of Respondent Information for Selection and Qualification | Notes/Comments: |
| Company Name | |
| Industry | |
| Date Prepared | |
| Preparer Name | |
| Pending Score |
| Pts | Pts | Combined | Maximum | Maximum | Maximum | Maximum | Weighted | ||||||
| Liquidity Ratios | Score | Score Yr 1 | Score Yr 2 | Score | Weight | Weight | Score | Preferred | Acceptable | Unacceptable | |||
| Current ratio |
Jessica Johnston: Jessica Johnston:
| Current Assets/Current Liabilities | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 8% | 0.00 | 0.00 | 5 | 1.50 | greater than 1.5 | 3 | 1.0 - 1.5 | 1 | 1.00 | less than 1 |
| Quick ratio |
Jessica Johnston: Jessica Johnston:
| Cash+Cash Equivalents+Marketable Securities/Current Liabilities | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 8% | 0.00 | 0.00 | 5 | 1.00 | greater than 1.0 | 3 | 0.50 - 1.0 | 1 | 0.50 | less than 0.5 |
| Cash ratio |
Jessica Johnston: Jessica Johnston:
| Cash+Marketable Securities/Current Liabilities | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 8% | 0.00 | 0.00 | 5 | 0.50 | greater than 0.5 | 3 | 0.25 - 0.50 | 1 | 0.25 | less than 0.25 | |
| Total Liquidity | 0 | 0 | ERROR:#DIV/0! | |||||||||||||||||
| Solvency Ratios | ||||||||||||||||||||
| Solvency ratio |
Jessica Johnston: Jessica Johnston:
| (Net Income + Depreciation)/(ST Debt + LT Debt) | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 7% | 0.00 | 0.00 | 5 | 0.30 | greater than 0.3 | 3 | 0.20 - 0.30 | 1 | 0.20 | less than 0.2 |
| Debt to Equity ratio |
Jessica Johnston: Jessica Johnston:
| Total Debt/Shareholder Equity | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 7% | 0.00 | 0.00 | 5 | 1.00 | less than 1 | 3 | 1.0 - 3.0 | 1 | 3.00 | more than 3.0 |
| Capitalization ratio |
Jessica Johnston: Jessica Johnston:
| Total Debt/Shareholders' Equity + Total Debt | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 7% | 0.00 | 0.00 | 5 | 0.50 | 0.0 - 0.49 | 3 | 0.50 - 0.75 | 1 | 0.75 | more than 0.75; less than 0 |
| Debt ratio |
Jessica Johnston: Jessica Johnston:
| Total Debt/Total Assets | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 7% | 0.00 | 0.00 | 5 | 0.50 | 0.0 - 0.49 | 3 | 0.50 - 0.75 | 1 | 0.75 | more than 0.75; less than 0 | |
| Total Solvency | 0 | 0 | ERROR:#DIV/0! | |||||||||||||||||
| Profitability | 0.00 |
Jessica Johnston: Jessica Johnston:
Average Assets Formula in White Text
Operating Profit Margin Jessica Johnston: Jessica Johnston:
| Operating Profit/Net Sales (Revenue) | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 5% | 0.00 | 0.00 | 5 | 0.50 | greater than 0.5 | 3 | 0.0 - 0.5 | 1 | 0.00 | 0.0 or less |
| EBIT/Tangible Net Assets |
Jessica Johnston: Jessica Johnston:
| EBIT/Tangible Net Assets (Assets-Intangibles-Liabilities) | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 5% | 0.00 | 0.00 | 5 | 5.50 | 5.5 or greater | 3 | 5.5 - 2.0 | 1 | 2.00 | less than 2.0 |
| Return on Assets |
Jessica Johnston: Jessica Johnston:
| Net Income/Average Total Assets | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 5% | 0.00 | 0.00 | 5 | 1.50 | greater than 1.5 | 3 | 1.0 - 1.5 | 1 | 1.00 | less than 1 |
| Return on Equity |
Jessica Johnston: Jessica Johnston:
| Net Income/Average Shareholders' Equity | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 5% | 0.00 | 0.00 | 5 | 1.00 | greater than 1.0 | 3 | 0.0 - 1.0 | 1 | 0.00 | 0.00 or less | |
| Total Profitability | 0 | 0 | ERROR:#DIV/0! | |||||||||||||||||
| AR Analysis | 0.00 |
Jessica Johnston: Jessica Johnston:
Average Accounts Receivable formula in white text 0.00 Jessica Johnston: Jessica Johnston:
Average Accounts Receivable formula in white text Receivables Turnover Ratio Jessica Johnston: Jessica Johnston:
Net Sales/Average Trade Receivables (net) AR-progress billings and AR-current rentention are included
| NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 5% | 0.00 | 0.00 | 5 | 6.00 | 6.0 or greater | 3 | 2.0 - 6.0 | 1 | 2.00 | less than 2.0 |
| Average Collection Period |
Jessica Johnston: Jessica Johnston:
365/Receivables Turnover Ratio
Jessica Johnston: Jessica Johnston:
| Average Accounts Receivable formula in white text | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 3% | 0.00 | 0.00 | 5 | 60.00 | 60 days or less | 3 | 61 - 180 days | 1 | 180.00 | greater than 180 days | |
| Total Accounts Receivable | 0 | 0 | ERROR:#DIV/0! | |||||||||||||||||
| Inventory | 0.00 |
Jessica Johnston: Jessica Johnston:
Average Inventory in White Text 0.00 Jessica Johnston: Jessica Johnston:
Average Inventory in White Text Inventory Turnover Ratio Jessica Johnston: Jessica Johnston:
| Cost of Goods Sold/Average Inventory | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 3% | 0.00 | 0.00 | 5 | 5.00 | 5.0 or greater | 3 | 2.0 - 5.0 | 1 | 2.00 | less than 2.0 |
| Days Sales of Inventory |
Jessica Johnston: Jessica Johnston:
(1/Inventory Turnover) x. No. of Days Jessica Johnston: Jessica Johnston:
| Average Inventory in White Text | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 2% | 0.00 | 0.00 | 5 | 70.00 | 70 days or less | 3 | 71 - 180 days | 1 | 180.00 | greater than 180 days | |
| 0 | 0 | ERROR:#DIV/0! |
| Z' Score* | NA | 0 | NA | 0 | 0 | 0 | 0 | 0 | 15% | 0.00 | 0.00 | ERROR:#DIV/0! | 5 | 3.00 | 3.0 or greater | 3 | 1.8 - 3.0 | 1 | 1.80 | less than 1.8 | |
| 0 | 0 | 0 | 0 | 100% | 0.00 | 0.00 | 0.00% | ||||||||||||||
| *Altman Z'-Score | 0.00% | Updated May 14, 2019 | |||||||||||||||||||
| A=working capital/total assets | 0 | 0 | |||||||||||||||||||
| B=retained earnings/total assets | 0 | 0 | COGS = 0; Q1 |
Jessica Johnston: Jessica Johnston:
White text FALSE Jessica Johnston: Jessica Johnston:
White text C=EBIT/total assets 0 0 COGS=0; Q2 Jessica Johnston: Jessica Johnston:
White text FALSE Jessica Johnston: Jessica Johnston:
White text D=book value of equity/total liabilities 0 0 COGS=0; Q3 Jessica Johnston: Jessica Johnston:
White text FALSE Jessica Johnston: Jessica Johnston:
White text E=sales/total assets 0 0 COGS=0; full year Jessica Johnston: Jessica Johnston:
White text TRUE Jessica Johnston: Jessica Johnston:
White text
REFERENCE
| Liquidity Ratios | Current Ratio | Current assets | Current liabilities | Measures a company's ability to pay back its liabilities with its assets. Also called working capital ratio. The higher the ratio, the better; review less than 1.0. |
| Quick Ratio | (Cash + Marketable Securities + Accounts Receivable) | Current liabilities | Measures a company's ability to meet its short-term financial liabilities. Quick assets include those assets that can be quickly converted to cash at close to their book values. | |
| Cash Ratio | (Cash + Marketable Securities) | Current liabilities | Measures a company's ability to meet its short-term financial liabilities. Measures the value of the current assets that could quickly be turned into cash and what percentage of the company's current liabilities the cash could cover. | |
| Solvency Ratios | Solvency Ratio | Net Income (or After-Tax Profit) + Depreciation | ST liabilities + LT liabilities | Measures a company's ability to meet its debt and other obligations. The lower a company's solvency ratio, the greater the probability that it will default on its debt obligations. |
| Debt to Equity Ratio | Total liabilities | Shareholders' Equity | Indicates the relative proportion of shareholders' equity and debt used to finance a company's assets. A high ratio usually indicates an aggressive leveraging practice; generally associated with higher levels of risk. | |
| Capitalization Ratio | Total Debt | Shareholders' Equity + Total Debt | The extent to which a company is using debt to fund its operations. The higher the ratio, the riskier the company is because debt is considered riskier than equity. | |
| Profitability Ratios | Operating Profit Margin | Operating Profit | Net Sales (Revenue) | Measures how much profit a company makes on a dollar of sales, after paying for variable costs of production such as wages and raw materials, but before paying interest or tax. |
| Return on Assets | Net Income | Average Total Assets | Measures efficiency of the business in using its assets to generate net income. Higher values of ROA show that the business is more profitable. Uses net income after taxes. Average total assets are calculated by dividing the sum of total assets at the beginning and end of the financial year by 2. | |
| Return on Equity | Net Income | Shareholders' Equity | Measures how much a company earns within a specific period in relation to the amount that's invested in its common stock. | |
| AR Ratios | Receivables Turnover Ratio | Net Credit Sales (Assume All) | Average Accounts Receivable | Measures effectiveness in extending credit and collecting debts on that credit. |
| Average Collection Period | 365 | (Net Sales/Average AR) | Measures the approximate amount of time that it takes for a business to receive payments. |
File details come from the government source that posted it. Updated .