excel problem
Instructions This spreadsheet contains four worksheets relating to partnership income allocation and the admitting of a new partner. You are to complete all four worksheets as part of a full homework submission for this week. For the first two worksheets relate to allocating a profit or loss among partners according to a what was agreed on in a partnership agreement. For each distribution schedule, you are to enter the correct amount in the gray-shaded cells. If there is no entry required for a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Please remember not to copy or paste formulas - just enter numbers or "zero". Exercises 2A and 2B relate to admitting a new partner to an existing partnership. For each scenario, enter the correct amount into the gray-shaded cells (do not copy or paste formulas). When correct, the cells will change color to a light green. Exercise 3 contains word problems that represent how you would see multiple choice questions on our exams and the CPA exam relating to this material. Ex 1A Income or Loss Allocation Exercise 1A - Allocation of Partnership Profit or Loss The WhyEyeOughta general partnership is comprised of three general partners; Mo, Larry, & Curley. The partnership agreement calls for profits of the partnership to be allocated as per the following: A) If there is a profit, each partner is to receive 10% interest on their capital balance at year-end in excess of $50,000 Capital balances for each partner as of December 31st, 20X1 are as follows: Mo$40,000Larry$60,000Curley$80,000 B) Mo & Larry are to receive salaries of $15,000 each; Curley is to receive a salary of $5,000 only if there is a profit C) Larry is to receive a bonus of 20% of partnership profit if there is a profit D) Any remaining profit or loss is to be allocated to Mo, Larry, & Curley in the ratio of 1:2:1 respectively Prepare a distribution of income schedule for the partnership if there was a 20X1 net income of $200,000 by entering the proper amounts in the gray-shaded cells. If there is no amount to be entered in a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Do not copy / copy & paste formulas! MoLarryCurleyTotal Interest on Capitalzero$1,000$3,000 Salaries15,00015,0005,000 Bonuszero40,000zero Sub-Total Remainder to be Allocated30,2506,05030,250 Total$45,250$116,500$38,350 Prepare a distribution of income schedule for the partnership if there was a 20X1 net loss of <$100,000> by entering the proper amounts in the gray-shaded cells. If there is no amount to be entered in a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Do not copy / copy & paste formulas! MoLarryCurleyTotal Interest on Capitalzerozerozerozero Salaries15,00015,000zero30,000 Bonuszerozerozerozero Sub-Total Remainder to be Allocated(32,500)(65,000)(32,500) Total($17,500)($50,000)($32,500) Prepare a distribution of income schedule for the partnership if there was a 20X1 net income of $20,000 by entering the proper amounts in the gray-shaded cells. If there is no amount to be entered in a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Do not copy / copy & paste formulas! MoLarryCurleyTotal Interest on Capitalzero$1,000$3,000 Salaries15,00015,000 Bonuszero Sub-Total Remainder to be Allocated(3,500)(7,000) Total$11,500$9,000 Ex 1B Income or Loss Allocation Exercise 1B - Allocation of Partnership Profit or Loss The Newman, LLP partnership is comprised of four general partners; Elaine, Jerry, George, & Kramer. The partnership agreement calls for profits of the partnership to be allocated as per the following: 1) Jerry and Kramer are to receive 5% bonuses on partnership profit if there is a profit 2) Each partner is to receive 20% interest on their capital balance at year-end. 3) Each partner is to receive a salary of $25,000 if there is a profit, $5,000 if there is not a profit. 4) Any partner that withdrew less than $50,000 from their capital account during the year is to receive a $10,000 bonus 5) Any remaining profit or loss is to be allocated to Elaine, Jerry, George, & Kramer in the ratio of 4:2:3:1 respectively ElaineJerryGeorgeKramer Year-End Capital Balances$150,000$50,000$75,000$200,000 Total Annual Capital Withdrawals$40,000$60,000$10,000$55,000 Prepare a distribution of income schedule for the partnership if there was a 20X1 net income of $500,000 by entering the proper amounts in the gray-shaded cells. If there is no amount to be entered in a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Do not copy / copy & paste formulas! ElaineJerryGeorgeKramerTotal Profit Bonuszero$25,000zero$25,000$50,000 Interest on Capital30,00010,00015,00040,00095,000 Salary25,00025,00025,00025,000100,000 Withdrawal Bonus10,000zero10,000zero20,000 Sub-Total$65,000$60,000$50,000$90,000$265,000 Remainder to be Allocated94,00047,00023,500235,000 Total$159,000$97,000$113,500$500,000 Prepare a distribution of income schedule for the partnership if there was a 20X1 net income of $100,000 by entering the proper amounts in the gray-shaded cells. If there is no amount to be entered in a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Do not copy / copy & paste formulas! ElaineJerryGeorgeKramerTotal Profit Bonuszero$5,000zero$5,000$10,000 Interest on Capital30,00010,00015,00040,00095,000 Salary25,00025,00025,00025,000100,000 Withdrawal Bonus10,000zero10,000zero20,000 Sub-Total$65,000$40,000$50,000$70,000$225,000 Remainder to be Allocated(50,000)(37,500)(25,000)(12,500)(125,000) Total$15,000$2,500$25,000$57,500$100,000 Prepare a distribution of income schedule for the partnership if there was a 20X1 net loss of <$100,000> by entering the proper amounts in the gray-shaded cells. If there is no amount to be entered in a particular cell, type the word "zero". Do not enter a "0". When correct, the cells will change color to a light green. Do not copy / copy & paste formulas! ElaineJerryGeorgeKramerTotal Profit Bonuszerozerozerozerozero Interest on Capital30,00010,00015,00040,00095,000 Salary5,0005,0005,0005,00020,000 Withdrawal Bonus10,000zero10,000zero20,000 Sub-Total$45,000$15,000$30,000$45,000$135,000 Remainder to be Allocated(94,000)(70,000)(47,000)(23,500)(235,000) Total($49,000)($55,500)($17,000)$21,500($100,000) Ex 2A New Partner Exercise 2A - Admitting a New Partner The AlphabetSoup Partnership has three existing partners with capital accounts and profit splits as follows: PartnerCapital BalanceProfit Interest A$1,000,00010% B6,000,00060% C3,000,00030% $10,000,000100% If AlphabetSoup admits a new partner, under each of the following scenarios determine how the is the entry is booked by entering the proper dollar amount in each gray-shaded cell. When correct, the cells will change color to a light green. Scenario 1: New Partner D contributes $10,000,000 for a 50% capital share of the firm. $10,000,000 / 50% implies a FMV of $20,000,000 Total new capital = $10,000,000 + $10,000,000 = $20,000,000 AccountDebitCredit Assets (Contributed by D) Capital - D Scenario 2: New Partner D contributes $14,000,000 for a 50% capital share of the firm. The firm uses the bonus method of accounting for new partners and the bonus is applied to the existing partners. $14,000,000 / 50% implies a FMV of $28,000,000 Total new capital = $10,000,000 + $14,000,000 = $24,000,000 New Partner Capital Balance = (BV Original + New Contribution) x New Partner % New Partner Capital Balance = ($10,000,000 + $14,000,000) x 50% New Partner Capital Balance = $24,000,000 x 50% = $12,000,000 AccountDebitCredit Assets (Contributed by D) Capital - A Capital - B Capital - C Capital - D Scenario 3: New Partner D contributes $14,000,000 for a 50% capital share of the firm. The firm uses the goodwill method, and any excess over FMV is attributable to existing goodwill. $14,000,000 / 50% implies a FMV of $28,000,000 Total new contributed capital = $14,000,000 + $10,000,000 = $24,000,000 $4,000,000 difference = Goodwill Total New Capital = $2,000,000 + $5,000,000 + $7,000,000 = $14,000,000 AccountDC Assets (contributed by D) Goodwill Capital - A Capital - B Capital - C Capital - D Scenario 4: New Partner D contributes $6,000,000 for 50% share of the firm. The firm uses the bonus method, and any bonus is attributable to the new partner. Total new capital = $10,000,000 + $6,000,000 = $16,000,000 New Partner Capital Balance = (BV Original + New Contribution) x New Partner % New Partner Capital Balance = ($10,000,000 + $6,000,000) x 50% New Partner Capital Balance = $16,000,000 x 50% = $8,000,000 AccountDC Assets (contributed by D)$6,000,0002000000 Capital - A Capital - B Capital - C Capital - D$8,000,0004000000 Ex 2B New Partner Exercise 2B - Admitting a New Partner The AlphabetSoup Partnership has three existing partners with capital accounts and profit splits as follows: PartnerCapital BalanceProfit Interest A$1,000,00025% B700,00040% C300,00035% $2,000,000100% If AlphabetSoup admits a new partner, under each of the following scenarios determine how the is the entry is booked by entering the proper dollar amount in each gray-shaded cell. When correct, the cells will change color to a light green. Scenario 1: New Partner D contributes $2,000,000 for a 50% capital share of the firm. AccountDebitCredit Assets (Contributed by D) Capital - D Scenario 2: New Partner D contributes $3,000,000 for a 50% capital share of the firm. The firm uses the bonus method of accounting for new partners and the bonus is applied to the existing partners. AccountDebitCredit Assets (Contributed by D)$3,000,000 Capital - A$125,000 Capital - B$200,000 Capital - C$175,000 Capital - D$2,500,000 Scenario 3: New Partner D contributes $3,000,000 for a 50% capital share of the firm. The firm uses the goodwill method, and any excess over FMV is attributable to existing goodwill. AccountDC Assets (contributed by D) Goodwill Capital - A Capital - B Capital - C Capital - D Scenario 4: New Partner D contributes $1,000,000 for 50% share of the firm. The firm uses the bonus method, and any bonus is attributable to the new partner. AccountDC Assets (contributed by D) Capital - A Capital - B Capital - C Capital - D 3 Word Problems Below are several "word type" problems that are written in the manner / style one will expect to see on both our exams and the CPA exam when it comes to certain aspects of the material