Case 22

profileFAtima55
Case_22_Procter_and_Gamble_F1670X.xlsx
Home>Business & Finance homework help>Accounting homework help>Case 22

Title Page

This spreadsheet supports STUDENT analysis of the case “The Procter & Gamble Company: Investment in Crest Whitestrips Advanced Seal” (UVA-F-1670).
This spreadsheet was prepared by Michael J. Schill, Associate Professor of Business Administration. Copyright © 2013 by the University of Virginia Darden School Foundation, Charlottesville, VA. All rights reserved. For customer service inquiries, send an e-mail [email protected]. No part of this publication may be reproduced, stored in a retrieval system, posted to the Internet, or transmitted in any form or by any means—electronic, mechanical, photocopying, recording, or otherwise—without the permission of the Darden School Foundation.
Rev. Apr. 28, 2016

Exh 1

Exhibit 1
THE PROCTER & GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Procter & Gamble Brands
Beauty and Grooming
Always, Anna Sui, Aussie, Braun, Camay, Christina Aguilera Perfumes, Clairol Professional, CoverGirl, Crest, DDF, Dolce & Gabbana Cosmetics, Dolce & Gabbana Fragrances, Dunhill Fragrances, Escada Fragrances, Fekkai, Fusion, Ghost, Gillette, Gucci Fragrances, HUGO BOSS Fragrances, Head & Shoulders, Herbal Essences, Ivory, Lacoste Fragrances, MACH3, Naomi Campbell, Natural Instincts, Nice 'n Easy, Nioxin, Olay, Old Spice, Oral-B, Pantene, Pert, Prestobarba/Blue, Puma, Rejoice, SK-II, Safeguard, Scope, Sebastian Professional, Secret, Tampax,Venus, Vidal Sassoon, Wella
Household Care
Ace, Align, Ariel, Bold, BounceBounty, Cascade, Charmin, Cheer, Comet, Dash, Dawn, Downy, Dreft Laundry, Duracell, Era, Eukanuba, Febreze, Gain, Iams, Joy, Luvs, Metamucil, Mr. Clean, Pampers, Pepto-Bismol, Prilosec OTC, Pringles, Puffs, Swiffer, Tide, Vicks

Exh 2

Exhibit 2
THE PROCTER & GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Value of $1 Invested in P&G Stock or the S&P500 Index beginning January 2001
Open High Low Close Volume Adj Close PG Adj Close S&P500
Jan-97 78.12 79.19 66.75 71.84 7669800 27.26 $1.0 112.03 $1.0
Jan-97 72 77.15 69.85 70.5 7197700 26.75 $1.0 101.34 $0.9
Feb-97 70 71.4 59.25 62.6 7552300 23.75 $0.9 95.66 $0.9
Apr-97 63 63.65 55.96 60.05 8095300 22.93 $0.8 103.84 $0.9
Apr-97 62.8 68.3 62.25 64.24 6772400 24.53 $0.9 103.25 $0.9
May-97 64.23 66.3 61.68 63.8 6186100 24.36 $0.9 100.79 $0.9
Jul-97 64 71.6 63.75 71.02 6176200 27.26 $1.0 99.77 $0.9
Jul-97 71.03 77.28 69.36 74.15 6853700 28.46 $1.0 93.85 $0.8
Sep-97 73.68 76.24 67 72.79 10150000 27.94 $1.0 86.19 $0.8
Sep-97 72.5 74.42 69.73 73.78 6606000 28.47 $1.0 87.31 $0.8
Oct-97 73.8 79 73.64 77.46 5829500 29.9 $1.1 94.12 $0.8
Dec-97 77.45 81.72 74.76 79.13 6734200 30.54 $1.1 94.32 $0.8
Jan-98 78.9 82.12 76.38 81.68 6806400 31.67 $1.2 93.4 $0.8
Jan-98 81.68 87.85 80.75 84.79 7498300 32.88 $1.2 91.72 $0.8
Feb-98 85 90.73 82.62 90.09 7131400 34.94 $1.3 94.78 $0.8
Mar-98 89.35 92.74 87.85 90.26 6794700 35.15 $1.3 89.26 $0.8
Apr-98 90.8 93.73 86.4 89.55 6554900 34.87 $1.3 88.73 $0.8
Jun-98 89.11 94.75 87.94 89.3 9650200 35.04 $1.3 82.18 $0.7
Jun-98 89.34 90.95 74.08 88.99 11221100 35.1 $1.3 75.71 $0.7
Jul-98 88.74 92.9 86.5 88.65 8918000 34.97 $1.3 76.22 $0.7
Sep-98 88.66 93.5 86.36 89.38 8839000 35.25 $1.3 68.23 $0.6
Sep-98 89.5 92.35 84.75 88.45 9792500 35.05 $1.3 73.84 $0.7
Oct-98 87.8 89.06 83.6 84.53 7592400 33.49 $1.2 78.4 $0.7
Dec-98 83.63 88.3 82 85.94 7893300 34.05 $1.2 73.96 $0.7
Jan-99 86.1 88.03 82.37 85.57 7863100 34.07 $1.2 72.14 $0.6
Feb-99 85.56 86.24 81.53 81.86 7115900 32.59 $1.2 71.17 $0.6
Mar-99 82.35 90 79.57 89.05 8697800 35.45 $1.3 71.32 $0.6
Mar-99 89.05 91.45 88.11 89.85 7419800 35.94 $1.3 77.36 $0.7
Apr-99 90.08 92.01 87.6 91.82 6795800 36.72 $1.3 81.6 $0.7
Jun-99 91.83 92.53 89.1 89.18 6782000 35.67 $1.3 82.47 $0.7
Jun-99 89.04 91.09 86.82 87.87 7690100 35.32 $1.3 83.96 $0.7
Jul-99 88 89.66 86.51 87.29 6039200 35.09 $1.3 85.69 $0.8
Sep-99 87.16 93.43 87.12 92.82 7289400 37.31 $1.4 84.75 $0.8
Sep-99 92.82 98.59 92.82 98.29 6451400 39.7 $1.5 89.29 $0.8
Nov-99 97.85 98.48 95 96.24 5521000 38.87 $1.4 90.27 $0.8
Nov-99 96.35 99.93 95.62 99.88 6045000 40.34 $1.5 94.81 $0.8
Jan-00 99.65 101.41 97.78 101.08 7527200 41.02 $1.5 96.68 $0.9
Feb-00 100.81 104 100.81 102.51 6417600 41.6 $1.5 97.99 $0.9
Feb-00 102.58 107.21 101.1 104.88 7636800 42.56 $1.6 96.7 $0.9
Mar-00 104.95 107.39 103.28 105.79 6408800 43.14 $1.6 94.87 $0.8
May-00 105.47 108.85 104.74 107.82 6651000 43.96 $1.6 96.49 $0.9
May-00 107.51 111.59 54.22 54.44 6649300 44.4 $1.6 98.28 $0.9
Jun-00 54.56 55.97 51.5 52.15 5607300 42.72 $1.6 95.11 $0.8
Aug-00 52.76 56.09 52.61 55.97 4451500 45.85 $1.7 95.34 $0.9
Aug-00 55.97 56.95 53.27 54.12 5525800 44.34 $1.6 96.3 $0.9
Sep-00 54.35 54.9 50.53 51.18 6360700 42.13 $1.5 97.54 $0.9
Oct-00 51.25 54.95 50.79 53.48 5410400 44.02 $1.6 101.88 $0.9
Nov-00 53.56 57.4 53.55 55.08 5320100 45.34 $1.7 104.95 $0.9
Jan-01 55.65 57.04 52.14 53.23 9974900 44.01 $1.6 102.6 $0.9
Jan-01 53.45 54.2 51.16 53.09 11118300 43.9 $1.6 104.74 $0.9
Feb-01 53.09 54 51.86 53 6405900 43.82 $1.6 102.83 $0.9
Mar-01 53.1 55.88 52.37 54.15 7824700 45 $1.7 100.9 $0.9
May-01 54.15 56.79 54.01 55.15 6357500 45.84 $1.7 104.15 $0.9
May-01 55.01 56.16 52.44 52.75 6438100 43.84 $1.6 104.31 $0.9
Jun-01 53 55.98 51.91 55.63 8308600 46.47 $1.7 108.3 $1.0
Jul-01 55.8 55.99 53.05 55.48 8959000 46.35 $1.7 107.28 $1.0
Aug-01 55.4 59.46 54.97 59.46 10496500 49.67 $1.8 108.15 $1.0
Oct-01 59.43 59.56 54.62 55.99 11490600 47.01 $1.7 105.59 $0.9
Oct-01 56.5 58.04 54.96 57.19 8261600 48.02 $1.8 110.23 $1.0
Nov-01 57.36 59.7 56.7 57.88 7654500 48.6 $1.8 110.02 $1.0
Jan-02 58.3 59.8 57.29 59.23 8875400 49.97 $1.8 112.66 $1.0
Jan-02 59.1 62.09 59.06 59.93 8451100 50.56 $1.9 113.3 $1.0
Feb-02 60.11 62.5 57 57.63 10049600 48.62 $1.8 115.17 $1.0
Apr-02 57.69 58.73 55.07 58.21 11333200 49.38 $1.8 116.63 $1.0
Apr-02 58.43 58.67 53.75 54.25 12089700 46.02 $1.7 113.12 $1.0
May-02 54.35 57.19 52.75 55.6 10216000 47.17 $1.7 113.41 $1.0
Jul-02 56.02 57.34 55.25 56.2 7294700 47.94 $1.8 113.92 $1.0
Jul-02 56 62.03 55.7 61.9 7668000 52.8 $1.9 116.41 $1.0
Aug-02 61.99 62.85 59.83 61.98 8358500 52.87 $1.9 119.55 $1.1
Oct-02 61.99 64.02 61.5 63.39 8109000 54.35 $2.0 123.32 $1.1
Oct-02 63.2 64.38 62.29 62.79 7114500 53.83 $2.0 125.77 $1.1
Nov-02 63.01 64.73 62.21 64.27 6007500 55.1 $2.0 127.45 $1.1
Jan-03 63.72 66.3 63.11 64.87 9350000 55.88 $2.0 129.37 $1.2
Jan-03 64.9 65.64 61.25 63.49 8815500 54.69 $2.0 126.83 $1.1
Feb-03 62.75 64.12 60.42 63.16 9627700 54.41 $2.0 128.3 $1.1
Apr-03 63.22 64.75 62.61 64.4 10496800 55.78 $2.0 133.98 $1.2
Apr-03 63.21 64.43 61.04 63.55 12691700 55.05 $2.0 138.53 $1.2
May-03 63.55 63.78 60.76 61.19 14234300 53 $1.9 136.5 $1.2
Jul-03 61.35 64.38 60.89 61.86 13464900 53.88 $2.0 132.23 $1.2
Jul-03 61.52 66.75 61.5 65.31 13787100 56.89 $2.1 133.92 $1.2
Sep-03 65.04 70.73 64.89 70.34 11269900 61.27 $2.2 139.11 $1.2
Sep-03 70.44 72.19 67.9 69.52 11169700 60.86 $2.2 141 $1.3
Oct-03 69.22 74.6 68.59 74 12470100 64.78 $2.4 135.54 $1.2
Dec-03 74 75.18 72.52 73.42 9812000 64.27 $2.4 134.01 $1.2
Jan-04 73.59 73.81 62.74 65.42 15670900 57.56 $2.1 125.91 $1.1
Jan-04 66.15 67.92 64.73 66.18 12547200 58.23 $2.1 122.65 $1.1
Mar-04 66.15 70.18 65.53 70.07 13792400 61.65 $2.3 121.56 $1.1
Mar-04 70.41 71.2 69.85 71.14 23180600 62.59 $2.3 127.35 $1.1
PG 35431 35461 35489 35521 35550 35581 35612 35642 35676 35703 35734 35766 35796 35826 35854 35885 35915 35948 35976 36007 36040 36068 36099 36130 36161 36193 36221 36250 36280 36312 36341 36372 36404 36433 36466 36494 36526 36557 36585 36616 36648 36677 36707 36739 36769 36799 36830 36860 36893 36922 36950 36981 37012 37042 37072 37103 37134 37166 37195 37225 37258 37287 37315 37348 37376 37407 37439 37468 37499 37530 37560 37590 37623 37652 37680 37712 37741 37772 37803 37833 37867 37894 37925 37957 37987 38017 38048 38077 1 0.98129126925898746 0.87123991195891415 0.84115920763022733 0.89985326485693318 0.8936170212765957 1 1.0440205429200293 1.0249449743213499 1.0443873807776962 1.0968451944240645 1.1203228173147468 1.1617754952311079 1.2061628760088041 1.2817314746881876 1.2894350696991927 1.2791636096845194 1.2853998532648567 1.2876008804108583 1.2828319882611885 1.2931034482758621 1.2857666911225236 1.2285399853264858 1.2490829053558326 1.2498165810711666 1.1955245781364638 1.3004402054292004 1.3184152604548789 1.3470286133528979 1.3085106382978724 1.2956713132795303 1.2872340425531916 1.3686720469552458 1.4563462949376376 1.4258987527512839 1.4798239178283199 1.5047688921496698 1.5260454878943506 1.5612619222303741 1.5825385179750548 1.6126192223037417 1.6287600880410857 1.5671313279530446 1.681951577402788 1.6265590608950844 1.5454878943506969 1.6148202494497432 1.6632428466617755 1.6144534115920761 1.6104181951577401 1.607483492296405 1.6507703595011005 1.6815847395451211 1.6082171680117388 1.7046955245781363 1.7002934702861334 1.822083639031548 1.7245047688921495 1.7615553925165077 1.7828319882611885 1.8330887747615552 1.8547322083639031 1.7835656639765223 1.8114453411592075 1.6881878209831256 1.7303741746148202 1.7586206896551722 1.936903888481291 1.9394717534849595 1.9937 637564196624 1.974688187820983 2.021276595744681 2.0498899486426998 2.0062362435803371 1.9959647835656638 2.0462215700660309 2.0194424064563461 1.9442406456346293 1.9765223771093177 2.086940572267058 2.247615553925165 2.2325752017608216 2.3763756419662507 2.357666911225238 2.111518708730741 2.136096845194424 2.2615553925165077 2.2960381511371972 S & P500 35431 35461 35489 35521 35550 35581 35612 35642 35676 35703 35734 35766 35796 35826 35854 35885 35915 35948 35976 36007 36040 36068 36099 36130 36161 36193 36221 36250 36280 36312 36341 36372 36404 36433 36466 36494 36526 36557 36585 36616 36648 36677 36707 36739 36769 36799 36830 36860 36893 36922 36950 36981 37012 37042 37072 37103 37134 37166 37195 37225 37258 37287 37315 37348 37376 37407 37439 37468 37499 37530 37560 37590 37623 37652 37680 37712 37741 37772 37803 37833 37867 37894 37925 37957 37987 38017 38048 38077 1 0.9045791305900206 0.85387842542176196 0.9268945818084442 0.92162813532089616 0.89966973132196737 0.89056502722485043 0.83772203873962325 0.76934749620637322 0.77934481835222713 0.84013210747121314 0.84191734356868686 0.83370525752030711 0.8187092743015264 0.8460233865928769 0.7967508703025975 0.79201999464429174 0.73355351245202183 0.67580112469874132 0.68035347674729985 0.60903329465321787 0.65910916718736057 0.69981255020976529 0.66018030884584478 0.6439346603588324 0.63527626528608405 0.6366151923591894 0.690529322502901 0.72837632776934746 0.73614210479335895 0.74944211371953939 0.76488440596268858 0.75649379630456126 0.79701865571721864 0.80576631259484066 0.84629117200749804 0.8629831295188789 0.87467642595733275 0.86316165312862625 0.8468267428367402 0.86128715522627863 0.87726501829866999 0.84896902615370884 0.8510220476658038 0.85959118093367848 0.8706596447380166 0.90939926805320004 0.93680264214942432 0.91582611800410596 0.9349281442470766 0.91787913951620104 0.90065161117557802 0.92966169775952878 0.9310898866375078 0.96670534678211195 0.95760064268499512 0.96536641970900661 0.94251539766134074 0.9839328 7512273497 0.98205837722038736 1.0056234937070427 1.0113362492189593 1.0280282067303401 1.0410604302418995 1.0097295367312327 1.0123181290725698 1.0168704811211282 1.0390966705346782 1.0671248772650184 1.100776577702401 1.1226457198964563 1.137641703115237 1.1547799696509864 1.1321074712130679 1.1452289565295011 1.1959296616977595 1.2365437829152905 1.2184236365259304 1.180308845844863 1.1953940908685172 1.241720967597965 1.258591448719093 1.2098545032580559 1.1961974471123806 1.123895385164688 1.0947960367758636 1.0850665000446309 1.1367490850665001

Exh 3

Exhibit 3
THE PROCTER & GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Crest Whitestrips’ Revenue and After-Tax Profit Since 2001 Launch (in millions)
2001 2002 2003 2004 2005 2006 2007
Revenue $281 $322 $276 $245 $231 $233 $241
After-tax profit $31 $48 $40 $39 $38 $36 $34
Revenue 2001 2002 2003 2004 2005 2006 2007 281 322 276 245 231 233 241 After-tax profit 2001 2002 2003 2004 2005 2006 2007 31 48 40 39 38 36 34

Exh 4

Exhibit 4
THE PROCTER & GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Crest Whitestrips’ Advanced Seal Packaging

Exh 5

Exhibit 5
THE PROCTER & GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Whitening Category History

Exh 6

Exhibit 6
THE PROCTER & GAMBLE COMPANY: CREST WHITESTRIPS ADVANCED SEAL
Crest Whitestrips Products: Gross Profit Comparison
Advanced Seal Premium Product Basic Product
Per unit revenue and costs
Revenue $22 $18 $13
Cost of goods sold expenses $12 $7 $6
Gross profit $10 $11 $7
Note: The cost of goods sold figures exclude depreciation expense.

image1.jpeg

image2.jpeg

Microsoft_Word_Document.docx

image1.png

image3.emf