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: Percentage of Total Sales COGS 33% Wages + Benefits 25% Rent + Utilities 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. Rent Act/Bud Month Year Location Sales Budget September 2018 North 124,943 Budget October 2018 North 112,038 Budget November 2018 North 100,693 Budget December 2018 North 112,768 Actual January 2018 South 99,128 Actual February February 2018 South 96,737 Actual March 2018 South 83,664 Actual April 2018 South 83,142 Actual May 2018 South 96,632 Actual June 2018 South 110,961 Actual July 2018 South 112,709 Actual August 2018 South 105,252 Actual September 2018 South 116,823 Actual October 2018 South 101,249 Actual November 2018 South 117,982 Actual December 2018 South 118,308 Budget January 2018 South 100,000 Budget February 2018 South 100,000 Budget March 2018 South 100,000 Budget April 2018 South 100,000 Budget May 2018 South 100,000 Budget June 2018 South 100,000 Budget July 2018 South 100,000 Budget August 2018 South 100,000 Budget September 2018 South 100,000 Budget October 2018 South 100,000 Budget November 2018 South 100,000 Budget December 2018 South 100,000 Sales Discounts Net Sales COGS Gross Margin Wages Fringe benefits 2,499 122,444 42,481 79,964 27,487 2,749 2,241 109,797 33,611 76,186 28,010 3,361 2,014 98,679 34,236 64,444 24,166 2,658 2,255 110,513 36,086 74,427 25,937 3,112 4,956 94,172 33,704 60,468 23,791 2,855 2,902 93,835 31,923 61,912 20,315 2,844 1,673 81,991 29,282 52,708 19,243 2,886 4,157 78,985 25,774 53,211 16,628 1,663 1,933 94,699 30,922 63,777 22,225 3,334 5,548 105,413 33,288 72,125 23,302 3,495 5,635 107,074 34,940 72,134 24,796 2,480 3,158 102,094 32,628 69,466 22,103 2,431 3,505 113,318 36,215 77,103 23,365 2,804 4,050 97,199 32,400 64,799 22,275 3,118 2,360 115,622 41,294 74,329 29,496 4,424 5,915 112,393 35,492 76,900 29,577 3,845 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 6,112 6,340 6,906 6,801 7,087 7,112 7,035 7,155 7,276 7,272 7,410 7,262 7,138 7,010 7,398 7,383 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 Depreciation Utilities 3,181 5,750 2,674 4,360 3,159 4,788 3,121 4,622 2,674 4,781 2,514 4,744 2,988 4,673 3,634 3,079 2,680 4,402 3,704 4,908 3,137 4,723 3,561 4,323 3,040 4,669 3,416 3,675 2,543 4,151 3,363 3,436 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 Freight 3,823 3,361 2,739 4,330 3,370 4,469 4,392 3,351 3,092 3,995 4,542 3,263 3,984 3,888 5,781 4,614 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 Phone & Internet Packaging Bank and Moneris Marketing Other Exp Total Expenses 545 1,699 6,122 3,765 6,122 67,356 590 1,681 5,490 3,289 3,294 62,449 573 1,712 2,960 3,988 4,934 58,584 516 1,443 4,421 3,346 5,526 63,175 721 1,685 2,825 3,153 2,825 55,767 668 1,915 2,815 3,398 4,692 55,486 673 2,050 1,640 4,981 4,919 55,481 731 1,289 1,580 3,423 3,949 46,482 664 1,546 2,841 3,056 4,735 55,851 691 2,330 5,271 4,091 4,217 63,275 654 2,096 2,141 3,841 3,212 59,033 700 1,305 2,042 3,217 6,126 56,333 674 2,535 2,266 4,261 5,666 60,401 647 1,296 3,888 4,339 4,860 58,412 749 2,478 3,469 4,789 4,625 69,902 646 1,420 4,496 4,314 4,496 67,589 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 Op Income 12,607 13,737 5,860 11,252 4,701 6,425 -2,772 6,729 7,926 8,850 13,101 13,134 16,702 6,387 4,427 9,311 12,278 12,278 12,278 12,278 12,278 11,306 11,306 11,306 11,306 11,306 11,306 11,306 Interest Exp Net Income 4,167 8,440 4,601 9,136 4,312 1,548 3,201 8,051 3,711 990 4,385 2,040 3,972 -6,744 3,884 2,845 4,320 3,606 4,210 4,640 3,765 9,336 3,695 9,439 3,666 13,036 4,337 2,050 3,564 863 4,391 4,920 4,800 7,478 4,800 7,478 4,800 7,478 4,800 7,478 4,800 7,478 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 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: Percentage of Total Sales COGS 33% Wages + Benefits 25% Rent + Utilities 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. Rent Act/Bud Month Year Location Sales Budget September 2018 North 124,943 Budget October 2018 North 112,038 Budget November 2018 North 100,693 Budget December 2018 North 112,768 Actual January 2018 South 99,128 Actual February February 2018 South 96,737 Actual March 2018 South 83,664 Actual April 2018 South 83,142 Actual May 2018 South 96,632 Actual June 2018 South 110,961 Actual July 2018 South 112,709 Actual August 2018 South 105,252 Actual September 2018 South 116,823 Actual October 2018 South 101,249 Actual November 2018 South 117,982 Actual December 2018 South 118,308 Budget January 2018 South 100,000 Budget February 2018 South 100,000 Budget March 2018 South 100,000 Budget April 2018 South 100,000 Budget May 2018 South 100,000 Budget June 2018 South 100,000 Budget July 2018 South 100,000 Budget August 2018 South 100,000 Budget September 2018 South 100,000 Budget October 2018 South 100,000 Budget November 2018 South 100,000 Budget December 2018 South 100,000 Sales Discounts Net Sales COGS Gross Margin Wages Fringe benefits 2,499 122,444 42,481 79,964 27,487 2,749 2,241 109,797 33,611 76,186 28,010 3,361 2,014 98,679 34,236 64,444 24,166 2,658 2,255 110,513 36,086 74,427 25,937 3,112 4,956 94,172 33,704 60,468 23,791 2,855 2,902 93,835 31,923 61,912 20,315 2,844 1,673 81,991 29,282 52,708 19,243 2,886 4,157 78,985 25,774 53,211 16,628 1,663 1,933 94,699 30,922 63,777 22,225 3,334 5,548 105,413 33,288 72,125 23,302 3,495 5,635 107,074 34,940 72,134 24,796 2,480 3,158 102,094 32,628 69,466 22,103 2,431 3,505 113,318 36,215 77,103 23,365 2,804 4,050 97,199 32,400 64,799 22,275 3,118 2,360 115,622 41,294 74,329 29,496 4,424 5,915 112,393 35,492 76,900 29,577 3,845 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 1,000 99,000 45,000 54,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 2,000 98,000 45,000 53,000 14,000 1,750 6,112 6,340 6,906 6,801 7,087 7,112 7,035 7,155 7,276 7,272 7,410 7,262 7,138 7,010 7,398 7,383 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 7,500 Depreciation Utilities 3,181 5,750 2,674 4,360 3,159 4,788 3,121 4,622 2,674 4,781 2,514 4,744 2,988 4,673 3,634 3,079 2,680 4,402 3,704 4,908 3,137 4,723 3,561 4,323 3,040 4,669 3,416 3,675 2,543 4,151 3,363 3,436 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 2,500 4,000 Freight 3,823 3,361 2,739 4,330 3,370 4,469 4,392 3,351 3,092 3,995 4,542 3,263 3,984 3,888 5,781 4,614 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 2,200 Phone & Internet Packaging Bank and Moneris Marketing Other Exp Total Expenses 545 1,699 6,122 3,765 6,122 67,356 590 1,681 5,490 3,289 3,294 62,449 573 1,712 2,960 3,988 4,934 58,584 516 1,443 4,421 3,346 5,526 63,175 721 1,685 2,825 3,153 2,825 55,767 668 1,915 2,815 3,398 4,692 55,486 673 2,050 1,640 4,981 4,919 55,481 731 1,289 1,580 3,423 3,949 46,482 664 1,546 2,841 3,056 4,735 55,851 691 2,330 5,271 4,091 4,217 63,275 654 2,096 2,141 3,841 3,212 59,033 700 1,305 2,042 3,217 6,126 56,333 674 2,535 2,266 4,261 5,666 60,401 647 1,296 3,888 4,339 4,860 58,412 749 2,478 3,469 4,789 4,625 69,902 646 1,420 4,496 4,314 4,496 67,589 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,238 2,250 4,000 1,535 41,722 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 750 1,225 2,250 4,000 1,519 41,694 Op Income 12,607 13,737 5,860 11,252 4,701 6,425 -2,772 6,729 7,926 8,850 13,101 13,134 16,702 6,387 4,427 9,311 12,278 12,278 12,278 12,278 12,278 11,306 11,306 11,306 11,306 11,306 11,306 11,306 Interest Exp Net Income 4,167 8,440 4,601 9,136 4,312 1,548 3,201 8,051 3,711 990 4,385 2,040 3,972 -6,744 3,884 2,845 4,320 3,606 4,210 4,640 3,765 9,336 3,695 9,439 3,666 13,036 4,337 2,050 3,564 863 4,391 4,920 4,800 7,478 4,800 7,478 4,800 7,478 4,800 7,478 4,800 7,478 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506 4,800 6,506