Module 5 ch.15 19 20 21 .xlsx

Chapter 15: Building wealth through investments 15.1 Stocks: Example 2: Find the current yield of AT&T stock that reported a dividend of %1.68 and a closing price of $26.28 Current Yield = (annual dividend per share / closing price per share) * 100% Find current yield High 1.68 Low 26.28 Current Yield = (D7/D8)*100%6.3926940639269403E-2 Price-earnings (P/E) ratio = current price share / net income per share (past 12 months) Example 3: Find the P/E ratio of a corporation that reported last year's net income as $6.16 per share if the company's stock sells for $58 per share. Price per earnings ratio Current price per share 58 Net income per share 6.16 P/E ratio: D18/D199.4155844155844157 *round to the nearest whole number* P/E ratio = 9 investors are willining to pay $9 for every $1 of last year's earnings for this stock at the price of $58. Calculate & distribute dividends from an available amount of money Example 4: A company has issued 20,000 shares of cumulative preferred stock that will earn dividends at $0.60 per share and has issued 100,000 shares of common stock. Last year the company paid no dividends. This year $250,000 is available for dividends. How are the dividends to be distributed? distribution of dividends Dividends for preferred stock holders 12000 (20,000 * $0.60) preferred stock holders remaining money 250000-12000238000 ($250,000 - $12,000) To preferred stockholders for current year 20000*0.612000 (20,000 * $0.60) Total to preferred stockholders 12000+1200024000 ($12,000 * 2 years) Amt left for common stockholders D32-D31226000 ($238,000 - $12,000) Share per common stockholders D35/1000002.2599999999999998 ($226,000 / 100,000) Preferred stockholders receive $24,000 & common stockholders receive $226,000 at a rate of $2.26 per share. 15.2 Bonds: Example 2: Calculate the closing price of the C.HRY bond. From column 8, the closing price as a percent of face value was 117.733% Calculate the price of bonds. 117.733% = 1.17733 Closing bond price = D46*10001177.33 ($1,000 * 1.17733) Closing bond price is $1,177.33 Calculate the current yield of a bond. Example 4: Calculate the current bond yield for KFT.GX (Table 15-3) Current yield = ($1,000 * stated interest rate (as a decimal) / current price of bond) * 100% Current yield = ((1000*0.05375)/1034.11)*100%5.1977062401485342E-2 (($1,000 * 0.05375) / $1,034.11) * 100% *Round to the nearest thousandths* Example 2: Find the sales charge and the sales charge percent for one share of MLSAX mutual fund stock if the stock was offered at $11.59. Use Table 15-4 Mutual fund sales charge = selling (offer) price - net asset value Find mutual fund charge and sales charge percent Sales charge percent = (sales charge / net asset value) * 100% 1 Stock offer 11.59 NAV 11.16 Mutual fund sales charge = D65-D660.42999999999999972 ($11.59 - $11.16) Sales charge percent: (D67/D66)*D643.8530465949820764E-2 (%0.43 / $11.16) * 100% Sales charge percent = D68*D643.8530465949820764E-2 (0.03853 * 100%) Example 3: Find the beginning of year NAV for IICAX. Beginning of year NAV = current NAV / (100% + YTD% return) Find beginning of year NAV Beginning of year NAV = 8.16/(100%+7.5%)7.590697674418605 [$8.16 / (100% + 7.5%)] Example 4: Calculate the number of shares purchased with a $1,000 investment in a no-load mutual fund with a net asset value (NAV) of $4.82. Number of shares purchased = total investment / offer price Find shares purchased Amount before increase = [ current amt / (100% + increase %)] Amount before decrease = [ current amt / (100% - decrease%)] Investment 1000 NAV 4.82 Number of shares purchased = D84/D85207.46887966804979 ($1,000 / $4.82) *Roud to the nearest thousandths.* Example 5: Calculate return on investment for 1,000 shares of a mutual fund purchased with an offer price of $8.16, which were sold with a NAV of $9.36, and had paid a dividend during ownership of $0.27 per share. Gain or loss on investment = (proceeds of sales + additions) - total cost Find ROI ROI = total gain (or loss) / total cost of investment Total proceeds from sale = 1000*9.369360 (1,000 shares * $9.36) Additions = 1000*0.27270 (1,000 shares * $0.27) Total cost of purchase = 1000*8.168160 (1,000 shares * $8.16) Gain (or loss) on investment = (9360+270)-81601470 [($9,360 + $270) - $8,160] ROI = 1470/81600.18014705882352941 ($1,470 / $8,160) *Round to the nearest tenth of a percent* Equations for this chapter: NAV = net asset value Stocks: Current Yield = (annual dividend per share / closing price per share) * 100% Price-earnings (P/E) ratio = current price share / net income per share (past 12 months) Bonds: Closing bond price = $1,000 * closing price Current yield = ($1,000 * stated interest rate (as a decimal) / current price of bond) * 100% Mutual fund sales charge = selling (offer) price - net asset value Sales charge percent = (sales charge / net asset value) * 100% Beginning of year NAV = current NAV / (100% + YTD% return) Number of shares purchased = total investment / offer price Amount before increase = [ current amt / (100% + increase %)] Amount before decrease = [ current amt / (100% - decrease%)] Gain or loss on investment = (proceeds of sales + additions) - total cost ROI = total gain (or loss) / total cost of investment Homework 1: 1) What was the closing share price in dollars and cents for C CORP? Closing share price on Table The cloing share price is: 48.78 2) What is the difference between this day's high price and low price for C CORP? Difference btw high and low High 49.11 Low 48.47 The difference is = D141-D1420.64000000000000057 3) Find the current yield of a company that reported a dividend of $1.82 and a closing price of $23.01. Current Yield = (annual dividend per share / closing price per share) * 100% Find Current yield of stock 1 Dividend 1.82 Closing price 23.01 *Round to the nearest tenth* Current yield = (D153/D154)*D1527.9096045197740106E-2 4) Find the P/E ratio of a corporation that reported last year's net income of $2.46 per share if the company's stock sells for $48.21 per share. P/E ratio = current price share / net income per share (past 12 months) Find Price per earnings ratio Current share price 48.21 Last years share price 2.46 *Round to the nearest whole number* P/E ratio = D163/D16419.597560975609756 per share 5) If C CORP. had 3,678,000 shares of common stock outstanding when it paid dividends last year, how much did it pay in dividends? Find dividends paid Common stock shares 3678000 Div. in chart 1.48 Dividends in arrears D174*D1755443440 6) One company has $200,000 to distribute in dividends. There are 21,000 shares of preferred stock that earn dividends at $0.30 per share and 80,000 shares of common stock. How much money goes to preferred stockholders? Find amount going to preferred Dividends distributed 200000 Preferred stock shares 21000 Dividends for preferred 0.3 Common stock shares 80000 Money for preferred stockholders D184*D1856300 7) A corporation has $1,140,000 to distribute in dividends and did not distribute dividends the previous year. There are 150,000 shares of cumulative preferred stock that earn dividends at $0.96 per share and 700,000 shares of common stock. How much money goes to common stockholders? Find amount going to Common Dividends distributed 1140000 Preferred stock shares 150000 Dividends for preferred 0.96 Common stock shares 700000 Years 2 Dividends for preferred stock holders D196*D197144000 remaining money D195-D200996000 To preferred stockholders for current year D196*D197144000 Total to preferred stockholders D202*D199288000 *Answer* -> Amt left for common stockholders D195-D203852000 *Amount to common stock holders* Share per common stockholders D204/D1981.2171428571428571 8) Refer to the bond listing table below to determine the coupon rate and maturity of a bond issued by Barclays Bank PLC. Coupon rate = annual payout as a percentage of the bond's per value Coupon rate and maturity on Table Coupon rate 0.05 Maturity of bond 44075 *All info needed for question is on table* 9) Refer to the bond listing table below to determine the yield to maturity for the bond issue of Citigroup? Yield percent from bond table Yield % 5.9400000000000001E-2 *All info needed for question is on table* 10) Refer to the bond listing table below to determine which of the two bonds, BAC.IOP or GS.IAR, is producing the greater yield to maturity? Which one produces greater yield to maturity? Find current yield of bond and difference Current yield = ($1,000 * stated interest rate (as a decimal) / current price of bond) * 100% 1000 1 BAC.IOP GS.IAR Interest rate 4.4999999999999998E-2 5.3749999999999999E-2 Current price of bond 100.23099999999999 96.641999999999996 Current yield ((C237*D239)/D240)*C2380.44896289571090781 ((C237*E239)/E240)*C2380.55617640363403076 Higher current yield^ 11) Find the price per share of ASMTX for the previous day. Find yesterday's share price NAV 8.64 Change 0.02 *Represents change in NAV for the last day.* Yesterday's share price D252+D2538.66 12) Calculate the number of shares purchased with a $7,500 investment in a no load mutual fund with a net asset value of $7.52. NAV = net asset value Find shares purchased Investment 7500 NAV 7.52 Shares purchased D265/D266997.340425531915 *Round to the nearest thousandths* 13) Calculate the return on investment for 2,000 shares of a mutual fund purchased with an offer of $15.94 if the shares were sold with a NAV of $18.84. The shares paid a dividend of $0.79 per share during ownership. Find the ROI Shares 2000 NAV 18.84 Offer price 15.94 Dividend 0.79 Total proceeds from sale = D278*D27937680 Additions = D278*D2811580 Total cost of purchase = D278*D28031880 Gain (or loss) on investment = (D282+D283)-D2847380 ROI = D285/D2840.2314930991217064 *Round two decimal places* 14) With the upcoming annual shareholders' meeting only a week away, the CEO of a business had a great deal of info to prepare. Profits for the five-year-old plastics company where at record levels and $250,000 was available for dividends to be paid. But tech. advancements in the thermoforming industry were forcing individual companies to make subtantial investments in advanced production capacity to remain viable. Find the dividen distribution for both types of stockholders, The CEO would be recommending to the board of directors a $2.2 million corporate bond issue to pay for the improved production capabilities. In addition, The company would be offering a 401(k) retirement program. Current yeild, amt shares able to purchase monthly Also, the first 4% of an employee's salary contributed would be fully matched by the company. Answer parts 1 through 4. NAV, ROI 1. The company has previously issued 25,000 shares of cumulative preferred stock that will earn dividends at $0.70 per share and 75,000 shares of common stock. Because no dividends were paid last year, how will the $250,000 declared for dividends be distributed? -How much will Common stockholders receive? -How much per share will Common stockholders receive? 2. A $1,000 corporate bond is issued and has a stated interest rate of 5.362% with a current price of 93.1. What is the current yield? 3. A sales manager at the company decides to put 4% of his $84,000 salary into an international growth fund offered through the new 401(k) plan. The current net asset value is $17.93 and the year-to-date return is +4.4%. - How many shares will the sales manager be able to purchase each month? - What was the net asset value of the fund at the beginning of the year? 4. A new employee decided to roll over her existing 401(k) plan, worth $26,069, into a fund with a net asset value of $13.10. - Calculate the number of shares that the new employee purchased with this initial investment. - Calculate the ROI if the new employee's share grew to $14.49 per share with a dividend paid of $0.25 per share during ownership, before she moved it to a different fund. *Ignore the 4% matching on all calculations.* Current yield = ($1,000 * stated interest rate (as a decimal) / current price of bond) * 100% Dividends distributed 250000 Preferred stock shares 25000 Dividends for preferred 0.7 Common stock shares 75000 Years 2 Dividends for preferred stock holders D313*D31417500 remaining money D312-D317232500 Question 1 To preferred stockholders for current year D313*D31417500 Total to preferred stockholders D319*D31635000 Amt left for common stockholders D312-D320215000 Share per common stockholders D321/D3152.8666666666666667 1 1000 Interest rate 5.3620000000000001E-2 Current price of bond 93.1 Current yield = ((D324*D325)/D326)*C3240.5759398496240602 Question 2 Correct answer: 5.7599999999999998E-2 Manager's salary 84000 Salary % invested 0.04 Net asset value 17.93 Year-to-date return 4.3999999999999997E-2 Amount invested by manager D330*D3313360 Total after company matches D334*26720 Monthly invested total D335/12560 Question 3 Shares per month D336/D33231.232571109871724 *Round to the nearest thousandths* Nav beginning of year D332/(C324+D333)17.174329501915707 Question 4 Amount transferred over 26069 Net asset value 13.1 Shares D340/D3411990 If NAV grew to 14.49 Dividend paid per share 0.25 Total proceeds D342*D34428835.100000000002 Additions D342*D345497.5 Total cost of purchase = D342*D34126069 Gain (or loss) on investment = (D346+D347)-D3483263.6000000000022 ROI = D349/D3480.12519083969465658 15) Calculate the current bond yield for a bond that has a current bond price of 97.960% and a stated interest rate of 6.299%. Current yield = ($1,000 * stated interest rate (as a decimal) / current price of bond) * 100% Current yield 1 1000 Bond price 0.97960000000000003 Interest rate 6.2990000000000004E-2 Correct answer: Current yield ((D360*D362)/D361)*C36064.301755818701508 6.4299999999999996E-2 *Round to the nearest thousandths* THE END Chapter 19: Insurance 19.1: Life Insurance Example 1 Estimate the annual premium of an insurance policy with a preferred rate class and a face value of $100,000 for a 30-year-old male for: a) a 10-year level term policy, b) 20-year level term policy, c) a whole life policy, d) a universal life policy Find all Estimated annual premiums Estimated annual premium = rate * (face value / $1,000) 10-year level term policy Face value 100000 Rate from table 0.87 *Rate is on Table 19-1* 1000 Estimated annual premium D11*(D10/D12)87 20-year level term policy Face value 100000 Rate from table 1.1200000000000001 1000 Estimated annual premium D17*(D16/D18)112.00000000000001 Whole life policy Face value 100000 Rate from table 10.86 1000 Estimated annual premium D23*(D22/D24)1086 Universal life policy Face value 100000 Rate from table 7.41 1000 Estimated annual premium D29*(D28/D30)741 Example 2 Use Table 19-1 & 19-2 to estimate the (a) semiannual, (b) quarterly, and (c) monthly premiums for a $250,000 whole life policy on a 40-year-old female, using a non-tobacco rate. Annual premium = rate * (amount of coverage / $1,000) Find all types of Annual premiums Table 19-2: Period Percent of Annual Premium Semiannually 0.51 Quarterly 0.26 Monthly 8.7499999999999994E-2 1000 Face value 250000 Rate on table 19-1 13.48 Annual premium D46*(D45/D44)3370 * $13.48 * ($250,000/ $1,000) * Premium options: (a) Semiannually D40*D471718.7 * 51% * $3,370 * (b) Quarterly D41*D47876.2 * 26% * $3,370 * (c) Monthly D42*D47294.875 * 8.75% * $3,370 * Eample 3 Eleanor McLeod, a smoker, started a $100,000 whole life insurance policy when she was 30 years old. At age 50, she determines that her policy has a cash value of $20,200 and wants to convert to extended term for the same face value. Using 20-year level term rates, estimate how long her her extended term insurance will last. Round to the nearest hundredth of a year. Find Years of paid-up term insurance Years of paid-up term insurance = cash value of surrendered policy / annual premium of term policy Policy cash value 20200 Policy amount 100000 Annual term premium: 1000 Estimated annual term premium: $8.02 per $1,000 8.02 # of insurance units D62/D63100 50-year-old female smoker; Annual term rate D64*D65802 20-year level term rate Years of paid-up term insurance D61/D6625.187032418952619 Years 19.2: Property Insurance How to Estimate an annual renters insurance premium using a rate table: 1. Locate the base annual premium in Table 19-3 according to maximum policy coverage limit, liability limit, deductible, and credit score rating for the applicant. 2. Add the additional cost for any options selected. 3. Compute the annual cost for extended coverage endorsments. 4. Add the premiums from stes 1 to 3. Example 1 Kirsten Lewen wants to buy renters insurance for her new apartment. She has excellent credit, and wants to find the most affordable policy. She also decides to add the identity theft/fraud protection, and an additional $2,000 of computer equipment coverage. Find the annual premium for a $20,000 policy, with the minimum liability offered, using a $1,000 deductible. Find Total annual premium and cost for add. Coverage Cost = rate for endorsement * (coverage desired / $100) Total annual premium = base annual premium + cost for each option + cost for each extended coverage endorsement 100 rate for endorsement 0.95 Policy 20000 Liability 300000 Deductible 1000 Theft/fraud protection 20 Annual base premium 126 Computer equip. protection 2000 Cost for additional Computer coverage D83*(D89/D82)19 Total annual premium D88+D87+D90165 How to estimate an annual homeowners insurance premium using a rate table 1. Locate the base annual rate in Table 19-4. 2. Add the additional cost for any option selected. 3. Compute the annual cost for extended coverage endorsements. 4. Add the premiums from steps 1 to 3. Example 2 Eric and Angela are in the process of buying a new home and need homeowners insurance. Their credit history, as provided by the bank, shows that they both occasionally make payments past 30 days, but with no other major problems. They need to insure their masonry home for $150,000, which is located in fire protection zone 1, and decide to go with a $1,000 deductible. Find the annual premium for their homeowners They also decide to add the identity theft/fraud protection and the sewer/sump pump backup coverage, and an additional $3,000 of protection for jewelry, watches, and furs policy. The cost for the identity theft/fraud protection is $20 and the cost for sewer backup is $75. Base annual premium = rate from table *(dwelling coverage / $100) Annual Cost for extension = rate for endorsement *(coverage desired / $100) Total annual premium = base annual premium + cost for each option + cost for each extended coverage endorsement Home coverage 150000 Deductable 1000 Additional protection 3000 Rate for dwelling 0.36 * Rate for dwelling is $0.36 per $100 * 100 Annual premium for dwelling D111*(D108/D112)540 * $.036 *($150,000 / $100) * Theft/fraud protection 20 Sewer backup 75 Rate for additional protection 0.85 Cost for addition coverage (Jewelery,etc.) D116*(D110/D112)25.5 * $0.85 *($3,000 / $100) * Total annual premium D113+D114+D115+D117660.5 * $540 + $20 + $75 + $25.50 * How to find the Compensation with a Coinsurance Clause 1. Find the face value required by the 80% coinsurance clause for full compensation: Multiply 0.8 by the replacement value of the property. 2. Find the compensation for the loss if the insurance is less than 80% of the replacement value: Example 3 Cassandra Brighton owns a home with a replacement value of $200,000. She has a homeowners insurance policy with an 80% coinsurance clause and a face value of $130,000. There is a fire, and the building damage is figured to be $50,000. What will the insurance company pay as compensation? Does Cassandra carry as much insurance as its coinsurance clause requires for full preotection? Compensation = loss * (face value of policy / 80% of replacement value of property) Replacement value 200000 Compensation 0.8 Face value 130000 Damage cost (LOSS) 50000 Face value required for comp. D130*D129160000 * 0.8 * $200,000 * Compensation D132* (D131/D133)40625 * $50,000 * ($130,000 / $160,000) * Cassandra recieves $40,625 compensation for her loss of $50,000. How to find an annual automobile insurance premium using table values 1. Locate the bodily injury and property damage premium according to territory, credit rating, and per person/per accident bodily injury and property damage coverage (Table 19-5). 2. Locate the comprehensive premium in Tbale 19-6 according to model class, vehicle age, territory, credit rating, and deductible. 3. Loacate the collision premium in Table 19-6 according to model class, vehicle age, territory, credit rating, and deductible. 4. Add the premiums from steps 1 to 3 to find the base annual premium. 5. Multiply any applicable discounts by the base premium and subtract. 6. Add any ticket or accident surcharges. Example 1 Use Tables 19-5 and 19-6 to find the annual premium for an automobile insurance policy in which the insured lives in territory 1, has good credit, and selects 50/100/50 coverage. The vehicle is three years old, model class 2 with a deductible for comprehensive of $250 and $500 for collision. The insured has been accident and ticket free for the last three years. Find Total annual premium Total annual premium = (Bodily injury/preporty damage premium) + comprehensive premium + collision premium - discounts + surcharges Liability premium 385 * Territory 1, good credit, 50/100/50 (Table 19-5) * Comprehensive premium 301 * Model class 2, age 3, $250 deductible, good credit (Table 19-6) * Collision premium 417 Discount 0.05 * accident-free discount * Base annual premium (before discount) D151+D152+D1531103 Amount discounted D155*D15455.150000000000006 Total annual premium D155-D1561047.8499999999999 Chapter 20: Taxes 20-1 Sales Tax & Excise Tax How to use the percent method to find the sales taxe or excise tax 1. Write the given tax rate percent as a decimal. 2. Find the sales tax or excise tax: Example 1 Kentucky has a state sales tax rate of 6%. Find the Kentucky state sales tax on $128.72 at six cents per $1.00, or 6%. Tax = tax rate * purchase price , where tax rate = tax per $1.00 of the purchase price or a percent of the purchase price. Find sales tax Sales Tax Rate 0.06 * or $0.06 per $1.00) * Amount being taxed 128.72 Sales tax D173*D1747.7231999999999994 Example 2 Find the pump price for a gallon of gasoline if the gasoline costs $1.50 per gallon, the cost is marked up 20%, and both a federal excise tax of $0.184 and a state excise tax of $0.345 per gallon are included in the pump price. * Look at problem #10 in HW 2 * Find pump price and taxes Cost/gallon 1.5 Markup 0.2 Federal excise tax per gallon 0.184 State excise tax per gallon 0.34499999999999997 Mark up cost D182-D183*D1821.2 * $1.50 - ($1.50 * 0.2) * Cost w/ markup D182*D1861.7999999999999998 * 1.2 * $1.50 * Cost and taxes D187+D184+D1852.3289999999999997 * pump price per gallon * Round the final price to the nearest cent. How to find the marked price and the sales tax from the total price 1. Find the marked price: 2. Find the sales tax: Example 3 At an amusement park concession, the items are priced to include tax. Find the marked price and the sales tax. The sales tax rate is 7%. Popcorn: $3; soft drink: $3.50; hot dog: $4 Marked prce = total price / [1 + sales tax rate (in decimal form)] Find marked price and sales tax Sales tax = total price - marked price Popcorn 3 Soft drink 3.5 Hot dog 4 Sales Tax Rate 7.0000000000000007E-2 1 Popcorn marked price D200/(D204+D203)2.8037383177570092 * $3.00 / (1+0.07) * Soft drink marked price D201/(D204+D203)3.2710280373831773 Hot dog marked price D202/(D204+D203)3.7383177570093458 Popcorn sales tax D200-D2050.19626168224299079 * $3.00 - $2.80 * Soft drink sales tax D201-D2060.22897196261682273 Hot dog sales tax D202-D2070.26168224299065423 20-2 Property Tax 1. Find the assesed value Assessed value = assessment rate * market value 2. Calculate property tax A. Express the given property tax rate as tax per $1 of assessed value: (a) If the given rate is a percent of assessed value, Property tax rate per $1 = decimal form of the percent of assessed value (b) If the given rate is tax per $100 of assessed value, Property tax rate per $1 = tax on $100 / $100 (c) If the given rate is a number of mill per $1.00 of assessed value, Property tax rate $1 = mills per $1 / 1,000 B. Find the property tax: Property tax = property tax rate per $1 * assessed value Example 1 Find the assessed value of a farm with a market value of $175,000 if the assessed valuation is 25% of the market value. Assessed value = assessment rate * market value Find Assessed value Market value 175000 Assessment rate 0.25 Assessed value D230*D23143750 Example 2 Find the property tax on a home with an assessed value of $90,000 if the property tax rate is (a) 11.08% of the assessed value, Find property tax (b) $11.08 per $100 of the assessed value, (c) $110.80 per $1,000 of the assessed value, (d) 110.8 mills per $1 of assessed value. Assessed value 90000 (a) Property tax rate 0.1108 Property tax D242*D2439972 Assessed value 90000 100 (b) per $100 11.08 Property tax (D248/D247)*D2469972 Assessed value 90000 1000 (c) per $1,000 110.8 Property tax (D253/D252)*D2519972 Assessed value 90000 1000 (d) mills per $1.00 110.8 Property tax (D258/D257)*D2569972 How to determine a property tax rate 1. Select appropriate formula: Tax per $1 of assessed value = total estimated budget / total assessed property value Tax per $100 of assessed value = (total estimated budget / total assessed property value) * $100 Tax per $1,000 of assessed value = (total estimated budget / total assessed property value) * $1,000 Tax, in mills, per $1 of assessed value = (total estimated budget / total assessed property value) * 1,000 2. Make calculations using the selected formula. Always round up Example 3 Find the tax rate expressed as tax per $100 of assessed value for Harbortown, which anticipates expenses of $95,590,000 and has property assessed at $3,868,758,500. Find the tax rate tax per $100 100 Anticipated expenses 95590000 Property assessed 3868758500 Tax rate per $100 of assessed value (D275/D276)*D2742.4708184809157769 Tax rate (rounded up) 2.48 Example 4 Harbortown (see the previous example) expects an increase in expenses of $5,000,000. To cover these expenses, the city has to increase the tax rate or to reassess property values. The city assessor's office predicts that the reassessment would cost $100,000 and increase the city's assessment value of property to $4,300,000,000. Find total reassessed property tax revenue and expense The city leaders prefer the reassessment choice but do not want to reassess property value and increase the tax rate in the same year. Which choice should the city leaders make? Current expenses 95590000 Expected increase in expenses 5000000 Current assessed property 3868758500 Current tax rate per $100 of assessed value 2.48 Exapected reassessment value 4300000000 Cost of reassessment 100000 100 Total property tax revenue from reassessment (D287/D290)*D288106640000 Expected total expenses D284+D285+D289100690000 Conculsion: Property taxes from the reassessment ($106,640,000) are more than expected total expenses ($100,690,000). Because a reassessment will cover the increase in expenses without a tax rate increase, property will be reassessed. 20-3 Income Taxes How to finding taxable income 1. Find adjusted gross income 2. Total the deductions or choose the startd deduction and total the exemptions. 3. Find the taxable income: Example 1 Find the taxable income for a family of four (husband, wife, two children) if their adjusted gross income is $67,754 and their itemized deductions are $11,345. Use $4,000 as the amount of each personal exemption. Adjusted gross income = total income - allowable expenses and deductions Find taxable income Taxable income = adjusted gross income - itemized or standard deductions - exemptions Adjusted gross income 67754 Itemized deductions 11345 Exemption per person 4000 Amount of people in family 4 Taxable income D309-D310-D311*D31240409 How to use the tax tables to calculate income tax 1. use Table 20-1 and locate the income range Example 2 Find the tax owed by a married taxpayer (a) filing jointly on a taxable income of $39,478; (b) filing separately on a taxable income of $39,478. Find tax owed (a) For the taxable income range find the tax for a taxpayer married filing jointly. 4999 (b) Locate the tax in the column to the right headed married filing seperately is The tax is $5,663. How to use the tax compensation worksheet to calculate income tax 1. Locate correct section according to filing status. 2. Locate the range in which the taxable income falls. 3. Enter the taxable income (line 43 of form 1040) on the appropriate line of Column a. 4. Multiply the amount in Column a by the amount in Column b and enter the result in Column c. 5. Subtract the amount in Column d from the amount in Column c and enter the result in the Tax column. This is the amount that will be entered on line 44 on Form 1040. Example 3 Use Table 20-2 to find the tax on a taxable income of (a) $112,418 for a married taxpayer filing jointly; (b) $148,382 for a married taxpayer filing separately. (a) The taxpayer would use Section B. Section B shows that the taxable income falls in the range, “At least $100,000 but not over $151,200.” (b) The taxpayer is married filing separately, so use Section C. The taxable income, $148,382, falls in the range, “Over $115,225 but not over $205,750.” a) Column a Column b Column c Column d Tax 112418 0.25 28104.5 8412.5 C343*D343-F34319692 * $112,418 x 25% - $8,412.50 * b) Column a Column b Column c Column d Tax 148382 0.33 48966.06 12235.5 C347*D347-F34736730.560000000005 * $148,382 x 33% - 12,235.50 * 100/500/100: Limit company willl pay for bodily injury per person / limit company will pay for all bodily injuries / limit company will pay others for property damage, including other vahicles or property THE END Homework 2 (chapter's 19 and 20 1) Find the annual premium for a universal life insurance policy with a face value of ?$47,000 for a? 35-year-old female using a? non-tobacco rate. Use the given table. Estimated annual premium = rate * (face value / $1,000) Find estimated annual premium Face value 47000 Rate 8.25 1000 Estimated annual premium D13*(D12/D14)387.75 2) Use the given table. Compare the premiums for a Universal life policy for $75,000 for a 40-year-old male to the same policy for a 40-year-old female. Use a non-tobacco rate. Compare estimated annual premiums Male Female Face value 75000 D2375000 Rate 12.33 10.17 1000 1000 Estimated annual premium D24*(D23/D25)924.75 E24*(E23/E25)762.75 Difference D26-E26162 3) Use the given table. Cindy started a? whole-life insurance policy for ?$260,000 when she was 23 years old. At age? 50, the policy has a cash value of 12,505 and Cindy converts the policy to extended term insurance for the same face value. Use 20-year level term rates to estimate the number of years of extended term insurance she has. Use a? non-tobacco rate. Find years of extended term insurance Policy cash value 12505 Policy amount 260000 Annual term premium: 1000 4.4000000000000004 # of insurance units D36/D37260 Annual term rate D38*D391144 Years of extended term insurance D35/D4010.930944055944057 4) A home valued at $279,000 is insured in a policy that contains an 80% coinsurance clause. The face value of the policy is $122,000. If the home is a total loss, what is the amount of compensation? Find compensation with 80% coinsurance clause Replacement value 279000 Compensation 0.8 Face value 122000 Loss 122000 Face value required for comp. D51*D50223200 Compensation D53* (D52/D54)66684.587813620077 5) Use the given table to find the total annual premium. Find total annual premium Territory Credit rating Model class Vahicle age Liability coverage Comprehensive deductable Collision deductable 2 GOOD 2 New 50/100/50 250 500 Insurance premium rate 358 Comprehensive deductable rate 297 Collision deductable rate 463 Total annual premium D68+D67+D661118 6) Use the given tables to find the annual premium on a 50/100/50 liability policy for a driver in territory 1 if the vehicle is 4.5 years old and a model class 2. The insured selects a comprehensive deductible of $250 and a collision deductible of $500. The insured's credit is good. Find total annual premium Territory Credit rating Model class Vahicle age Liability coverage Comprehensive deductable Collision deductable 1 good 2 4.5 50/100/50 250 500 Insurance premium rate 382 Comprehensive deductable rate 230 Collision deductable rate 367 Total annual premium D82+D81+D80979 7) Jaime has 250/500/250 vehicle insurance. He has $250 deductable on comprehensive and a $1,00 deductable on collision. Jaime had an at fault crash into another vehicle while texting. Carolyn, Larry, and Maria, the driver and passengers in the other vehicle, were injured. Carloyn's medicial care was $34,148 and Maria's medical care was $57,733. Find insurance requirement of coverage Larry was not injured. Their car required $12,320 to repair. Jaime's vehicle had $11,560 in damages but he had no injuries. How much will the insurance company be responsible for paying and to whom? How much, if any, will Jaime be responsible for paying? Parties injured 5 Carloyn's medicial 34148 Maria's medical 57733 Car 1 damage cost 12320 Car 2 damage cost 11560 Total insurance wil cover of injuries D94+D9591881 Total cost of damages D96+D9723880 Insurance type 100/500/100 Collision deductible -1000 Injury cost insurance will cover per person 250000 Total injury cost insurance will cover 500000 Property damage insurance will cover 250000 Amount driver will have to pay for injuries None under $250,000 in injuries Amount driver will have to pay for damages -D1011000 Required to pay deductable 8) Alex and Chista live in territory 1. They have a 3 year old, model class 2 car, and a 6 year old, model class 3 car. They want $100/$300 bodily injury coverage, and $100,000 of property damage coverage. They purchased comprehensive coverage with $0 deductible and collision coverage with a $1,000 deductible on their new car, but they decide to forego comprehensive and collision coverage on their older car. They both have excellent driving records during the past 4 years. Their insurance company allows a 6% disocunt for being accident free for 4 years and a 10% discount for insuring multiple vehicles. Complete parts 1 through 5 below 1. What amount should Alex and Christa plan to spend annually on their automobile insurance? Use the tables provided above. 2. Christa remembers seeing an article stating that wrongful death claim settlements have been increasing in their home state, with a median settlement value of nearly $1 million. How much would it cost to increase the auto liability coverage to 250/500/250 for both vehicles? Is increasing their liability coverage a wise decision? How much to increase to 500/1,000/500? 3. The replacement value of their home is $190,000. Their insurance policy contains a coinsurance clause. How much insurance should Alex and Christa carry to meet the coinsurance requirement and how much should they anticipate for an annual insurance premium for that level of coverage if their home is in zone 1, is masonry construction, and they choose a $1,000 deductible? Q: How much wold it cost to fully insure their home? Q: Is increasing their liability a wise decision? 4. Alex is also thinking about purchasing additional life insurance. His employer provides some life insurance coverage, but the financial planner at the seminar they attended suggested they carry insurance to represent an amount 5 to 15 times his annual earnings. Alex earns $80,000 a year and his employer provides $80,000 of life insurance. If Alex decides to purchase enough insurance to cover 10 times his earnings, how much more insurance should he purchase? Q: If he is a 40-year-old male, preferred rate class, and selects 10-year level term insurance, how much should he plan to spend annually on life insurance? 5. Considering the auto insurance with 250/500/250 liability coverage, the property insurance with a fully insured dwelling, and the additional life insurance, how much should Alex and Christa plan to pay each year in premiums? Q: What percentage of Alex's gross pay does the total premium represent? Recall that Alex's gross pay is $80,000. 1. What amount should Alex and Christa plan to spend annually on their automobile insurance? Use the tables provided above. Location Territory 1 1st discounted rate 0.06 2nd discounted rate 0.1 Credit rating Good Vehicle 1 Vehicle 2 Model class 2 3 Age of vehicle 3 years old 6 years old Coverage type 100/300/100 forego Comprehensive deductible 0 0 Collision deductible 1000 0 Insurance premium 425 D143425 Comprehensive premium 447 Collision premium 367 Each premium total SUM(D143:D145)1239 SUM(E143:E145)425 Totals before discount(s) D146+E1461664 1st discounted amount E147*D13399.84 2nd discouned amount E147*D134166.4 Total after discount(s) E147-D148-D1491397.76 2. Christa remembers seeing an article stating that wrongful death claim settlements have been increasing in their home state, with a median settlement value of nearly $1 million. How much would it cost to increase the auto liability coverage to 250/500/250 for both vehicles? Is increasing their liability coverage a wise decision? 8 cont. Coverage type 250/500/250 250/500/250 Insurance premium 460 D156460 Comprehensive premium 447 Collision premium 367 Each premium total SUM(D156:D158)1274 SUM(E156:E158)460 Totals before discount(s) D159+E1591734 Increase in annual premium before discount(s) E160-E14770 1st discounted amount E160*D133104.03999999999999 D14899.84 Discounted amounts from 100/300/100 policy 2nd discouned amount E160*D134173.4 D149166.4 Total after discount(s) E160-E162-E1631456.56 E160-F162-F1631467.76 How much to increase to 500/1,000/500? Coverage type 500/1000/500 500/1000/500 Insurance premium 530 D169530 Comprehensive premium 447 Collision premium 367 Each premium total SUM(D169:D171)1344 SUM(E169:E171)530 Totals before discount(s) D172+E1721874 Increase in annual premium before discount(s) E173-E147210 Q: Is increasing their liability a wise decision? A: Given the significant liability? concern, it would be wise to increase their liability to at least? 250/500/250, if not to? 500/1000/500. 8 cont. 3. The replacement value of their home is $190,000. Their insurance policy contains a coinsurance clause. How much insurance should Alex and Christa carry to meet the coinsurance requirement and how much should they anticipate for an annual insurance premium for that level of coverage if their home is in zone 1, is masonry construction, and they choose a $1,000 deductible? Location Zone 1 Construction style Masonry Deductible 1000 Replacement value 190000 Coinsurance 0.8 Face value required for comp. D186*D185152000 Premium rate 0.27 Rate per $100 of face value 100 Annual premium for level of coverage D187*D188/D189410.4 Q: How much would it cost to fully insure their home? Replacement value 190000 Premium rate 0.27 Rate per $100 of face value 100 Home annual premium for full coverage D194*(D195/D196)513 4. Alex is also thinking about purchasing additional life insurance. His employer provides some life insurance coverage, but the financial planner at the seminar they attended suggested they carry insurance to represent an amount 5 to 15 times his annual earnings. Alex earns $80,000 a year and his employer provides $80,000 of life insurance. If Alex decides to purchase enough insurance to cover 10 times his earnings, how much more insurance should he purchase? 8 cont. Gross annual income 80000 Amount provided by employer 80000 Multiply by (Amt more than income) 10 Policy amount he/she looking for D202*D204800000 Additional insurance needed D205-D203720000 Q: If he is a 40-year-old male, preferred rate class, and selects 10-year level term insurance, how much should he plan to spend annually on life insurance? Policy amount D206720000 1000 Rate on table 19-1 1.1299999999999999 Life insurance annual premium D212*(D210/D211)813.59999999999991 5. Considering the auto insurance with 250/500/250 liability coverage, the property insurance with a fully insured dwelling, and the additional life insurance, how much should Alex and Christa plan to pay each year in premiums? 250/500/250 car insurance F1641467.76 Full coverage home insurance D197513 Additional Life insurance D213813.59999999999991 Total Annual premium for ALL SUM(D217:D219)2794.3599999999997 Q: What percentage of Alex's gross pay does the total premium represent? Recall that Alex's gross pay is $80,000. Gross annual income D202:D20280000 Total annual premium for ALL D2202794.3599999999997 Percent of income going to premiums D225/D2243.4929499999999995E-2 9) Find the sales tax and total sale Find the sales tax and total sale Item marked price Sale tax rate Sales tax Total sales 590.22 5.1610000000000003E-2 D234*C23430.461254200000003 C234+E234620.68125420000001 10) The excise tax rate on gasoline is $0.436 per gallon. In addtion, a federal excise tax of $0.173 per gallon is added to the price. What is the total price of a gallon of gasoline if it costs $1.50 per gallon and is marked up 20%? Cost and tax per gallon Cost/gallon 1.5 Markup 0.2 Federal excise tax per gallon 0.17299999999999999 State excise tax per gallon 0.436 Mark up cost D243*D2420.30000000000000004 Cost w/ markup D242+D2461.8 Cost and taxes D247+D244+D2452.4090000000000003 11) Find the marked price and sales tax Marked price = total price / [1 + sales tax rate (in decimal form)] Find the marked price and sales tax Sales tax = total price - marked price Total price Sale tax rate Marked price Sales tax 376.36 5.2999999999999999E-2 C256/(1+D256)357.41690408357078 C256-E25618.943095916429229 12) You have a receipt for a purchase that shows the total amount of the purchase to be $327.58. The sales tax rate is 6.25%. How much of the $327.58 is the cost of the item and how much is sales tax? Find marked price and sales tax Total purchase amount 327.58 Sales tax rate 6.25E-2 Marked price D267/(1+D268)308.31058823529412 Sales tax D267-D26919.269411764705865 13) Find the assessed value of a store with a market value of $140,000 if the rate for assessed value is 35% of market value. Assessed value Market value 140000 Assessment rate 0.35 Assessed value D278*D27949000 14) Find the property tax for each property. Find Property tax Assessed value 78980 Property tax rate 5.7799999999999997E-2 Property tax D287*D2884565.0439999999999 15) Find the property tax for each property. Find Property tax per $1,000 Assessed value 682700 Tax rate per $1,000 19.87 1000 Property tax (D298/D299)*D29713565.249000000002 16) Calculate the property tax on a store with an assessed value of $150,700 if the tax rate is 51 mills per $1.00 of assessed value. Tax, in mills, per $1 of assessed value = (total estimated budget / total assessed property value) * 1,000 Property tax in mills Assessed value 150700 Mills per $1.00 51 1000 Property tax (D308/D309)*D3077685.7 17) Determine the tax rate for each city or county. An equal number of zeroes in the numerator and denominator can be reduced to facilitate calculator entry. Tax per $1 of assessed value = total estimated budget / total assessed property value Find Property tax per $1 Assessed property value Expenses to be funded by property tax 2014374000 203819011 Tax per amount of assessed value Tax rate 1 D318/C3180.10118230825060291 18) Find the taxable income. Use $4,000 for each exemption. Taxable income Adjusted gross income 49015 Itemized deductions 11897 amount per exemption 4000 Numer of exemptions 2 Taxable income D328-D329-D330*D33129118 19) Steven is single and has a taxable income of $390,309. Use the tax computation worksheet (Table 20-2) to calculate his income tax liability. Total taxed and income after taxes Filing status 1 Taxable income 390309 Multiplication amount 0.33 Subtraction amount 16393.75 Taxed rate amount D341*D342128801.97 Total taxed amount D344-D343112408.22 Total income after taxes D341-D345277900.78000000003 20) Use Table 20-2 to find the federal income tax, where the taxable income is $103,701 and the filing status is "Head of household." Total taxed and income after taxes Filing status Head of household Taxable income 103701 Multiplication amount 0.25 Subtraction amount 5677.5 Taxed rate amount D354*D35525925.25 Total taxed amount D357-D35620247.75 Total income after taxes D354-D35883453.25 21) Vladimir is a head of household and has a taxable income of $26,755. Use the portion of Table 20-1 to find his tax. Income after taxes Filing status Head of household Taxable income 26755 Federal income tax 3359 Total income after taxes D367-D36823396 20) Use Table 20-2 to find the federal income tax, where the taxable income is $103,701 and the filing status is "Head of household." taxed amount and income after taxes Filing status Head of household Taxable income 103701 Multiplication amount 0.25 Subtraction amount 5677.5 Taxed rate amount D377*D37825925.25 Total taxed amount D380-D37920247.75 Total income after taxes D377-D38183453.25 21) Vladimir is a head of household and has a taxable income of $26,755. Use the portion of Table 20-1 to find his tax. Income after taxes Filing status Head of household Taxable income 26755 Federal income tax 3359 Total income after taxes D390-D39123396 THE END Chapter 21: Financial Statements 21-1 Balance Sheet Steps: 1) Preparing a balance sheet 2) Preparing a verticle analysis of a balance sheet 1) Preparing a balance sheet Example 1 Prepare a balance sheet, using Figure 21-1 as a guide, for Move It Fitness for December 31, 2015. The company assets are: cash, $1,973; accounts receivable, $2,118; merchandise inventory, $18,476; equipment, $18,591. The liabilities are: accounts payable, $2,317; wages payable, $684; mortgage note payable, $15,286. The owner's capital is $22,871. Total assets & Total libilities Total assets = total current assets + total plant and equipment Total liabilities = total current liabilities + total long-term liabilities Move It Fitness Balance sheet 42369 Assets 0 Current assets 0 Cash 1973 Accounts receivable 2118 Merchandise inventory 18476 Total current assets SUM(D19:D21)22567 Subtotal Plant and equipment 0 Equipment 18591 Total plant and equipment 0 Total assets SUM(D23:D25)+D2241158 Total Liabilities 0 Current liabilities 0 Accounts payable 2317 Wages payable 684 Total current liabilities SUM(D27:D30)3001 Subtotal Long-term liabilities 0 Mortgage note payable 15286 Subtotal Total long-term liabilities D33+D3215286 Subtotal Total liabilities D34+D3118287 Owner's Equity B. Pierson, capital 22871 Total liabilities and owner's equity D2641158 Total 2) Preparing a verticle analysis of a balance sheet Example 2 Prepare a verticle analysis of the balance sheet for Move It Fitness shown in Figure 21-2. For each item, divide the amount of the item by the total assets. Percentage formula percentage formula ratio & percent of total assets Ratio = liability / total assets Move It Fitness Balance sheet Verticle analysis Percent of total assets = (amount of item / total assets) * 100% 42369 Types Amount Percent From 2015 balance sheet Assets 0 (Table1[[#This Row],[Amount]]/G56)*H560 Cash 1973 Current assets 0 (Table1[[#This Row],[Amount]]/G56)*H560 Accounts receivable 2118 Cash 1973 (G49/G56)*H564.7937217551873267E-2 Merchandise inventory 18476 Accounts receivable 2118 (G50/G56)*H565.1460226444433647E-2 Total assets D2641158 Merchandise inventory 18476 (G51/G56)*H560.44890422275134845 Total current assets SUM(G49:G51)22567 (G52/G56)*H560.54830166674765535 Verticle analysis 1 Plant and equipment 0 (Table1[[#This Row],[Amount]]/G56)*H560 Cash (D48/D51)*D534.7937217551873267E-2 Equipment 18591 (G54/G56)*H560.4516983332523446 Accounts receivable (D49/D51)*D535.1460226444433647E-2 Total plant and equipment 0 (Table1[[#This Row],[Amount]]/G56)*H560 Merchandise inventory (D50/D51)*D530.44890422275134845 Total assets SUM(G53:G55)+G5241158 1 Liabilities 0 (Table1[[#This Row],[Amount]]/G56)*H560 Current liabilities 0 (Table1[[#This Row],[Amount]]/G56)*H560 Accounts payable 2317 (G59/G56)*H565.6295252441809614E-2 Wages payable 684 (G60/G56)*H561.6618883327664122E-2 Total current liabilities SUM(G57:G60)3001 (G61/G56)*H567.291413576947374E-2 Long-term liabilities 0 (Table1[[#This Row],[Amount]]/G56)*H560 Mortgage note payable 15286 (G63/G56)*H560.37139802711502018 Total long-term liabilities G63+G6215286 (G64/G56)*H560.37139802711502018 Total liabilities G64+G6118287 (G65/G56)*H560.44431216288449388 Owner's Equity 0 (Table1[[#This Row],[Amount]]/G56)*H560 B. Pierson, capital 22871 (G67/G56)*H560.55568783711550607 Total liabilities and owner's equity G5641158 1 How to prepare a comparative balance sheet Example 3 Move It Fitness reported the following assets for 2016: cash, $2,184; accounts receivable, $4,308; merchandise inventory, $17,317; equipment, $14,203. Liabilities for the same period are: accounts payable, $1,647; wages payable, $894; mortgage note payable, $12,715. The owner's capital is $22,756. Complete a comparable balance sheet for Move It Fitness for 2015 and 2016. Use the balance sheet in Figure 21-3 as the data for 2015. Complete the balance sheet and vertical analysis for 2016. Then show both sets of data on the same statement Percent (+/-) Percent increase (decrease) = (amount of increase (decrease)/ earlier year's amount) * 100% From 2016 balance sheet Types Amount Percent Assets 0 (Table13[[#This Row],[Amount]]/D90)*E900 Current assets 0 (Table13[[#This Row],[Amount]]/D90)*E900 Cash 2184 (D83/D90)*E905.7455540355677154E-2 Accounts receivable 4308 (D84/D90)*E900.11333263180048406 Merchandise inventory 17317 (D85/D90)*E900.45556666315900241 Total current assets SUM(D83:D85)23809 (D86/D90)*E900.62635483531516367 Plant and equipment 0 (Table13[[#This Row],[Amount]]/D90)*E900 Equipment 14203 (D88/D90)*E900.37364516468483638 Total plant and equipment 0 (Table13[[#This Row],[Amount]]/D90)*E900 Total assets SUM(D87:D89)+D8638012 Table13[[#This Row],[Amount]]/Table13[[#This Row],[Amount]]*100%1 Liabilities 0 (Table13[[#This Row],[Amount]]/D90)*E900 Current liabilities 0 (Table13[[#This Row],[Amount]]/D90)*E900 Accounts payable 1647 (D93/D90)*E904.3328422603388407E-2 Wages payable 894 (D94/D90)*E902.3518888771966746E-2 Total current liabilities SUM(D91:D94)2541 (D95/D90)*E906.6847311375355156E-2 Long-term liabilities 0 (Table13[[#This Row],[Amount]]/D90)*E900 Mortgage note payable 12715 (D97/D90)*E900.33449963169525415 Total long-term liabilities SUM(D96:D97)12715 (D98/D90)*E900.33449963169525415 Total liabilities SUM(D95:D97)15256 (D99/D90)*E900.40134694307060931 Owner's Equity 0 (Table13[[#This Row],[Amount]]/D90)*E900 B. Pierson, capital 22756 (D101/D90)*E900.59865305692939075 Total liabilities and owner's equity D9038012 1 Move It Fitness Comparative Balance sheet 12/31/2015 and 2016 Types 2015 Amount 15' Percent 2016 Amount 16' Percent Assets 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 Table14[[#This Row],[2016 Amount]]/$F$116*$G$1160 Cash 1973 (D109/D116)*E1164.7937217551873267E-2 2184 (F109/F116)*G1165.7455540355677154E-2 Accounts receivable 2118 (D110/D116)*E1165.1460226444433647E-2 4308 (F110/F116)*G1160.11333263180048406 Merchandise inventory 18476 (D111/D116)*E1160.44890422275134845 17317 (F111/F116)*G1160.45556666315900241 Total current assets SUM(D109:D111)22567 (D112/D116)*E1160.54830166674765535 SUM(F109:F111)23809 (F112/F116)*G1160.62635483531516367 Plant and equipment 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 0 Equipment 18591 (D114/D116)*E1160.4516983332523446 14203 (F114/F116)*G1160.37364516468483638 Total plant and equipment 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 0 Total assets SUM(D113:D115)+D11241158 1 SUM(F113:F115)+F11238012 Table14[[#This Row],[2016 Amount]]/Table14[[#This Row],[2016 Amount]]*100%1 Liabilities 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 0 Current liabilities 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 0 Accounts payable 2317 (D119/D116)*E1165.6295252441809614E-2 1647 (F119/F116)*G1164.3328422603388407E-2 Wages payable 684 (D120/D116)*E1161.6618883327664122E-2 894 (F120/F116)*G1162.3518888771966746E-2 Total current liabilities SUM(D117:D120)3001 (D121/D116)*E1167.291413576947374E-2 SUM(F117:F120)2541 (F121/F116)*G1166.6847311375355156E-2 Long-term liabilities 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 0 Mortgage note payable 15286 (D123/D116)*E1160.37139802711502018 12715 (F123/F116)*G1160.33449963169525415 Total long-term liabilities D123+D12215286 (D124/D116)*E1160.37139802711502018 SUM(F122:F123)12715 (F124/F116)*G1160.33449963169525415 Total liabilities D124+D12118287 (D125/D116)*E1160.44431216288449388 SUM(F121:F123)15256 (F125/F116)*G1160.40134694307060931 Owner's Equity 0 (Table14[[#This Row],[2015 Amount]]/D116)*E1160 0 0 B. Pierson, capital 22871 (D127/D116)*E1160.55568783711550607 22756 (F127/F116)*G1160.59865305692939075 Total liabilities and owner's equity D11641158 1 F11638012 1 Preparing a Horizonatl analysis of Balance sheet Move It Fitness Comparative Balance sheet Final analysis 12/31/2015 and 2016 Types 2016 Amount 2015 Amount Amount diff Percent diff Assets 0 0 0 1 Cash 2184 1973 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]211 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$1360.10694374049670552 Accounts receivable 4308 2118 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]2190 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$1361.0339943342776203 Merchandise inventory 17317 18476 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-1159 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-6.2730028144620045E-2 Equipment 14203 18591 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-4388 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-0.23602818568124362 Total assets SUM(D137:D140)38012 SUM(E137:E140)41158 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-3146 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-7.6437144662034107E-2 Liabilities 0 0 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]0 - Accounts payable 1647 2317 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-670 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-0.28916702632714719 Wages payable 894 684 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]210 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$1360.30701754385964913 Mortgage note payable 12715 15286 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-2571 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-0.168193117885647 Total liabilities SUM(D143:D145)15256 SUM(E143:E145)18287 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-3031 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-0.16574615847323235 Owner's Equity 0 0 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]0 - B. Pierson, capital 22756 22871 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-115 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-5.0282016527480218E-3 Total liabilities and owner's equity D14138012 E14141158 Table4[[#This Row],[2016 Amount]]-Table4[[#This Row],[2015 Amount]]-3146 Table4[[#This Row],[Amount diff]]/Table4[[#This Row],[2015 Amount]]*$G$136-7.6437144662034107E-2 21-2 Income Statements Example 1 Complete the portion of the income statement shown for Green Zone Organics using the information given. Gross sales: $25,283; returns and allowances: $492; cost of beginning inventory: $5,384; cost of purchases: $18,923; cost of ending inventory: $5,557; total operating expenses: $3,750 Net sales, COGS, net income Net sales = total sales - sales returns and allowances Cost of goods sold = cost of beginning inventory + cost of purchases - cost of ending inventory Net income = gross profit - operating expenses Green Zone Organics Income Statement For the Month Ending June 30, 2016 Column1 Column2 Column3 Revenue: Total sales (gross sales) 25283 Sales returns and allowances 492 Net sales E166-E16724791 Cost of goods sold: Cost of beginning inventory 5384 Add: Purchases 18923 Less: ending inventory 5557 Cost of goods sold D170+D171-D17218750 Gross profit (loss) E168-E1736041 Expenses: Operating expenses 3750 Total expenses 3750 Net income (loss) E174-D1762291 How to prepare a vertical analysis of an income statement Example 2 Figure 21-7 is an income statement for The 7th Inning. Complete a vertical analysis of the statement. Percent of net sales = (amount of item / net sales) * 100% Percent of Net Sales Figure 21-7 The 7th Inning Income Statement The 7th Inning Income Statement For the Year Ending December 31, 2016 Column1 Amount Percent of Net Sales Revenue: 1 Total sales 846891 Table9[[#This Row],[Amount]]/$D$202*$E$1991.0093378749451765 Sales returns and allowances 7835 Table9[[#This Row],[Amount]]/$D$202*$E$1999.3378749451764834E-3 Net sales 839056 Table9[[#This Row],[Amount]]/$D$202*$E$1991 Cost of goods sold: Table9[[#This Row],[Amount]]/$D$202*$E$1990 Beginning inventory, Jan. 1, 2016 28527 Table9[[#This Row],[Amount]]/$D$202*$E$1993.3998922598730004E-2 Purchases 521054 Table9[[#This Row],[Amount]]/$D$202*$E$1990.62100026696668642 Less: ending inventory, Dec. 31, 2016 33562 Table9[[#This Row],[Amount]]/$D$202*$E$1993.9999713964264605E-2 Cost of goods sold 516019 Table9[[#This Row],[Amount]]/$D$202*$E$1990.61499947560115176 Gross profit from sales 323037 Table9[[#This Row],[Amount]]/$D$202*$E$1990.38500052439884824 Operating expenses: Table9[[#This Row],[Amount]]/$D$202*$E$1990 Salary 64607 Table9[[#This Row],[Amount]]/$D$202*$E$1997.6999628153543984E-2 Insurance 10137 Table9[[#This Row],[Amount]]/$D$202*$E$1991.2081434373867776E-2 Utilities 11712 Table9[[#This Row],[Amount]]/$D$202*$E$1991.3958543887416335E-2 Maintenance 3839 Table9[[#This Row],[Amount]]/$D$202*$E$1994.5753799508018531E-3 Rent 30976 Table9[[#This Row],[Amount]]/$D$202*$E$1993.6917678915352491E-2 Depreciation 5034 Table9[[#This Row],[Amount]]/$D$202*$E$1995.9995995499704429E-3 Total operating expenses 126305 Table9[[#This Row],[Amount]]/$D$202*$E$1990.15053226483095289 Net income 196732 Table9[[#This Row],[Amount]]/$D$202*$E$1990.23446825956789535 Prepare a horizontal analysis of an income statement Example 3 Prepare a horizontal analysis for the Comparative Income Statement for Aspen Lakes Extreme using the yearly amounts in Figure 21-9. For each item, find the amount of increase or decrease by subtracting the smaller amount from the larger amount. For Aspen Lakes Extreme, the later year's amounts are all larger than the earlier year's amounts, so the difference of each amount is recorded as an increase in every case. Percent of net sales (+/-) Percent (+/-) = (amount of (+/-) / earlier year's amount) * 100% 100% 2017 2016 Amount Percent of net sales Net sales 242897 239528 D230-E2303369 F230/E230*$C$2291.4065161484252364E-2 Cost of goods sold 116582 115351 D231-E2311231 F231/E231*$C$2291.0671775710657038E-2 Gross profit 126315 124177 2138 F232/E232*$C$2291.7217359092263462E-2 Operating expenses 38725 37982 743 F233/E233*$C$2291.9561897741035228E-2 Net income 87590 86195 1395 F234/E234*$C$2291.6184233424212543E-2 21-3 Financial Statement Ratios Example 1 Find the current ratio of a business whose current assets are $18,000 and whose current liabilities are $12,000. Current ratio = current assets / current liabilities Current ratio Current Amount Assets 18000 Liabilities 12000 Current ratio D249/D2501.5 * or 1.5 to 1 * Example 2 Find the acid-test ratio if the balance sheet shows the following amounts: Acid-test (quick ratio) = quick current assets / current liabilities Quick current assets = assets that can be readily exchanged for cash, such as marketable securities, accounts recievable, or notes recievable. Acid-test ratio Category Amount Cash 17342 Marketable securities 0 Receivables 10345 Current liabilities 26345 Acid-test ratio (SUM(D260:D262)/D263)1.0509394572025053 Example 3 Based on the income statement in Figure 21-11, find the operating ratio and the gross profit margin ratio for Vincent's Gift Shop. Express results in percent form, rounded to the nearest tenth of a percent. Operating ratio = (cost of goods sold + operating expenses) / net sales Operating ratio & gross profit margin ratio Gross profit margin ratio = (gross profit from sales / net sales) = (net sales - cost of goods sold / net sales) Vincent's Gift Shop Income Statement For the Year Ending Dec. 31, 2015 Category Amount Net sales 173157 Cost of beginning inventory 37376 Cost of purchases 123574 Cost of goods available for sale 160950 Less: Cost of ending inventory 34579 Cost of goods sold 126371 Gross profit 46786 Operating expenses 17643 Net income 29143 Operating ratio ($D$281+$D$283)/$D$2760.83169609083086449 Gross profit margin ratio ($D$276-$D$281)/$D$2760.27019410130690646 Finanical Ratio analysis chart Finanical Ratio Analysis Ratio Value Less than 1 Value = 1 Value More Than 1 Current ratio = current assets / current liabilities Debts greater than assets; potentially major problems Debts & assets are equal Assets greater than debts; current ratio of 2 is desirable Acid-test (quick ratio) = quick current assets / current liabilities Cash flow could be a problem Business is in satisfactory condition Business is in good financial condition Operating ratio = (cost of goods sold + operating expenses) / net sales Desirable Marginal Undesirable Gross profit margin ratio = (gross profit from sales / net sales) = (net sales - cost of goods sold / net sales) 0.25 to 0.40 is industery average Uncommon except for businesses with low turnover and high investment Undesirable Asset turnover ratio = net sales / Average total assets 0.40 to 1.0 is industry average Uncommon Uncommon Debt ratio = total liabilities / total assets 0.05 to 0.75 is industry average Debt ratio is too high Debt ratio is dangerously high Example 4 Arsella would like to apply for a loan to expand Vincent's Gift Shop. The business's current assets are $58,482, its average total assets are $210,580, and total (current) liabilities are $32,289. What you are looking for Other information about the business can be found in Example 3. Current ratio Analyze the financial condition of the business using information given in Table 21-1. Should Arsella plan to expand the business at this time? Acid-test ratio Operating ratio What You Know What you are looking for Solution Plan Gross profit margin ratio Current assets: $58,482 Current ratio Asset turnover ratio Average total assets: $210,580 Acid-test ratio Total debt to total assets ratio Total (current) liabilities: $32,289 Operating ratio Should Arsella expand the business at this time? Ending inventory: $34,579 Gross profit margin ratio Net