Accounting case study hw
Master wiki
lost_profits_and_royalties_calculations.xlsCalculations
| Lost Profit and Royalty Calculations | |||||
| 2010 | 2011 | 2012 | 2013 | ||
| UnderCover Mouse Revenues | $ 2,091,626 | $ 1,949,065 | $ 1,274,673 | ||
| SportPet Revenues | $ 1,964,738 | $ 2,324,447 | $ 1,907,690 | ||
| Total Revenues | $ 4,056,363 | $ 4,273,512 | $ 3,182,362 | ||
| Market Share for Undercover Mouse | 52% | 46% | 40% | ||
| Market Share for SportPet | 48% | 54% | 60% | ||
| 2011 | 2012 | 2013 | |||
| Sports Pet Units Sold | 128,548 | 159,701 | 130,161 | ||
| Ideal Revenues | 3,207,279 | 3,984,552 | 3,247,510 | ||
| UCM Actual Revenues | $ 2,091,626 | $ 1,949,065 | $ 1,274,673 | ||
| Lost Sales | 1,115,653.74 | 2,035,486.37 | 1,972,837.56 | 5,123,978 | |
| Incremental Sales | 1,013,098 | 1,060,135 | 764,112 | ||
| Undercover Mouse COGS | 553,621 | 454,397 | 262,734 | ||
| Incremental Profits | $ 459,478 | $ 605,737 | $ 501,378 | $ 1,566,593 | |
| Royalty Amount (remaining portion of infringing sales) | 3,557,384.72 | ||||
| Comments (in no particular order): | |||||
| 2) The COGS percent you use is the marketshare. You need to calculate the COGS as a percent of the revenue for actual sales. | |||||
| 3) You need to figure out what percent of 2010 sales occurred in November and December and use those sales as well. | |||||
| 4) The market shares are calculated correctly, but the Incremental sales calculation doesn't work. You need to remove SportPet from the market and then figure out what percent of sales UCM would have made. | |||||
| You can think about the relative strength of Walmart v. Target, or the geographic sales split (you'll notice that the market shares for each geography don't add to 100%). | |||||
| One of the more difficult aspects of this case is that the evidence indicates that, if SportPet had not been in the market, UCM would not have made ALL of SportPet's sales. | |||||
| They were selling through different channels and had different geographic strengths. | |||||
| Thus, you're going to have to figure out some way to allocate SportPet's revenues as between UCM in the but-for world and other companies. |
UnderCover Income
| UnderCover Mouse | |||||||||||||
| Income Statement | |||||||||||||
| 2009 | 2010 | 2011 | 2012 | 2013 | 2009 | 2010 | 2011 | 2012 | 2013 | ||||
| Units | 27,200 | 97,017 | 126,696 | 115,744 | 75,788 | Net Income | $ (91,364) | $ 220,604 | $ 216,280 | $ 113,584 | $ 39,318 | ||
| Wholesale | 2,745 | 42,394 | 51,010 | 44,705 | 28,220 | Discount Rate | 1 | 0.9411764706 | 0.8858131488 | 0.833706493 | 0.7846649346 | ||
| Direct | 24,455 | 54,623 | 75,687 | 71,039 | 47,568 | ||||||||
| Revenues | $ 599,809 | $ 1,610,950 | $ 2,091,626 | $ 1,949,065 | $ 1,274,673 | ||||||||
| Wholesale | $ 32,133 | $ 356,025 | $ 426,243 | $ 412,685 | $ 254,311 | ||||||||
| Direct | $ 561,235 | $ 1,245,234 | $ 1,654,238 | $ 1,523,564 | $ 1,005,623 | ||||||||
| Repair | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | ||||||||
| Freight | $ 6,441 | $ 9,691 | $ 11,145 | $ 12,816 | $ 14,739 | ||||||||
| $ - 0 | |||||||||||||
| COGS | $ 294,192 | $ 811,790 | $ 1,073,656 | $ 996,310 | $ 655,945 | ||||||||
| Manufacture | $ 50,047 | $ 178,511 | $ 233,121 | $ 212,969 | $ 139,449 | ||||||||
| Freight | $ 112,187 | $ 278,870 | $ 380,377 | $ 354,546 | $ 236,067 | ||||||||
| Labor | $ 131,958 | $ 354,409 | $ 460,158 | $ 428,794 | $ 280,428 | ||||||||
| Gross Margin | $ 305,616 | $ 799,160 | $ 1,017,970 | $ 952,755 | $ 618,728 | ||||||||
| SG&A | $ 375,326 | $ 443,296 | $ 668,004 | $ 759,977 | $ 540,385 | ||||||||
| Advertising | $ 251,235 | $ 158,782 | $ 351,254 | $ 412,584 | $ 178,542 | ||||||||
| Bank | $ 192 | $ 212 | $ 212 | $ 212 | $ 212 | ||||||||
| Dues | $ 160 | $ 160 | $ 160 | $ 160 | $ 160 | ||||||||
| Equipment Rental | $ - 0 | $ 227 | $ 227 | $ 227 | $ 227 | ||||||||
| Freight - Other | $ 455 | $ 242 | $ 300 | $ 300 | $ 300 | ||||||||
| Insurance | $ 16,036 | $ 27,491 | $ 28,041 | $ 28,602 | $ 29,174 | ||||||||
| Office Supplies | $ 1,871 | $ 1,230 | $ 3,023 | $ 1,488 | $ 973 | ||||||||
| Outside Svcs | $ 4,537 | $ 3,434 | $ 4,537 | $ 4,537 | $ 4,537 | ||||||||
| Postage | $ 1,304 | $ 1,287 | $ 1,670 | $ 1,557 | $ 1,018 | ||||||||
| Rent | $ 14,584 | $ 14,584 | $ 14,584 | $ 14,584 | $ 14,584 | ||||||||
| Repair | $ 676 | $ 451 | $ 585 | $ 546 | $ 357 | ||||||||
| R&D | $ 254 | $ 1,045 | $ 3,055 | $ 2,843 | $ 3,820 | ||||||||
| Ship Materials | $ 1,410 | $ 3,601 | $ 4,675 | $ 4,356 | $ 2,849 | ||||||||
| Shop Supplies | $ 4,782 | $ 4,973 | $ 6,457 | $ 6,017 | $ 3,935 | ||||||||
| Small Tools | $ 804 | $ 1,690 | $ 2,194 | $ 2,044 | $ 1,337 | ||||||||
| Property Tax | $ 415 | $ 393 | $ 393 | $ 393 | $ 393 | ||||||||
| Telephone | $ 3,000 | $ 3,017 | $ 3,917 | $ 3,650 | $ 2,387 | ||||||||
| Travel | $ 1,637 | $ 5,809 | $ 12,874 | $ 34,921 | $ 45,129 | ||||||||
| Entertainment | $ 208 | $ 833 | $ 1,081 | $ 1,007 | $ 659 | ||||||||
| Utilities | $ 5,122 | $ 5,277 | $ 6,852 | $ 6,385 | $ 4,176 | ||||||||
| Vehicles | $ 1,250 | $ 2,568 | $ 5,621 | $ 6,458 | $ 7,156 | ||||||||
| Owner's Salary | $ 65,394 | $ 205,991 | $ 216,291 | $ 227,105 | $ 238,460 | ||||||||
| EBITDA | $ (69,709) | $ 355,864 | $ 349,966 | $ 192,779 | $ 78,343 | ||||||||
| Interest | $ (18,750) | $ (17,344) | $ (15,850) | $ (14,263) | $ (12,577) | ||||||||
| Rate | 6.25% | ||||||||||||
| Term | 10 | ||||||||||||
| Depreciation | ($2,905) | $ (4,271) | $ (6,419) | $ (6,419) | $ (6,193) | ||||||||
| Taxes1 | $ - 0 | $ (113,644) | $ (111,417) | $ (58,513) | $ (20,255) | ||||||||
| Net Income | $ (91,364) | $ 220,604 | $ 216,280 | $ 113,584 | $ 39,318 |
&A
UnderCover Geography
| UnderCover Mouse | |||||
| Geographic Breakdown of Revenues | |||||
| 2009 | 2010 | 2011 | 2012 | 2013 | |
| Pacific | $ 389,876 | $ 905,496 | $ 1,012,678 | $ 768,056 | $ 428,284 |
| Mountain | $ 149,952 | $ 288,481 | $ 357,215 | $ 324,314 | $ 185,790 |
| Central | $ 59,981 | $ 252,336 | $ 356,026 | $ 450,489 | $ 378,187 |
| East | $ - 0 | $ 164,637 | $ 365,707 | $ 406,207 | $ 282,411 |
| 2008 | 2009 | 2010 | 2011 | 2012 | |
| Pacific | 65% | 56% | 48% | 39% | 34% |
| Mountain | 25% | 18% | 17% | 17% | 15% |
| Central | 10% | 16% | 17% | 23% | 30% |
| East | 0% | 10% | 17% | 21% | 22% |
UnderCover Balance
| UnderCover Mouse | |||||
| Balance Sheet | |||||
| 2009 | 2010 | 2011 | 2012 | 2013 | |
| ASSETS | $499,781 | $715,587 | $960,949 | $1,021,445 | $1,018,277 |
| Current Assets | $145,475 | $382,993 | $624,547 | $761,645 | $796,556 |
| Cash & Short Term | $58,891 | $241,487 | $470,364 | $561,457 | $610,013 |
| Checking | $36,812 | $27,669 | $31,233 | $34,267 | $22,143 |
| Petty Cash | $687 | $314 | $300 | $300 | $300 |
| Schwab | $21,392 | $213,504 | $438,831 | $526,890 | $587,570 |
| A/R | $39,192 | $70,666 | $83,342 | $108,210 | $100,834 |
| Other Current | $47,392 | $70,841 | $70,841 | $91,978 | $85,709 |
| Finished Goods | $8,731 | $8,652 | $8,652 | $11,234 | $10,468 |
| Raw Materials | $33,194 | $51,727 | $51,727 | $67,161 | $62,583 |
| WIP | $3,967 | $10,462 | $10,462 | $13,583 | $12,657 |
| Undeposited Funds | $1,500 | $0 | $0 | $0 | $0 |
| Fixed Assets | $354,306 | $332,594 | $336,402 | $259,800 | $221,721 |
| Vehicle | $10,780 | $12,241 | $16,049 | $12,551 | $9,053 |
| Cost | $24,351 | $24,351 | $24,351 | $24,351 | $24,351 |
| Accum. Depr. | ($1,306) | ($4,804) | ($8,302) | ($11,800) | ($15,298) |
| Personal Portion | ($12,266) | ($7,306) | $0 | $0 | $0 |
| Warehouse | $28,526 | $27,753 | $27,753 | $25,605 | $26,604 |
| Cost | $30,125 | $30,125 | $30,125 | $33,819 | $37,514 |
| Accum. Depr. | ($1,599) | ($2,372) | ($5,293) | ($8,215) | ($10,910) |
| Equipment | $315,000 | $292,600 | $292,600 | $221,645 | $186,064 |
| Cost | $350,000 | $364,000 | $365,274 | $366,191 | $367,345 |
| Accum. Depr. | ($35,000) | ($71,400) | ($107,927) | ($144,547) | ($181,281) |
| Goodwill | $0 | $0 | $0 | $0 | $0 |
| LIABILITIES & EQUITY | $499,781 | $715,587 | $960,949 | $1,021,445 | $1,018,277 |
| Liabilities | $318,509 | $322,307 | $302,693 | $285,899 | $255,443 |
| A/P | $15,951 | $38,204 | $42,156 | $49,604 | $46,223 |
| Credit Cards | $420 | $111 | $111 | $144 | $134 |
| Payroll Liabilities | $1,927 | $3,354 | $3,689 | $3,874 | $4,067 |
| Fed. Withholding | $483 | $990 | $1,089 | $1,143 | $1,201 |
| SS -- Employee | $331 | $544 | $599 | $628 | $660 |
| SS - Employer | $331 | $544 | $599 | $628 | $660 |
| ETT | ($6) | ($6) | ($6) | ($6) | ($7) |
| Other | $787 | $1,281 | $1,409 | $1,480 | $1,553 |
| Sales Tax Payable | $212 | $3,132 | $3,132 | $4,067 | $3,790 |
| Bank Debt | $300,000 | $277,505 | $253,605 | $228,211 | $201,229 |
| Equity | $181,271 | $393,281 | $658,256 | $735,547 | $762,834 |
&A
SportsPet Income
| SportPet | |||||
| Income Statement | |||||
| 2009 | 2010 | 2011 | 2012 | 2013 | |
| Units | 3,894 | 56,352 | 128,548 | 159,701 | 130,161 |
| Wholesale CMIYC | 3,873 | 56,289 | 128,422 | 159,623 | 129,868 |
| Direct CMIYC | 21 | 62 | 126 | 78 | 293 |
| Revenues | $ 694,211 | $ 906,439 | $ 1,964,738 | $ 2,324,447 | $ 1,907,690 |
| Wholesale CMIYC | $ 32,133 | $ 541,238 | $ 1,248,537 | $ 1,532,544 | $ 1,245,873 |
| Direct CMIYC | $ 421 | $ 1,245 | $ 2,513 | $ 1,564 | $ 5,843 |
| Other Products | $ 655,216 | $ 354,265 | $ 702,543 | $ 777,523 | $ 641,235 |
| Freight | $ 6,441 | $ 9,691 | $ 11,145 | $ 12,816 | $ 14,739 |
| COGS | $ 248,344 | $ 389,131 | $ 858,713 | $ 1,030,296 | $ 851,021 |
| Manufacture | $ 92,343 | $ 149,742 | $ 327,859 | $ 394,929 | $ 322,856 |
| Freight | $ 3,116 | $ 44,186 | $ 100,736 | $ 124,859 | $ 102,615 |
| Sales Commission | $ 66,108 | $ 81,898 | $ 184,526 | $ 219,953 | $ 187,089 |
| Labor | $ 86,776 | $ 113,305 | $ 245,592 | $ 290,556 | $ 238,461 |
| Gross Margin | $ 445,867 | $ 517,308 | $ 1,106,024 | $ 1,294,151 | $ 1,056,668 |
| SG&A | $ 289,488 | $ 330,059 | $ 390,764 | $ 424,461 | $ 504,937 |
| Advertising | $ 15,435 | $ 25,412 | $ 34,512 | $ 24,589 | $ 90,425 |
| Bank | $ 192 | $ 212 | $ 212 | $ 212 | $ 212 |
| Dues | $ 160 | $ 160 | $ 160 | $ 160 | $ 160 |
| Equipment Rental | $ - 0 | $ 227 | $ 227 | $ 227 | $ 227 |
| Freight - Other | $ 455 | $ 242 | $ 300 | $ 300 | $ 300 |
| Insurance | $ 16,036 | $ 27,490.96 | $ 28,041 | $ 28,602 | $ 29,174 |
| Office Supplies | $ 1,871 | $ 1,230 | $ 3,023 | $ 3,154 | $ 2,589 |
| Outside Svcs | $ 4,537 | $ 3,434 | $ 4,537 | $ 4,537 | $ 4,537 |
| Postage | $ 1,304 | $ 1,287 | $ 2,789 | $ 3,299 | $ 2,708 |
| Rent | $ 14,584 | $ 14,584 | $ 14,584 | $ 14,584 | $ 14,584 |
| Repair | $ 676 | $ 451 | $ 977 | $ 1,156 | $ 949 |
| R&D | $ 254 | $ 1,045 | $ 3,055 | $ 2,843 | $ 3,820 |
| Ship Materials | $ 1,410 | $ 3,601 | $ 7,805 | $ 9,234 | $ 7,578 |
| Shop Supplies | $ 4,782 | $ 4,973 | $ 10,779 | $ 12,752 | $ 10,466 |
| Small Tools | $ 804 | $ 1,690 | $ 3,662 | $ 4,333 | $ 3,556 |
| Property Tax | $ 415 | $ 393 | $ 393 | $ 393 | $ 393 |
| Telephone | $ 3,000 | $ 3,017 | $ 6,539 | $ 7,737 | $ 6,350 |
| Travel | $ 1,637 | $ 5,809 | $ 12,874 | $ 34,921 | $ 45,129 |
| Entertainment | $ 208 | $ 833 | $ 1,805 | $ 2,135 | $ 1,752 |
| Utilities | $ 5,122 | $ 5,277 | $ 11,438 | $ 13,533 | $ 11,106 |
| Vehicles | $ 1,250 | $ 2,568 | $ 5,621 | $ 6,458 | $ 7,156 |
| Personnel | $ 215,356 | $ 226,124 | $ 237,430 | $ 249,301 | $ 261,767 |
| EBITDA | $ 156,380 | $ 187,249 | $ 715,260 | $ 869,690 | $ 551,731 |
| Interest | $ (18,750) | $ (17,344) | $ (15,850) | $ (14,263) | $ (12,577) |
| Rate | 6.25% | ||||
| Term | 10 | ||||
| Depreciation | ($2,905) | $ (4,271) | $ (6,419) | $ (6,419) | $ (6,193) |
| Taxes1 | $ (45,806) | $ (56,316) | $ (235,617) | $ (288,663) | $ (181,207) |
| Net Income | $ 88,918 | $ 109,319 | $ 457,374 | $ 560,345 | $ 351,754 |
SportsPet Geography
| SportPet | |||||
| Geographic Breakdown of Revenues | |||||
| 2009 | |||||
| 2009 | 2010 | 2011 | 2012 | 2013 | |
| Pacific | $ 151,043 | $ 209,979 | $ 429,671 | $ 520,230 | $ 434,602 |
| Mountain | $ 336,282 | $ 470,503 | $ 1,076,762 | $ 1,374,400 | $ 1,150,611 |
| Central | $ 88,042 | $ 103,704 | $ 209,581 | $ 230,382 | $ 200,476 |
| East | $ 118,844 | $ 122,252 | $ 248,723 | $ 199,435 | $ 122,001 |
| 2008 | 2009 | 2010 | 2011 | 2012 | |
| Pacific | 22% | 23% | 22% | 22% | 23% |
| Mountain | 48% | 52% | 55% | 59% | 60% |
| Central | 13% | 11% | 11% | 10% | 11% |
| East | 17% | 13% | 13% | 9% | 6% |
| 215356 | 226123.8 | 237429.99 | 249301.4895 | 261766.563975 |
SportsPet Balance
| SportPet | |||||
| Balance Sheet | |||||
| 2009 | 2010 | 2011 | 2012 | 2013 | |
| ASSETS | $1,109,817 | ($654,460) | $530,424 | $984,799 | $1,781,342 |
| Wholesale CMIYC | $877,678 | ($870,270) | $314,616 | $801,821 | $1,613,800 |
| Direct CMIYC | $183,270 | ($1,295,201) | ($471,269) | ($156,349) | $758,845 |
| Checking | $36,812 | $27,669 | $31,233 | $34,267 | $22,143 |
| Petty Cash | $687 | $314 | $300 | $300 | $300 |
| Wholesale CMIYC | $145,771 | ($1,323,184) | ($502,802) | ($190,916) | $736,402 |
| Direct CMIYC | $39,192 | $70,666 | $83,342 | $180,647 | $213,720 |
| Other Products | $655,216 | $354,265 | $702,543 | $777,523 | $641,235 |
| Finished Goods | $8,731 | $8,652 | $8,652 | $18,754 | $22,188 |
| Raw Materials | $33,194 | $51,727 | $51,727 | $112,119 | $132,646 |
| WIP | $3,967 | $10,462 | $10,462 | $22,676 | $26,828 |
| Undeposited Funds | $2,127,242 | ($1,158,152) | $1,067,311 | $1,913,109 | $3,361,030 |
| Fixed Assets | $232,140 | $215,810 | $215,808 | $182,979 | $167,543 |
| Sales Commission | $ 67 | $ 29 | $ 29 | $ 30 | $ 30 |
| Vehicle | $86 | $39 | $38 | $38 | $38 |
| Cost | $24,351 | $24,351 | $24,351 | $24,351 | $24,351 |
| Accum. Depr. | ($1,306) | ($4,804) | ($8,302) | ($11,800) | ($15,298) |
| Personal Portion | ($12,266) | ($7,306) | $0 | $0 | $0 |
| Warehouse | $28,526 | $27,753 | $27,753 | $25,605 | $26,604 |
| Cost | $30,125 | $30,125 | $30,125 | $33,819 | $37,514 |
| Accum. Depr. | ($1,599) | ($2,372) | ($5,293) | ($8,215) | ($10,910) |
| Equipment | $203,528 | $188,018 | $188,018 | $157,337 | $140,902 |
| Cost | $226,142 | $235,124 | $247,867 | $257,037 | $268,579 |
| Accum. Depr. | ($22,614) | ($47,106) | ($72,926) | ($99,700) | ($127,677) |
| Goodwill | $0 | $0 | $0 | $0 | $0 |
| LIABILITIES & EQUITY | $1,109,817 | ($654,460) | $530,424 | $984,799 | $1,781,342 |
| Liabilities | $318,509 | $322,307 | $302,693 | $321,923 | $311,583 |
| A/P | $15,951 | $38,204 | $42,156 | $82,809 | $97,970 |
| Credit Cards | $420 | $111 | $111 | $240 | $284 |
| Payroll Liabilities | $1,927 | $3,354 | $3,689 | $3,874 | $4,067 |
| Fed. Withholding | $483 | $990 | $1,089 | $1,143 | $1,201 |
| SS -- Employee | $331 | $544 | $599 | $628 | $660 |
| SS - Employer | $331 | $544 | $599 | $628 | $660 |
| ETT | ($6) | ($6) | ($6) | ($6) | ($7) |
| Other | $787 | $1,281 | $1,409 | $1,480 | $1,553 |
| Sales Tax Payable | $212 | $3,132 | $3,132 | $6,789 | $8,032 |
| Bank Debt | $300,000 | $277,505 | $253,605 | $228,211 | $201,229 |
| Equity | $791,308 | ($976,767) | $227,731 | $662,876 | $1,469,759 |
| $ 215,356.00 | $ 226,123.80 | $ 237,429.99 | $ 249,301.49 | $ 261,766.56 |
