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 Year Location Actual January 2017 North Sales 113,358 Sales Discounts Net Sales 5,668 107,690 COGS 38,542 Gross Margin Wages Fringe benefits Rent Depreciation Utilities Freight Phone & Internet Packaging Bank and Moneris Marketing Other Exp Total Expenses Op Income Interest Exp Net Income 69,148 26,072 2,607 7,174 3,937 3,084 5,396 735 1,542 5,385 3,793 3,231 62,955 6,193 4,425 1,768 Actual February 2017 North 80,963 1,619 79,344 26,718 52,626 17,812 1,959 7,060 2,591 3,087 3,206 800 1,336 3,967 4,185 2,380 48,384 4,242 3,770 472 Actual March 2017 North 85,449 2,563 82,886 28,198 54,687 17,944 2,692 7,399 2,642 4,748 3,948 630 1,974 3,315 3,654 4,973 53,919 768 4,430 (3,662) Actual April Actual May Actual June Actual Actual 2017 North 99,367 4,968 94,399 32,791 61,608 20,867 2,295 7,222 2,697 3,158 3,607 768 1,967 2,832 3,817 4,720 53,951 7,657 4,408 3,249 2017 North 111,352 2,227 109,125 38,973 70,152 27,838 2,784 7,093 2,854 3,921 3,897 721 2,728 5,456 3,836 4,365 65,493 4,658 4,238 420 2017 North 95,933 1,919 94,014 32,617 61,397 20,146 2,015 7,061 3,094 4,431 4,893 606 1,957 1,880 3,004 3,761 52,847 8,550 3,992 4,558 July 2017 North 99,811 2,994 96,817 30,941 65,875 23,955 3,114 7,094 3,549 4,899 3,404 706 1,856 2,905 4,615 2,905 59,001 6,874 3,966 2,908 August 2017 North 89,324 3,573 85,751 27,690 58,061 19,651 2,948 7,276 3,142 3,089 4,154 699 1,385 1,715 3,253 5,145 52,456 5,604 3,868 1,736 Actual September 2017 North 107,859 3,236 104,623 34,515 70,108 26,965 3,775 7,208 3,982 4,181 4,832 777 2,071 4,185 3,225 5,231 66,432 3,676 4,345 (669) Actual October 2017 North 115,518 2,310 113,208 36,966 76,242 27,724 2,772 7,149 3,944 4,342 3,697 707 1,479 4,528 3,721 5,660 65,724 10,518 4,275 6,243 Actual November 2017 North 94,117 1,882 92,235 32,000 60,235 21,647 2,814 7,351 3,814 4,124 4,800 718 1,600 3,689 4,153 3,689 58,400 1,835 3,887 (2,052) Actual December 2017 North 93,408 4,670 88,738 32,693 56,045 23,352 2,569 7,379 3,911 4,244 3,923 614 1,962 2,662 3,505 5,324 59,445 (3,400) 4,256 (7,656) Actual January 2018 North 96,411 1,928 94,483 30,852 63,631 20,246 2,834 7,198 2,894 3,975 4,628 638 1,851 1,890 4,323 2,834 53,312 10,320 4,473 5,847 Actual Actual March Actual April Actual May Actual June Actual Actual February 2018 North 98,715 4,936 93,779 30,602 63,178 24,679 2,715 7,262 3,166 3,247 4,284 663 2,142 3,751 4,585 3,751 60,245 2,933 4,351 (1,418) 2018 North 91,983 3,679 88,304 28,515 59,789 19,316 1,932 7,022 3,099 4,991 3,422 718 1,141 3,532 3,450 2,649 51,272 8,517 4,257 4,260 July Actual 2018 North 2018 North 2018 North 2018 North August 2018 North September 2018 North 103,294 3,099 100,195 32,021 68,174 23,758 3,564 7,087 3,768 3,520 3,843 624 1,921 4,008 4,195 4,008 60,295 7,879 3,774 4,105 89,195 3,568 85,627 30,326 55,301 22,299 2,453 7,328 3,774 4,390 3,336 626 2,123 4,281 4,084 3,425 58,119 (2,818) 3,576 (6,394) 93,410 1,868 91,542 30,825 60,717 23,353 2,802 7,460 3,921 3,211 3,083 604 1,850 3,662 4,353 3,662 57,959 2,757 3,523 (766) 111,281 2,226 109,055 34,497 74,558 27,820 4,173 7,462 2,984 3,822 4,830 677 1,725 5,453 4,641 5,453 69,039 5,519 3,924 1,595 90,149 2,704 87,445 28,848 58,597 18,030 2,704 7,055 3,507 3,767 4,039 681 1,154 4,372 4,894 2,623 52,826 5,770 3,951 1,819 111,586 4,463 107,123 36,823 70,299 25,665 3,593 7,175 2,511 3,567 4,051 638 2,578 3,214 4,019 4,285 61,295 9,005 4,479 4,526 Actual October 2018 North 124,172 4,967 119,205 43,460 75,745 29,801 3,278 7,065 3,975 4,647 6,519 649 2,173 3,576 3,550 3,576 68,810 6,935 3,712 3,223 Actual November 2018 North 118,293 4,732 113,561 36,671 76,890 24,842 2,981 7,476 3,694 4,838 5,501 666 2,200 5,678 4,588 5,678 68,142 8,749 3,684 5,065 Actual December 2018 North 110,035 2,201 107,834 38,512 69,322 22,007 3,301 7,042 3,528 3,968 5,007 647 2,311 5,392 4,095 3,235 60,532 8,790 4,280 4,510 Budget January 2018 North 102,574 2,051 100,523 33,849 66,673 22,566 2,257 6,237 2,619 4,278 3,723 500 1,354 5,026 3,927 4,021 56,508 10,165 3,891 6,274 Budget February 2018 North 88,283 Budget March Budget April Budget May Budget June Budget July 3,531 84,752 29,133 55,618 22,071 2,648 6,479 3,813 5,385 3,787 561 1,748 2,543 3,373 4,238 56,646 (1,027) 4,364 (5,391) 2018 North 2018 North 2018 North 2018 North 2018 North 113,114 2,262 110,852 35,065 75,786 24,885 2,737 6,542 2,915 4,503 4,558 512 1,052 2,217 3,624 3,326 56,871 18,915 3,796 15,119 114,888 114,483 90,176 4,596 110,292 37,913 72,379 27,573 3,309 6,311 3,949 5,837 4,170 517 2,275 3,309 3,370 5,515 66,135 6,245 4,410 1,835 4,579 2,705 111,190 3,336 107,854 37,805 109,904 34,345 87,471 29,758 57,713 70,050 75,559 27,476 3,022 6,307 3,733 4,881 4,465 588 1,717 4,396 3,515 3,297 63,398 12,161 3,996 8,165 22,544 2,254 6,106 2,517 4,514 2,381 597 1,190 1,749 3,476 4,374 51,702 6,010 3,927 2,083 27,798 3,058 6,518 2,744 5,653 4,159 568 2,268 4,314 3,103 5,393 65,575 4,475 4,352 123 Budget August 2018 North Budget September 2018 North 81,298 3,252 78,046 26,828 51,218 19,512 2,146 6,811 2,503 4,899 2,146 538 1,341 3,122 3,605 3,122 49,745 1,473 4,061 (2,588) 89,494 2,685 86,809 26,848 59,961 19,689 2,166 6,770 3,252 4,646 2,148 500 805 1,736 3,227 3,472 48,411 11,550 3,890 7,660 Budget October 2018 North 80,034 2,401 77,633 25,611 52,022 18,408 1,841 6,841 3,642 4,840 2,049 590 768 2,329 3,533 2,329 47,170 4,852 4,506 346 Budget November 2018 North 95,828 1,917 93,911 31,623 62,288 22,999 2,300 6,691 3,774 4,112 3,479 539 1,265 1,878 3,377 3,756 54,170 8,118 4,966 3,152 Budget December 2018 North 98,290 3,932 94,358 30,470 63,889 23,590 2,595 6,697 3,625 5,145 3,656 557 1,523 3,774 3,912 4,718 59,793 4,096 4,221 (125) July Actual January 2018 South Actual February 2018 South Actual March 2018 South Actual April Actual May Actual June Actual 112,873 108,563 5,644 107,229 34,991 72,239 22,575 3,386 7,318 3,230 4,977 4,899 600 2,099 4,289 4,466 4,289 62,128 10,110 3,793 6,317 2,171 106,392 32,569 73,823 22,798 2,280 7,042 2,706 3,191 3,583 609 1,954 5,320 3,633 4,256 57,371 16,452 4,270 12,182 88,936 4,447 84,489 26,681 57,808 22,234 2,890 7,270 3,015 3,970 3,202 658 1,334 3,380 3,988 5,069 57,010 798 3,568 (2,770) 2018 South 92,161 1,843 90,318 29,492 60,826 19,354 2,903 7,363 3,131 3,107 3,244 636 1,475 1,806 4,172 3,613 50,804 10,023 4,493 5,530 2018 South 101,248 4,050 97,198 33,412 63,786 21,262 3,189 7,437 3,197 4,456 3,341 775 2,339 2,916 3,062 4,860 56,834 6,952 4,336 2,616 Actual August Actual 2018 South 2018 South 2018 South September 2018 South 109,407 5,470 103,937 38,292 65,644 27,352 2,735 7,218 2,756 4,295 4,212 756 2,298 5,197 3,615 4,157 64,591 1,053 3,717 (2,664) 123,355 3,701 119,654 40,707 78,947 27,138 3,257 7,433 2,791 3,672 4,478 610 2,850 2,393 4,504 7,179 66,304 12,643 4,346 8,297 120,420 6,021 114,399 39,739 74,660 30,105 3,312 7,238 2,949 4,684 5,166 663 2,782 5,720 4,243 5,720 72,581 2,079 3,707 (1,628) 81,721 4,086 77,635 26,151 51,484 20,430 3,065 7,368 3,860 4,795 2,615 770 1,046 1,553 4,980 3,105 53,587 (2,103) 3,699 (5,802) Actual Actual Actual October 2018 South November 2018 South December 2018 South 124,650 6,233 118,418 37,395 81,023 29,916 4,188 7,149 2,863 3,580 4,113 737 2,618 2,368 4,372 4,737 66,641 14,381 3,708 10,673 98,023 2,941 95,082 34,308 60,774 19,605 2,157 7,168 3,416 4,842 5,146 732 2,402 2,852 3,501 5,705 57,525 3,249 4,388 (1,139) 111,006 2,220 108,786 38,852 69,934 22,201 3,108 7,133 3,303 3,647 5,439 719 1,554 4,351 3,078 5,439 59,973 9,960 4,380 5,580 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 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 Budget March 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 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 Budget May Budget June 2018 South 2018 South Budget July 2018 South Budget August 2018 South Budget September 2018 South Budget October 2018 South Budget November 2018 South Budget December 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 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 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 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 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 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 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 Sales Net Sales COGS Gross Margin Expenses (list below) Total Expenses Op Income Interest Expense Net Income Actual Actual Actual Actual Budget Budget Budget Actual Vs Budget 2017 2018 2018 2018 2018 2018 2018 North North South Total Common size North South Total % % % % Common size % % 2018 2018 North South 2018 Total Actual Yr Over Yr North 1,186,459 1,148,828