Database includes 2 years worth of actual data and one year of budget data for a retail business with 2 locations. In 2017, there was just one location, North. In January 2018, a new location, South, opened. Budget data is based on best estimates of business activity. Actual data included: Some maintenance costs that were expensed in Q4 Other Expenses North location were unexpected and not budgeted for. Sales Discounts can be impacted by competition (more competition, more discounts must be offered). In retail, industry averages include the following: COGS Percentage of Total Sales 33% Wages + Benefits Rent + Utilities 25% 10% Requirements: 1. Create an Income Statement by location and in total for both budget and actual data (row format) for both 2017 and 2018. 2. Use common size and specific ratios to analysize year over year results and to compare to budget data. 3. Create a 2019 Income Statement budget by location and in total including the following: 1. provide reasons for values 2. Budget for improved results and explain how the improvement will be achieved. In particular, the Net Profit for the South location must be improved. Use ratios, common size analysis and trend analysis to support your budget estimates and improvements. Act/Bud Month Actual January July Year Location 2017 North Actual February 2017 North Actual March 2017 North Actual April 2017 North Actual May 2017 North Actual June 2017 North Actual Sales 84,053 123,803 Sales Discounts Net Sales COGS Gross Margin Wages Fringe benefits Rent Depreciation Utilities Freight 4,203 79,850 26,897 52,953 20,173 2,219 7,353 3,748 3,645 2,959 Phone & Internet Packaging 652 Bank and Moneris Marketing Other Exp Total Expenses Op Income Interest Exp Net Income 1,883 3,194 3,268 4,791 53,884 (931) 3,579 (4,510) 3,714 120,089 37,141 82,948 28,475 3,986 7,124 3,593 3,460 5,200 787 2,600 4,804 3,684 3,603 67,315 15,633 3,907 11,726 113,048 2,261 110,787 35,045 75,742 23,740 3,561 7,283 2,647 3,495 4,556 666 2,103 3,324 3,046 3,324 57,744 17,998 3,686 14,312 83,854 1,677 82,177 29,349 52,828 17,609 1,761 7,496 2,944 3,863 2,935 684 1,761 1,644 4,926 4,931 50,553 2,275 3,712 (1,437) 122,223 2,444 119,779 40,334 79,445 26,889 2,958 7,348 3,284 4,598 5,647 715 2,823 5,989 4,673 5,989 70,913 8,532 3,797 4,735 116,921 2,338 114,583 40,922 73,660 23,384 2,338 7,330 3,560 3,576 4,911 744 2,865 3,437 4,431 6,875 63,451 10,209 3,952 6,257 2017 North 115,507 4,620 110,887 39,272 71,614 23,101 2,310 7,192 3,410 3,212 5,105 631 2,749 3,327 3,732 6,653 61,423 10,192 4,179 6,013 Actual August Actual Actual Actual Actual 2017 North September 2017 North October 2017 North November 2017 North December 2017 North 80,893 3,236 77,657 24,268 53,389 16,988 1,699 7,007 3,457 3,775 3,398 652 1,456 1,553 3,647 3,106 46,737 6,652 3,537 3,115 118,759 2,375 116,384 35,628 80,756 23,752 3,088 7,054 3,240 3,146 4,988 784 1,425 4,655 3,719 5,819 61,670 19,086 4,151 14,935 99,574 2,987 96,587 33,855 62,732 23,898 2,868 7,229 2,618 4,290 3,724 758 1,354 1,932 4,809 3,863 57,343 5,389 4,310 1,079 120,121 2,402 117,719 42,042 75,676 24,024 2,402 7,082 2,866 4,600 5,886 715 2,943 4,709 3,625 4,709 63,561 12,115 4,216 7,899 Actual January April 2018 North Actual February 2018 North Actual March Actual 116,059 1,294,815 107,304 5,803 38,061 2,146 110,256 1,256,754 421,892 105,158 37,139 73,117 27,854 4,178 7,041 3,653 4,710 5,571 711 2,228 2,205 3,255 5,513 66,919 6,198 3,585 2,613 834,862 279,887 33,369 86,539 39,020 46,370 54,878 8,499 26,190 40,772 46,815 59,175 721,514 113,348 46,611 66,737 37,556 67,602 26,826 2,683 7,038 3,998 3,522 3,756 781 1,502 3,155 3,468 6,309 63,038 4,564 4,146 418 104,624 5,231 99,393 36,618 62,774 25,110 2,511 7,244 3,040 3,875 4,760 618 1,831 1,988 3,411 4,970 59,358 3,417 3,573 (156) 2018 North 124,431 2,489 121,942 38,574 83,369 26,131 3,136 7,283 3,383 4,158 5,786 709 2,314 4,878 4,910 7,317 70,004 13,365 3,736 9,629 2018 North 118,173 3,545 114,628 37,815 76,812 25,998 2,860 7,052 2,558 4,456 5,672 723 1,513 2,293 4,331 3,439 60,894 15,918 4,499 11,419 Actual May Actual June Actual July Actual 2018 North 120,104 6,005 114,099 39,634 74,464 24,021 3,363 7,055 3,175 3,643 3,963 634 1,982 2,282 4,895 5,705 60,718 13,747 4,376 9,371 2018 North 111,272 5,564 105,708 36,720 68,989 23,367 2,337 7,029 2,520 4,568 4,406 660 1,469 2,114 4,738 6,343 59,551 9,438 3,911 5,527 2018 North 105,818 2,116 103,702 37,036 66,665 22,222 3,333 7,095 3,750 3,265 3,704 735 2,222 3,111 4,592 3,111 57,140 9,525 4,269 5,256 August 2018 North 111,073 3,332 107,741 37,765 69,976 23,325 2,333 7,417 3,310 4,682 4,532 641 1,888 5,387 3,300 3,232 60,047 9,929 4,225 5,704 Actual Actual Actual Actual September 2018 North October 2018 North November 2018 North December 2018 North 84,550 4,228 80,323 29,593 50,730 16,910 2,198 7,040 3,420 3,628 4,439 611 1,480 4,016 4,293 4,016 52,051 (1,321) 3,562 (4,883) 81,382 3,255 78,127 27,670 50,457 17,090 2,564 7,288 3,397 3,789 3,320 726 1,660 3,906 3,143 3,125 50,009 448 4,393 (3,945) 123,698 4,948 118,750 37,109 81,641 30,925 4,329 7,425 3,596 3,237 4,824 758 2,227 2,375 3,825 5,938 69,458 12,182 4,397 7,785 Budget January 2018 North 93,329 1,285,758 87,837 4,666 88,663 32,665 55,997 21,466 2,791 7,060 3,087 3,703 4,573 755 1,633 2,660 3,859 2,660 54,246 1,751 4,443 (2,692) 47,526 1,757 1,238,232 428,756 809,477 283,390 34,436 86,026 39,234 46,526 53,736 8,351 21,721 38,164 48,765 56,164 716,513 92,963 49,530 43,433 86,080 27,229 58,851 20,203 2,020 6,947 3,607 5,453 2,723 527 817 1,722 3,261 2,582 49,862 8,989 3,201 5,788 Budget February 2018 North 99,991 4,000 95,991 33,997 61,994 23,998 2,880 6,142 3,199 5,840 2,720 581 1,020 1,920 3,272 4,800 56,371 5,624 3,744 1,880 Budget March 2018 North 119,495 2,390 117,105 35,849 81,257 27,484 2,748 6,676 3,143 5,516 2,868 569 1,792 4,684 3,730 3,513 62,724 18,533 3,447 15,086 Budget April 2018 North 97,043 3,882 93,161 29,113 64,048 23,290 2,795 6,621 3,326 5,951 2,329 503 873 2,795 3,812 3,726 56,022 8,027 3,472 4,555 Budget May 2018 North 81,409 3,256 78,153 24,423 53,730 19,538 1,954 6,543 3,115 4,190 1,954 576 1,465 1,563 3,226 3,126 47,250 6,480 4,988 1,492 Budget June 2018 North 92,280 2,768 89,512 28,607 60,905 21,224 2,335 6,316 3,273 5,032 3,147 556 1,144 1,790 3,999 3,580 52,397 8,508 3,914 4,594 32 Budget May 81,409 3,256 78,153 24,423 53,730 19,538 1,954 6,543 3,115 4,190 1,954 5/6 1,465 1,563 3,226 3,126 6,480 4,988 1,492 33 Budget June 2018 North 92,280 2,768 89,512 28,607 60,905 21,224 2,335 6,316 3,273 5,032 3,147 556 1,144 1,790 3,999 3,580 52,397 8,508 3,914 4,594 84 Budget July 2018 North 93,934 3,757 90,177 29,120 61,057 22,544 2,480 6,752 3,846 5,876 3,494 590 1,165 4,509 3,631 2,705 57,592 3,465 4,734 (1,269) 35 Budget 36 Budget August September 2018 2018 North 90,398 3,616 86,782 27,119 59,663 19,888 2,188 6,388 2,933 4,466 2,441 559 1,627 4,339 3,951 2,603 51,383 8,280 4,659 3,621 North 84,062 3,362 80,700 26,059 54,640 21,016 2,522 6,704 2,808 4,039 3,388 526 782 3,228 3,358 2,421 50,791 3,849 3,139 710 38 Budget 39 Budget 37 Budget October 2018 North November 2018 North December 2018 North 85,968 2,579 83,389 28,369 55,020 21,492 2,579 6,627 3,699 4,848 3,404 518 1,135 4,169 3,069 2,502 54,042 977 4,966 (3,989) 111,476 3,344 108,132 33,443 74,689 24,525 2,698 6,483 3,332 4,145 3,010 527 1,003 5,407 3,908 5,407 60,444 14,245 3,176 11,069 120,223 3,607 116,616 37,269 79,347 26,449 2,909 6,948 3,567 5,073 2,982 595 1,863 3,498 3,171 5,831 62,887 16,460 4,230 12,230 40 1,164,116 38,319 1,125,797 360,597 765,201 271,650 30,107 79,147 39,848 60,429 34,459 6,627 14,687 39,624 42,388 42,797 661,764 103,437 47,670 55,767 11 Actual January 2018 South 85,364 2,561 82,803 29,024 53,779 21,341 2,988 7,490 3,129 3,753 4,063 789 2,032 2,484 3,257 4,140 55,466 (1,687) 3,853 (5,540) 12 Actual February 2018 South 108,915 5,446 103,469 34,853 68,616 22,872 2,287 7,179 3,859 4,913 4,879 791 2,440 5,173 3,575 5,173 63,142 5,474 3,797 1,677 13 Actual 14 Actual 15 Actual March April 2018 South 107,863 2,157 105,706 36,673 69,032 26,966 3,506 7,475 2,549 4,023 5,134 636 1,467 2,114 4,553 5,285 63,708 5,324 3,780 1,544 2018 South 108,415 5,421 102,994 35,777 67,217 23,851 3,578 7,023 2,885 4,704 4,293 617 1,789 5,150 3,812 6,180 63,881 3,336 3,591 (255) May 2018 South 110,726 3,322 107,404 36,540 70,865 22,145 2,215 7,314 3,221 4,176 4,385 782 2,192 3,222 3,472 5,370 58,494 12,370 4,460 7,910 16 Actual June 2018 South 81,659 3,266 78,393 26,947 51,445 18,782 2,817 7,398 2,741 3,519 2,695 775 1,886 2,352 4,741 4,704 52,409 (964) 3,606 (4,570) 17 Actual July 2018 South 91,636 3,665 87,971 28,407 59,563 22,909 2,520 7,275 3,447 3,981 3,409 745 1,420 1,759 3,291 3,519 54,275 5,288 4,472 816 18 Actual August 2018 South 120,439 6,022 114,417 36,132 78,285 27,701 3,878 7,410 3,471 4,073 3,974 615 2,168 2,288 3,012 6,865 65,456 12,829 3,919 8,910 19 Actual September 2018 South 95,509 4,775 90,734 33,428 57,305 19,102 1,910 7,097 2,597 3,910 4,346 662 2,006 2,722 3,161 2,722 50,234 7,071 3,571 3,500 50 Actual October 2018 South 82,919 1,658 81,261 29,022 52,239 19,071 2,479 7,304 3,279 4,523 3,192 629 1,451 1,625 3,576 4,876 52,006 233 3,780 (3,547) 51 Actual November 2018 South 101,327 2,027 99,300 34,451 64,849 23,305 2,797 7,069 3,575 3,250 5,168 787 2,067 1,986 3,596 2,979 56,579 8,271 3,639 4,632 52 Actual December 2018 South 90,982 2,729 88,253 31,844 56,409 20,016 3,002 7,045 3,153 4,495 4,458 784 2,229 1,765 4,936 5,295 57,179 (770) 3,948 (4,718) 53 1,185,754 43,050 1,142,704 393,098 749,606 268,061 33,977 87,079 37,906 49,320 49,997 8,612 23,147 32,641 44,982 57,108 692,830 56,776 46,416 10,360 54 Budget January 2018 South 100,000 1,000 99,000 45,000 54,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,238 2,250 4,000 1,535 41,722 12,278 4,800 7,478 55 Budget February 2018 South 100,000 1,000 99,000 45,000 54,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,238 2,250 4,000 1,535 41,722 12,278 4,800 7,478 56 Budget March 57 Budget April 2018 South 100,000 1,000 99,000 45,000 54,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,238 2,250 4,000 1,535 41,722 12,278 4,800 7,478 2018 South 100,000 1,000 99,000 45,000 54,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,238 2,250 4,000 1,535 41,722 12,278 4,800 7,478 58 Budget May 59 Budget June 2018 South 2018 South 100,000 1,000 99,000 45,000 54,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,238 2,250 4,000 1,535 41,722 12,278 4,800 7,478 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 60 Budget July 2018 South 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 61 Budget August 2018 South 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 62 Budget September 2018 South 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 53 Budget October 2018 South 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 64 Budget November 2018 South 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 55 Budget December 2018 66 South 100,000 2,000 98,000 45,000 53,000 14,000 1,750 7,500 2,500 4,000 2,200 750 1,225 2,250 4,000 1,519 41,694 11,306 4,800 6,506 1,200,000 19,000 1,181,000 540,000 641,000 168,000 21,000 90,000 30,000 48,000 26,400 9,000 14,763 27,000 48,000 18,306 500,468 140,532 57,600 82,932 U Actual Vs Budget 2018 2018 2018 North South Total A B C D E F G H J K L M N 0 P Q R S T 1 Actual Actual Actual Actual Budget Budget Budget 2 2017 2018 2018 2018 2018 2018 2018 3 North North South Total Common size North South Total 4 % % % % Common size % % 5 Sales 1,294,815 1,285,758 1,185,754 2,471,512 100% 100% 100% 1,164,116 1,200,000 2,364,116 100% 100% 6 7 Net Sales 1,256,754 1,238,232 1,142,704 2,380,936 97% 96% 96% 1,125,797 1,181,000 2,306,797 97% 98% 8 COGS 421,892 9 Gross Margin 834,862 428,756 393,098 809,477 749,606 821,853 33% 33% 33% 360,597 1,559,083 64% 63% 63% 765,201 540,000 900,597 641,000 1,406,201 31% 45% 66% 53% 10 Expenses (list below) 11 12 Wages 279,887 283,390 268,061 551,451 22% 22% 23% 271,650 13 Fringe benefits 33,369 34,436 33,977 68,413 3% 3% 3% 30,107 14 Rent 86,539 86,026 87,079 173,105 7% 7% 7% 79,147 168,000 439,650 21,000 90,000 169,147 23% 14% 51,107 3% 2% 7% 8% 15 Depreciation 39,020 39,234 37,906 77,140 3% 3% 3% 39,848 30,000 69,848 3% 3% 16 Utilities 46,370 46,526 49,320 95,846 4% 4% 4% 60,429 48,000 108,429 5% 4% 17 Freight 54,878 53,736 49,997 103,733 4% 4% 4% 34,459 26,400 60,859 3% 2% 18 Phone and Internet 8,499 8,351 8,612 16,963 1% 1% 1% 6,627 9,000 15,627 1% 1% 19 Packaging 26,190 21,721 23,147 44,868 2% 2% 2% 14,687 14,763 29,450 1% 1% 20 Bank and Moneris 40,772 38,164 32,641 70,806 3% 3% 3% 39,624 27,000 66,624 3% 2% 21 Marketing 46,815 48,765 44,982 93,747 4% 4% 4% 42,388 48,000 90,388 4% 4% 22 Other Expenses 59,175 56,164 57,108 113,272 5% 4% 5% 42,797 18,306 61,102 4% 2% 23 2% 24 Total Expenses 721,514 716,513 692,830 1,409,343 56% 56% 58% 661,764 500,468 1,162,232 57% 42% 25 Op Income 113,348 92,963 56,776 149,740 9% 7% 5% 103,437 140,532 243,969 9% 12% 26 Interest Expense 46,611 49,530 46,416 95,946 4% 4% 4% 47,670 57,600 105,270 4% 5% 27 Net Income 66,737 43,433 10,360 53,794 5% 3% 1% 55,767 82,932 138,699 5% 7% 28 29 W Actual Yr Over Yr North