Use Excel to create all solutions and submit an excel document for final HW submission. Use the mathematical functions within excel to make all calculations. Partial credit is only possible if functions are used in all calculable cells.

You decide to form an entity to invest in Real Estate. You call the LLC Robaks Investment. You find an attractive investment in a 300,000 Sf commercial building with a purchase price of $50,000,000. In order to fund this property, you find 10 partners whos willing to contribute $2,000,000 each. You put down $1,000,000 for working capital for the property and borrow the remaining $30,000,000 at 5% amortized over 30 years and the mortgage is paid monthly. (Note: The funds raised for an investment is above the purchase price as you need working capital to operate the business). The deal closes on 12/31/2018 and your accountant attributes 30% of the investment value to land and the remaining to the building. The building depreciates over 39 years.

The tenants are spread evenly. Meaning there are 3 tenants, each occupying 100,000 Sf. Each tenant pays rent of $20 Sf/Yr (Note: The tenants were in the property before you assumed control so there were no leasing commissions or concessions). Rents are paid monthly. Costs are as follow: Property taxes are 2% of the Fair Market Value (building was assessed at $50,000,000) when you bought it. Taxes are paid twice a year at the end of March and September and it is for the following 6 months (Note: the previous landlord paid for the property taxes for Jan 2019-March 2019, and is reimbursed by the purchase price of the property, this assumption is valid for all of the costs)~(Also Note: this will generate a prepaid expense). Insurance costs are $1.00 Sf/Yr paid at the end of the year. Common area utilities are $1.50 Sf/Yr. Repairs and Maintenance is $0.75 SF/Yr. All of the costs are first paid for by the landlord but reimbursed by the tenant (assume 100% reimbursement and no bad collection). There is also a management fee equal to 2% of total revenue.

In July of 2019, there were leaks in the roof. As a result, you re-did the roof for $300,000. It was completed at the end of July, and your accounting deemed it as a capital improvement lasting for 7 years. The parking lot was getting weary so you re-pave it for $500,000 at the end of September 2019 and it was deemed as a capital improvement lasting for 7 years. Recall that the depreciation for the 1^st year on these improvements will be for a part of the year.

Make a Balance sheet for 1/1/2019. Then make an Income Statement (for period of 2019, then a Cash flow Statement (2019), and a Balance sheet (ending 2019). Taxes are 21%. (Note: These are GAAP statements not Finance modeling statements).

(It may be helpful to think of the various components of this problem and create separate worksheets for each of the major elements, such as Terms of the Deal, Rent Revenue, Costs, Amortization Schedule and Depreciation. This will streamline your calculations and make it easier to create the financial statements.)