80.2K

Verified Solution

Link Copied!

This semester we will be using a bar, The Illini Tap, as our working example for many of the homework assignments. We will construct financial statements for the business and analyze its financial performance. Later in the semester we will introduce some potential investment ventures for the bar and analyze them using the tools we will cover in class.

You can think of yourself as the owner of this small business, which we will assume has been operating for the past 8 years. You originally opened the bar using some money you had saved up (the initial capital contribution) as well as some longer-term financing you were able to secure from a local bank. The bar is located in the Champaign-Urbana area in a building you are leasing, and up until now you have only served alcohol and only accept cash. Over time you have made some financial investments with available cash (i.e. mutual funds). Youve also invested some of the business money in a local microbrewery that is operated by one of your friends.

Since opening the bar you have kept decent records, but never actually constructed your financial statements in a formal fashion. Business has been good and you would like to start considering some investment opportunities to update and expand operations. To do this you will most likely need to secure some additional debt financing from a lender, so you realize the importance of putting together an accurate system of financial statements for your business for the 2022 calendar year.

Using the information given to you on the next (back) page, start this process by putting together the 12/31/21 Balance Sheet for the bar. You will need to enter individual line items and complete the Deferred Tax and Valuation Equity worksheets to do this. Some items are already entered, others do not belong on the Balance Sheet. There are also a few short answer questions for you to answer about balance sheet items in general.

This will serve as the beginning balance sheet for 2022 and in the next few assignments we will also put together the other 3 financial statements (and the ending balance sheet) for 2022. Make sure you save a copy of the Excel file you create to complete this assignment so that you can reference it in the future.

Use the information below to construct the Dec. 31, 2021 Balance Sheet and deferred tax and valuation equity worksheets.

Note: Not all items will be used

(all balances are as of 12/31/21, unless otherwise stated)

a. Cash on hand and in checking account 113,000

b. Market value of all equipment 175,000

c. 2020 Income taxes paid in 2021 32,000

d. Cash operating expenses paid in 2021 618,000

e. Principal portion of 15-year loan due in 2022 15,000

f. Principal paid on 15-year loan during 2021 14,000

g. Inventories: Supplies 9,000

h. Insurance premiums for 2022 paid in Dec. 2021 20,500

i. Investment in local brewery made in 2016 for $50,000,

estimated market value of $70,000 as of 12/31/21 ???

j. Change in accounts payable during 2021 1,500

k. Inventories: Completed goods 27,000

l. Principal balance on operating (short-term) loan 16,000

m. Accrued interest on 15-year note 9,000

n. Accrued interest on operating (short-term) loan 1,000

o. Rent for January 2022, paid in Dec. 2021 5,000

p. Accumulated depreciation on equipment 110,000

q. Accrued income, social security, and payroll taxes 21,000

r. 2022 liquor license fees, paid in Dec. 2021 4,000

s. Original cost of all equipment owned 295,000

t. Legal/accountancy services used in 2021, bill not yet received 3,000

u. Accounts payable 39,000

v. Total principal balance on 15-year loan 180,000

w. Mutual funds valued at $83,000 as of 12/31/21, book value of $70,000 ???

x. Capital originally contributed by owner(s) 100,000

Other:

Federal tax rate 25%

Self-employment tax rate 15.30%

State tax rate 5%

Note that a few items have already been added for you (a., b., and x.). There are subtotal calculations in some cells that will update as you enter values on the balance sheet or deferred tax worksheets (cells shaded in gray). The deferred tax value, current and non-current are shaded in red on both the balance sheet and the deferred tax worksheet. The valuation equity cells are shaded in blue on both the balance sheet and the valuation equity worksheet. These cells all contain formulas and references that should not be changed; they will update/compute automatically as you complete the assignment.

Assignment:

1.

Develop a completed balance sheet and completed statement of deferred taxes and Valuation Equity report. Reference line items above on the B/S, Deferred Tax, and Valuation Equity reports using the item letters (a few examples are completed for you). (70 points)

Ive included a blank template in the Homework 1 folder along with the assignment. Note that some calculations/subtotals are already built in to the template (see bolded note on previous page), but you should double-check each of them to ensure their accuracy.

2.

Briefly explain why Deferred Taxes are included on the B/S. What would the implications of ignoring deferred taxes be for the B/S? (15 points)

3.

Briefly explain/define Valuation Equity and its potential sources. (15 points)

4.

Extra Credit: For each unused line item above, provide a brief description of why it was NOT used on the 12/31/21 Balance Statement (0.5 points each)

Answer & Explanation Solved by verified expert

See Answer