I NEED TO EXTRACT DATA FROM 3 DIFFERENT SHEETS FROM 150000 FILES ONTO A SINGLE NEW WORKBOOK.
FIRST SHEET IS 'INFO' I NEED 'B9' 'B11' AND 'C2'
SECOND SHEET IS 'fSC COMM' RANGE 'H9:H79'
THIRD IS 'PCF COMM' RANGE 'H15:H84'
THE MASTER WORKBOOK NEEDS TO HAVE THE FILE NAMES AS FIRST COLUMN AND EVERY ROW FROM THE THREE SHEETS (FSCCOMM,PCF COMM AND INFO) NEEDS TO BE A SEPERATE COLUMN.
<tbody>
</tbody>
FIRST SHEET IS 'INFO' I NEED 'B9' 'B11' AND 'C2'
SECOND SHEET IS 'fSC COMM' RANGE 'H9:H79'
THIRD IS 'PCF COMM' RANGE 'H15:H84'
THE MASTER WORKBOOK NEEDS TO HAVE THE FILE NAMES AS FIRST COLUMN AND EVERY ROW FROM THE THREE SHEETS (FSCCOMM,PCF COMM AND INFO) NEEDS TO BE A SEPERATE COLUMN.
Total Estimated Risk of Guarantor Support Needed on Contingent Liabilities (from column 6 of CONLB worksheet) | Cash & Equivalents | Public Equities | Domestic Stocks - unrestricted, diversified | Domestic & International Stocks - unrestricted, concentrated | Domestic Stocks - Rule 144 (liquid portion) | International Stocks, diversified | Executive Stock Options - vested | REITs | Fixed Income: | Government & Municipal Obligations | Domestic Corporate Bonds | Convertible Bonds | International Bonds/Domestic High Yield Bonds | Investment Accounts with No Detail | Other Liquidity (liquid portion of Hedge Funds/Alternative Investments) | Total Liquid Assets | Total Liquid Assets (Scorecard definition) | Encumbered Liquidity | Intermediate/Alternative Investments: | Restricted/Illiquid Public Securities | Rule 144 shares not considered liquid | REIT Operating Partnership (OP) units | Hedge Funds (illiquid portion) | Private Equity Funds | Closely Held Companies/Privately Held Sec. | Receivables: | Notes & Mortgages | Accounts Receivable | Real Estate: | Personal Residence - Primary residence | Personal Residence(s) - non-primary | Residential Lot(s) | Other 100% Owned Real Estate: | Recreational Real Estate/Land | Income Producing Properties | Equity in RE Ptnrshps and/or partially owned RE | Equity in Partnerships (Excluding Real Estate) | Cash Value of Ins. & Annuities | Retirement Plans, Deferred Comp., IRAs | Personal Property | Other Assets | Fine Art or Farm Equipment | Yachts, Aircraft | Less: Est. Taxes Payable on Unrealized Asset Appreciation | Total Assets | Total Assets (Scorecard definition) | Credit Cards | Installment Accounts | Other Accounts Payable | Notes Payable to Bank | Unsecured | Secured (Non-R/E) | Other Notes Payable | To Related Parties (Non-R/E) | Other (Non-R/E) | Real Estate Liabilities | Mortgages on Primary Residence | Mortgages on Non-primary Residence(s) | Mortgages on Residential Lot(s) | Morts./Notes secured by other 100% owned R/E | Morts. on Recreational Real Estate/Land | Income Producing Properties | Notes Due to R/E Partnership/Corps | Other Direct R/E Debt | Taxes Payable | Income Taxes | Margin Acct / Due to Brokers | Loans against Insurance / other Policies | Other Direct Liabilities | Total Liabilities | Total Liabilities (Scorecard definition) | STATED NET WORTH: | Contingent Liabilities (not to be used, use RAV version) | Name of Borrower: | Preparation Date (for the entire spread workbook) | Financial Statement Date (corresponding date for each year of financial data) | Actual Year | Salaries | Bonuses | Dividends | Interest | Recurring Real Estate Inflows: | Net Rents-NOI | R/E Partnership Distributions | Other Recurring - NOI | Other Partnership Distributions | Other (incl. Bus. Income, Trust Income) | Other (incl. Retirement income/annuities) | Other (verifiable income to be generated from asset purchased w/loan proceeds) | Recurring Cash Sources SUBTOTAL | Recurring Cash Sources (Scorecard definition) | R/E Taxes for non-income prop. | Planned Investments, Partnership | Contributions & Capital Calls | Estimated Living Expenses | Federal Income Taxes | State Income Taxes | Other | Recurring Cash Uses SUBTOTAL | Recurring Cash Uses (Scorecard definition) | CASH FL. AVAIL. FOR DEBT SERV. | Subject Request debt (based on actual maturity) | Subject Request debt | Bank of America- revolving debt | Bank of America- term debt | Residential Mortgage debt | Non-BoA debt | Other CBI debt | Sched. Cont. Liab. Pymts. | DEBT SERVICE SUBTOTAL | Debt Service (Scorecard definition) | SUBTOTAL - RECURRING USES + DEBT SERVICE | NET RECURRING CASH FLOW | DEBT SERVICE COVERAGE | Real Estate Sales | New Real Estate Investment | Sale of Mkt. Sec. | Purchase of Mkt. Sec. | Other purch. & capital outlays | Other | NON-RECURRING SOURCES (USES) SUBTOTAL | NET CASH FLOW BEFORE FINANCING ACTIVITIES | Real Estate Financing | Margin Financing | Homestead Financing | Discretionary Debt Payments | Contingent Liability Payments | Other | FINANCING SOURCES (USES) SUBTOTAL | NET CASH FLOW (DEFICIT) | Scorecard Federal Taxes | Scorecard State Taxes | ||
PCF F79 FSC H80 M SU | $1,207.98 | 583.65 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1996.177 | 0 | 2579.827 | 2579.827 | 0 | 0 | 0 | 0 | 0 | 0 | 563 | 4140 | 0 | 0 | 0 | 0 | 5250 | 2500 | 0 | 0 | 0 | 0 | 0 | 41253 | 200 | 624 | 15100 | 17700 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5204.342 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5204.342 | 5204.342 | 84705.49 | 9081.883 | Mark E Salzberg | 5/7/2014 | 2/25/2014 | Various | 1254.238 | 0 | 2.442 | 52.004 | 0 | 0 | 0 | 0 | 6236.688 | 13.779 | 0 | 0 | 7559.151 | 7559.151 | 64.764 | 0 | 0 | 801.485 | 2267.745 | 5.2 | 0 | 3139.194 | 3139.194 | 4419.957 | 0 | 0 | 209 | 0 | 0 | 0 | 32.412 | 168.304 | 409.716 | 409.716 | 3548.91 | 4010.241 | 10.78786 | 272 | 0 | 0 | 0 | 2170.379 | -260.838 | 2181.541 | 6191.782 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6191.782 | 392.6052 | 5.2 | |
PCF F79 FSC H80 M SU | $0.00 | 750 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 0 | 1550 | 1550 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 25000 | 0 | 0 | 0 | 0 | 4000 | 0 | 0 | 0 | 150 | 750 | 500 | 0 | 0 | 0 | 0 | 0 | 100 | 0 | 0 | 0 | 0 | 300 | 0 | 0 | 0 | 0 | 4000 | 0 | 0 | 0 | 0 | 2500 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6900 | 6900 | 25050 | 0 | Klentner Family Trust | 7/24/2012 | 8/12/2012 | Various | 99.5 | 0 | 3 | 1.881 | 0 | 66.379 | 0 | 0 | 0 | 13.7 | 0 | 0 | 184.46 | 184.46 | 0 | 0 | 0 | 177.3143 | 1.941 | 6.174 | 0 | 185.4293 | 185.4293 | -0.96925 | 0 | 0 | 64.92 | 0 | 364.9 | 0 | 17.76 | 0 | 447.58 | 447.58 | 633.0093 | -448.549 | -0.00217 | 0 | 0 | 180.288 | -152.277 | 0 | 0 | 28.011 | -420.538 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -420.538 | 1.941 | 6.174 | |
PCF F79 FSC H80 M SU | $0.00 | 6303.135 | 0 | 2413.251 | 0 | 118479.6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 127196 | 127196 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 40000 | 0 | 0 | 34000 | 0 | 0 | 0 | 0 | 0 | 0 | 3500 | 8000 | 161305 | 0 | -5000 | -5000 | -5000 | 0 | 0 | 0 | 0 | 0 | 58000 | 0 | 0 | 0 | 0 | 14000 | 0 | 0 | 11583.2 | 0 | 0 | 0 | 0 | 0 | 200 | 43000 | 0 | 0 | 126783.2 | 126783.2 | 242217.8 | 1788.8 | Adam Lindemann | 1/31/2014 | 9/30/2013 | Various | 0 | 0 | 1496.179 | 152.338 | 0 | 0 | 0 | 0 | 0 | 402.082 | 0 | 0 | 2050.599 | 2050.599 | 0 | 0 | 0 | 1027.19 | 80.991 | 96.447 | 0 | 1204.628 | 1204.628 | 845.9712 | 0 | 2600 | 2800 | 0 | 907.572 | 0 | 0 | 117.5853 | 6425.157 | 6425.157 | 7629.785 | -5579.19 | 0.131665 | 0 | 0 | 15905.85 | -4997.33 | 0 | 3612.91 | 14521.42 | 8942.238 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8942.238 | 80.991 | 96.447 | |
PCF F79 FSC H80 M | $0.00 | 3187.628 | 0 | 2675.907 | 0 | 201713.3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5.75 | 0 | 207582.6 | 207582.6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 45000 | 0 | 0 | 35500 | 0 | 0 | 0 | 0 | 0 | 0 | 17000 | 10000 | 175000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 127966 | 0 | 0 | 0 | 0 | 14000 | 0 | 0 | 11383.16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 153349.2 | 153349.2 | 336733.4 | 1684.8 | Adam Lindemann | 9/23/2014 | 6/30/2014 | Various | 0 | 0 | 1496.179 | 152.338 | 0 | 0 | 0 | 0 | 0 | 402.082 | 0 | 0 | 2050.599 | 2050.599 | 0 | 0 | 0 | 1021.261 | 80.991 | 96.447 | 0 | 1198.699 | 1198.699 | 851.9 | 0 | 0 | 2800 | 2600 | 904.836 | 0 | 0 | 116.6147 | 6421.451 | 6421.451 | 7620.15 | -5569.55 | 0.132665 | 0 | 0 | 15905.85 | -4997.33 | 0 | 3612.91 | 14521.42 | 8951.873 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8951.873 | 80.991 | 96.447 |
<tbody>
</tbody>
Last edited: