Please help me with this question. First part is the problem and second part, the screenshot part is the requirement or working area.
| Formula: Divide, Subtract; Cell Referencing | ||||
| Some cells are merged. | ||||
| Case 4.3. Using Excel to Assess the Debt-to Net Assets Ratio | ||||
| PROBLEM | ||||
| Goodfellow & Perkins LLP is a successful mid-tier accounting firm with a large range of clients across Texas. During 2025, Goodfellow & Perkins gained a new client, Brookwood Pines Hospital (BPH), a private, not-for-profit hospital. The fiscal year-end for BPH is June 30. Goodfellow & Perkins is performing the audit for the fiscal year-end June 30, 2026. The balance sheets as of June 30, 2026 and 2025 are presented here. | ||||
| Brookwood Pines Hospital | ||||
| Balance Sheet (in thousands) | ||||
| June 30 | ||||
| Assets | 2026 | 2025 | ||
| Cash and cash equivalents | $ 43,077 | $ 36,361 | ||
| Short term investments | 22,725 | 49,338 | ||
| Patient accounts receivable, net | 119,380 | 99,962 | ||
| Current portion of pledges and grants | 9,208 | 5,099 | ||
| receivable, net | ||||
| Current portion of insurance recoverable | 2,364 | 1,953 | ||
| Inventory | 10,740 | 10,056 | ||
| Other current assets | 25,792 | 23,193 | ||
| Total current assets | 233,286 | 225,962 | ||
| Long-term investments | 915,088 | 807,321 | ||
| Property and equipment, net | 576,432 | 538,981 | ||
| Prepaid pension cost | 19,760 | 7,248 | ||
| Insurance recoverable, less current portion | 11,619 | 10,723 | ||
| Other assets, net | 31,535 | 28,463 | ||
| Total assets | $ 1,787,720 | $ 1,618,698 | ||
| Liabilities and net assets | ||||
| Accounts payable | $ 38,431 | $ 39,547 | ||
| Accrued salaries and benefits | 52,361 | 50,754 | ||
| Grants payable, current portion | 6,459 | 8,459 | ||
| Accrued expenses and other current liabilities | 19,209 | 27,380 | ||
| Due to third-party payors | 72,494 | 67,687 | ||
| Current accrued liabilities under self- | 15,709 | 14,965 | ||
| insurance programs | ||||
| Current maturities of long-term debt | 5,040 | 4,928 | ||
| Short-term debt | 14,550 | – | ||
| Long-term debt subject to short-term | – | 53,132 | ||
| refinancing agreements | ||||
| Total current liabilities | 224,253 | 266,852 | ||
| Long-term debt, net, less current liabilities | 220,796 | 179,530 | ||
| Accrued liabilities under self-insurance | 82,618 | 82,559 | ||
| programs, less current portion | ||||
| Grants payable, less current portion | 13,245 | 16,489 | ||
| Other liabilities | 42,669 | 48,336 | ||
| Total liabilities | 583,581 | 593,766 | ||
| Net assets | ||||
| Without donor restrictions | 1,138,140 | 962,652 | ||
| With donor restrictions | 65,999 | 62,280 | ||
| Total net assets | 1,204,139 | 1,024,932 | ||
| Total liabilities and net assets | $ 1,787,720 | $ 1,618,698 | ||
| BPH provides medically necessary care to patients, regardless of their ability to pay. Both uninsured and underinsured patients are offered discounts of up to 100% of charges based on their income as a percentage of the federal poverty-level guidelines. BPH does not pursue collection of these accounts; therefore, they are not reported in patient service revenue and accounts receivable. The cost of providing the charity care is included in operating expenses. | ||||
| BPH’s investments consist of mutual funds, common equities, corporate and U.S. government debt issues, state and municipal government debt issues, and trusts. A majority of the investments are the result of charitable contributions to the hospital by generous donors. Earnings from the investments are used to cover the costs of the charity care. BPH is also eligible for certain government grants to help cover the costs of the charity care. Selected financial statements and other financial information are provided below. Since BPH operates as a non-for-profit, it reports assets, liabilities, and net assets. (Note: Net assets takes the place of equity since there are no owners.) | ||||