AutoSave OFF H ? C ... HW1 template.xIs - Compatibility Mode Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Comments LE Share E20 X V fx =SUM(E7:E10, E12:E19) ABC F H I J K M N P R for Illini Tap - HW 1 As of December 31, 2021 Notes ASSETS 2021 LIABILITIES AND OWNER EQUITY 2021 Cash on hand and in checking account 113,000 Current Asset CURRENT ASSETS CURRENT LIABILITIES Market value of all equipment 75,000 NonCurrent Asset Cash a. Accounts payable 2020 Income taxes paid in 2021 32,00 Savings & short-term deposits Notes payable within 1 year Cash operating expenses paid in 2021 $18,00 Marketable securities (at market) Current portion of all term debt Principal portion of 15-year loan due in 2022 15,000 Accounts receivable Accrued interest Principal paid on 15-year loan during 202 14,000 nventories: Accrued expense Inventories: Supplies 9,00 Supplies and other Accrued taxes Insurance premiums for 2022 paid in Dec. 2021 20,500 Raw Inputs Accrued business expenses nvestment in local brewery made in 2016 for $50,000, ntermediate goods Other accrued items estimated market value of $70,000 as of 12/31/21 ??? Completed Goods Current portion-deferred taxes Change in accounts payable during 2021 1,500 Cash investment in work-in-progress Other current liabilities - business Inventories: Completed goods 27,000 Prepaid expenses Other current liabilities - personal Principal balance on operating (short-term) loan 16,00 Other current assets - business Accrued interest on 15-year note 9.000 Other current assets - personal Accrued interest on operating (short-term) loan 1,000 TOTAL CURRENT ASSETS 113,000 TOTAL CURRENT LIABILITIES Rent for January 2022, paid in Dec. 202 5,000 Accumulated depreciation on equipment 110,000 Accrued income, security, and payroll taxes 21,000 ONCURRENT ASSETS NONCURRENT LIABILITIES 2022 liquor license fees, paid in Dec. 202 4,00 Intermediate assets (at market) Noncurrent portion of term business debt Original cost of all equipment owned 295,000 Machinery & equipment (at market) 175,000 b . Non-real estate debt: Legal/accountancy services used in 2021, bill not yet received 3,000 Notes with original maturity less than 10 years Accounts payable 39,000 Investments in capital leased assets Notes with original maturity greater than 10 years Total principal balance on 15-year loan 80,000 Investments In cooperatives Real estate debt: Mutual funds valued at $83,000 as of 12/31/21, book value of $70,000 ?? Investments in other business entities Notes with original maturity less than 10 years Capital originally contributed by owner(s) 100,000 Equity Section, one component of equity Notes with original maturity greater than 10 years Business real estate (at market Noncurrent portion-deferred taxes Other Land Other noncurrent liabilities - business Federal tax rate 25% Buildings Other noncurrent liabilities - personal Self-employment tax rate 5.30% State tax rate 5% Other noncurrent assets -business (at market) TOTAL NONCURRENT LIABILITIES Other noncurrent assets-personal (at market) TOTAL NONCURRENT ASSETS 175,000 TOTAL LIABILITIES OWNER EQUITY Contributed capital 100,000 X. Retained earnings Valuation equity TOTAL OWNER EQUITY 288,000 TOTAL ASSETS 288,000 TOTAL LIABILITIES & OWNER EQUITY 288,000 NOTES to accompany Financial Statements Book values (cost less depreciation) Financial assets Other investments Machinery and equipment Real-estate land Real-estate buildings BRE Info Balance Sheet Deferred Taxes and VE Short Answer + Ready Accessibility: Unavailable 68%AutoSave OFF AH ? C ... HW1 template.xls - Compatibility Mode Q Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Comments LE Share 131 X fx =0.124*MIN(130,125)+0.029*125 A B D F G H M N o P Q R S T U V W X Y Z AA AB AC AD AE Calculation of Deferred Taxes for M A W N 4 As of Illini Tap - HW 1 December 31, 2021 Market Value Tax Basis Difference Current Portion Valuation of Specific Current Asset Items Marketable securities Inventories lololo Accounts receivable 13 Cash investment in work in progress Prepaid expenses 1. Excess of Market Value over Tax Basis of Current Assets (Sum) Deductions Accounts payable Accrued interest Accrued taxes Other accrued expenses 2. Total deductions 3. Current portion of deferred taxable income [1 -2, zero if negative] 4. Estimated federal and state income tax rate 30% 5. Deferred federal and state tax expense [3x4 6. Estimated self-employment tax rate 15.3% 7. Social Security Tax Income Limit 147 000 8. Deferred self-employment tax [the minimum of 3x6 or 7] 9. Total current portion of deferred income taxes [5+8] Noncurrent Portion Market Value Tax Basis Gain/Loss Deferred taxable income from noncurrent assets Real Estate - Land Machinery and equipment Real Estate - Buildings Other Noncurrent Assets 10. Noncurrent portion of deferred taxable income [sum, zero if negative] 1. Estimated federal and state income tax rate 30% 12. Total noncurrent portion of deferred taxes [10x 11] Valuation Equity: Gain/Loss Real Estate - Land Machinery and equipment Real Estate - Buildings Other Noncurrent Assets Financial Assets/Marketable Securities Less: Personal Liabilities NonCurrent Deferred Taxes Other Total Valuation Equity REAR Info Balance Sheet Deferred Taxes and VE Short Answer + Ready Accessibility: Unavailable 75%100% A View Zoom Add Page Insert Table Chart Text Shape Media Comment Collaborate Format Document Instructions: (i) Use the Excel template provided in the Homework 1 folder on the Canvas website ("HWI template") (ii) This assignment should submitted to Canvas by 5:00 pm on Wednesday September 6, 2023. (iii) You should submit the excel template with the 'Balance Sheet' and Deferred Taxes and VE' worksheets both completed, and answers to the short questions included on the 'Short Answer' worksheet 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). You've 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 Nothing selected. putting together an accurate system of financial statements for your business for the 202 Select an object or text to format. 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)100% A View Zoom Add Page Insert Table Chart Text Shape Media Comment Collaborate Format Document 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 Insurance premiums for 2022 paid in Dec. 2021 20,500 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,50 k. Inventories: Completed goods 27,000 1. Principal balance on operating (short-term) loan 16,00 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 1. 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 owned295,000 t. Legal/accountancy services used in 2021, bill not yet received 3,000 Nothing selected. Accounts payable 39,000 Select an object or text to format. 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.100% A View Zoom Add Page Insert Table Chart Text Shape Media Comment Collaborate Format Document 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) I've 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 Nothing selected. why it was NOT used on the 12/31/21 Balance Statement (0.5 points each) Select an object or text to format