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. 1 Act/Bud 2 Actual Month Year Location January Sales 2017 North 123,402 4,936 Sales Discounts Net Sales COGS 118,466 43,191 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 75,275 29,616 3,554 7,039 3,345 4,251 5,615 676 1,728 3,554 4,391 3,554 67,323 7,952 3,856 4,096 3 Actual February 2017 North 91,003 4,550 86,453 31,851 54,602 20,021 2,002 7,475 2,662 4,837 4,778 673 1,911 4,323 4,295 5,187 58,163 (3,561) 3,776 (7,337) 4 Actual March 2017 North 103,248 3,097 100,151 35,104 65,046 25,812 3,614 7,169 3,072 3,440 3,861 641 2,106 4,006 4,579 6,009 64,309 737 3,742 (3,005) 5 Actual April 6 Actual May 2017 North 2017 North 87,910 118,995 1,758 86,152 30,769 55,383 21,098 2,954 7,324 3,265 4,743 3,692 774 2,154 4,308 3,320 2,585 56,216 (833) 3,872 (4,705) 4,760 114,235 38,078 76,157 29,749 2,975 7,236 2,999 3,127 3,808 664 1,523 3,427 4,913 5,712 66,132 10,024 3,860 6,164 7 Actual June 8 Actual July 2017 North 2017 North 82,039 2,461 79,578 27,073 52,505 19,689 1,969 7,381 2,825 4,974 3,519 791 1,083 2,387 4,367 4,775 53,761 (1,256) 4,389 (5,645) 114,599 5,730 108,869 36,672 72,197 24,066 3,610 7,387 2,780 4,666 5,134 637 1,834 2,177 4,585 3,266 60,142 12,056 3,729 8,327 9 Actual August 2017 North 85,486 1,710 83,776 26,501 57,276 20,517 2,872 7,364 3,197 4,266 3,445 767 1,325 2,513 4,027 4,189 54,482 2,793 3,817 (1,024) 10 Actual September 2017 North 123,813 4,953 118,860 37,144 81,717 26,001 3,900 7,241 3,283 4,913 3,714 617 1,486 5,943 4,976 4,754 66,828 14,888 3,718 11,170 11 Actual October 2017 North 80,771 3,231 77,540 25,039 52,501 19,385 2,132 7,149 2,854 3,369 2,504 633 1,002 2,326 3,158 3,877 48,389 4,112 3,973 139 12 Actual November 2017 North 90,154 3,606 86,548 27,948 58,600 21,637 2,596 7,381 3,941 3,777 3,913 726 1,677 3,462 3,691 5,193 57,994 606 4,282 (3,676) 13 Actual December 2017 North 121,400 6,070 115,330 37,634 77,696 26,708 4,006 7,066 3,335 3,404 4,892 683 2,634 5,767 4,506 5,767 68,768 8,928 4,310 4,618 14 Actual January 2018 North 105,161 2,103 103,058 33,652 69,406 25,239 3,786 7,070 3,682 4,390 3,702 697 1,346 5,153 3,414 6,183 64,662 4,745 4,159 586 15 Actual 16 Actual 17 Actual April 18 Actual May 19 Actual June 20 Actual July February 2018 North March 2018 North 2018 North 99,062 123,328 1,981 97,081 31,700 65,381 24,766 3,715 7,048 3,088 4,178 4,121 748 2,219 1,942 4,700 4,854 61,378 4,003 4,329 (326) 2,467 120,861 43,165 77,697 30,832 3,392 7,054 3,796 3,742 4,316 716 2,590 6,043 4,117 4,834 71,432 6,264 4,372 1,892 97,512 4,876 92,636 31,204 61,433 22,428 2,691 7,270 3,689 3,878 4,681 768 1,872 4,632 4,149 4,632 60,690 743 4,271 (3,528) 2018 North 2018 North 2018 North 109,653 5,483 104,170 33,992 70,178 25,220 3,783 7,449 3,492 3,275 4,759 740 1,360 5,209 3,157 6,250 64,694 5,484 4,321 1,163 88,048 2,641 85,407 26,414 58,992 21,132 2,536 7,459 3,120 4,551 2,906 731 1,057 2,562 4,845 5,124 56,022 2,970 4,267 (1,297) 104,525 2,091 102,435 35,539 66,896 26,131 3,658 7,157 3,441 3,807 4,620 616 1,422 2,049 4,751 3,073 60,725 6,171 4,015 2,156 21 Actual August 2018 North 115,208 2,304 112,904 38,019 74,885 28,802 3,168 7,351 3,764 3,829 4,942 680 2,661 2,258 3,405 6,774 67,635 7,250 4,277 2,973 22 Actual September 2018 North 84,753 1,695 83,058 27,121 55,937 21,188 3,178 7,000 2,784 3,182 3,526 658 1,627 4,153 3,136 3,322 53,755 2,182 4,015 (1,833) 23 Actual October 2018 North 96,832 1,937 94,895 32,923 61,972 21,303 2,343 7,117 3,837 4,688 4,938 782 1,317 4,745 3,049 3,796 57,915 4,057 4,493 (436) 24 Actual November 2018 North 94,778 2,843 91,935 30,329 61,606 18,956 2,085 7,227 3,229 4,820 3,033 602 1,213 2,758 3,982 5,516 53,421 8,185 3,505 4,680 25 Actual December 2018 North 115,125 3,454 111,671 35,689 75,983 28,781 2,878 7,078 3,432 4,125 4,996 696 2,498 2,233 3,805 4,467 64,990 10,992 3,850 7,142 26 Budget January 2018 North 27 Budget February 2018 North 28 Budget March 29 Budget April 30 Budget May 114,616 2,292 112,324 38,969 73,354 25,216 3,026 6,248 3,225 5,431 4,287 500 2,338 2,246 3,186 5,616 61,319 12,035 3,004 9,031 82,988 1,660 81,328 24,896 56,432 18,257 2,191 6,933 2,638 5,368 3,237 571 1,245 3,253 3,370 3,253 50,316 6,116 4,013 2,103 2018 North 2018 North 2018 North 90,846 1,817 89,029 29,979 59,050 22,712 2,271 6,351 2,781 5,412 2,998 520 1,199 4,451 3,105 4,451 56,252 2,798 3,426 (628) 124,210 115,455 2,484 121,726 37,263 84,463 29,810 2,981 6,176 3,918 4,583 4,844 595 2,236 6,086 3,773 4,869 69,872 14,591 4,828 9,763 4,618 110,837 36,946 73,891 26,555 3,187 6,232 2,563 4,603 4,803 592 1,108 5,542 3,172 5,542 63,898 9,993 3,103 6,890 31 Budget June 2018 North 110,420 3,313 107,107 34,230 72,877 24,292 2,915 6,261 3,677 4,468 3,765 562 2,054 2,142 3,869 3,213 57,219 15,658 4,015 11,643 32 Budget July 2018 North 33 Budget August 2018 North 34 Budget September 2018 35 Budget October 2018 North 36 Budget November 2018 North 37 Budget December 2018 North 38 Actual January 2018 South 123,449 2,469 120,980 39,504 81,476 29,628 2,963 6,218 2,793 5,381 4,740 577 2,370 6,049 3,380 3,629 67,729 13,748 3,142 10,606 98,932 2,968 95,964 29,680 66,284 24,733 2,721 6,414 2,577 5,570 2,671 587 1,187 4,798 3,218 2,879 57,355 8,929 3,057 5,872 North 82,524 2,476 80,048 25,582 54,466 18,981 2,088 6,044 3,116 4,171 3,070 501 1,535 1,601 3,929 4,002 49,038 5,428 3,256 2,172 81,555 2,447 79,108 26,098 53,011 18,758 2,063 6,098 3,805 5,413 2,871 509 1,566 2,373 3,064 2,373 48,893 4,118 3,444 674 123,649 3,709 119,940 37,095 82,845 29,676 3,264 6,407 2,650 5,757 4,451 556 1,855 4,798 3,922 5,997 69,333 13,512 3,713 9,799 85,162 3,406 81,756 27,252 54,504 20,439 2,248 6,755 3,062 4,007 3,543 563 1,090 2,453 3,261 3,270 50,691 3,813 4,813 (1,000) 104,499 4,180 100,319 34,485 65,834 25,080 3,511 7,085 2,885 4,810 5,173 646 1,724 5,016 4,628 3,010 63,567 2,267 4,042 (1,775) 39 Actual February 2018 South 107,645 3,229 104,416 32,294 72,122 22,605 2,939 7,123 3,558 4,517 4,844 693 1,292 5,221 3,588 6,265 62,645 9,478 3,852 5,626 40 Actual March 2018 South 118,818 3,565 115,253 40,398 74,855 23,764 3,089 7,431 2,928 4,347 4,848 713 2,020 2,305 3,900 6,915 62,260 12,596 3,905 8,691 42 Actual 41 Actual April May 2018 South 2018 South 94,037 104,783 4,702 89,335 29,151 60,184 23,509 2,586 7,048 3,562 4,235 3,790 714 2,041 3,573 3,238 5,360 59,656 528 3,889 (3,361) 3,143 101,640 35,626 66,013 25,148 3,269 7,228 3,312 3,015 4,988 776 1,781 2,033 4,919 4,066 60,535 5,479 4,092 1,387 42 Actual May 2018 South 104,783 3,143 101,640 35,626 66,013 25,148 3,269 7,228 3,312 3,015 4,988 776 1,781 2,033 4,919 4,066 60,535 5,479 4,092 1,387 43 Actual 44 Actual 45 Actual June 2018 South 99,338 3,974 95,364 32,782 62,583 20,861 2,086 7,221 3,848 3,053 4,589 786 1,639 4,768 3,983 4,768 57,603 4,980 3,990 990 July 2018 South 116,135 2,323 113,812 39,486 74,326 25,550 3,577 7,305 2,529 3,650 5,528 716 1,579 5,691 3,375 5,691 65,190 9,136 3,833 5,303 46 Actual August 2018 South September 2018 South 112,126 4,485 107,641 38,123 69,518 25,789 2,837 7,149 3,223 3,840 4,575 749 1,906 2,153 4,505 6,458 63,184 6,334 3,658 2,676 103,382 2,068 101,314 31,015 70,300 24,812 2,729 7,120 2,838 4,161 3,412 763 1,241 4,053 3,907 6,079 61,114 9,186 4,321 4,865 47 Actual October 2018 South 119,439 4,778 114,661 37,026 77,635 23,888 2,867 7,349 3,549 4,095 5,554 678 2,592 4,586 3,011 5,733 63,902 13,734 3,813 9,921 48 Actual November 2018 South 101,175 2,024 99,152 33,388 65,764 20,235 2,833 7,460 2,635 4,233 3,673 783 2,337 2,975 4,331 2,975 54,469 11,295 4,289 7,006 49 Actual December 2018 South 91,134 4,557 86,577 29,163 57,414 19,138 2,105 7,362 3,945 4,710 3,500 655 1,167 4,329 4,931 2,597 54,439 2,976 4,428 (1,452) 50 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 51 Budget February 52 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 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 53 Budget April 54 Budget May 55 Budget June 2018 South 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 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 56 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 57 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 58 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 59 Budget October 2018 South 60 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 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 December 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