https://onedrive.live.com/edit.aspx?resid=854EC95DCB79A0F9!3754&ithint=file%2cxlsx&authkey=!ANSuRJAQZAx6y9Y
A B C D E F C H J K L M N Q R S T U V V X Nathan and Gurleen Actual Actual Actual Actual North South Total Budget Budget Budget Actual Vs Budget Actual 2021 2022 2022 2022 2022 2022 2022 2022 2022 2022 2022 2022 2022 Yr Over Yr North North South Total Common size North South Total Common size North South Total North AW Sales 1,237,521 1,225,740 1,264,961 2,490,701 100% 100% 100% 1,161,784 1,200,000 2,361,784 100% 100% 100% 63,956 64,961 128,917 99% Sales Discount 43,067 39,202 48,447 87,649 3% 4% 4% 30,110 19,000 49,110 3% 2% 2% 9,092 29,447 38,539 91% 39% Net Sales 1,194,454 1,186,538 1,216,514 2,403,052 97% 96% 96% 1,131,674 1,181,000 2,312,674 97% 98% 98% 54,864 35,514 90,378 COGS 401,408 406,583 408,387 814,970 33% 32% 33% 368,961 540,000 908,961 32% 45% 38% 37,622 131,613) (93,991) 101% Gross Margin 793,046 779,955 808,127 1,588,082 64% 64% 64% 762,713 641,000 1,403,713 66% 53% 59% 17,242 167,127 184,369 98% 10 Expenses (list below) Wages 289,538 275,222 278,622 553,844 22% 22% 22% 275,076 168,000 443,076 24% 14% 19% 146 110,622 110,768 95% 11 12 Fringe benefits 35,698 33,972 35,852 69,824 3% 3% 3% 31,042 21,000 52,042 3% 2% 2% 2,930 14,852 17,782 95% 13 Rent 87,606 86,891 86,614 173,505 7% 7% 7% 79,443 90,000 169,443 7% 8% 7% 7,448 (3,386) 4,062 99% 14 Depreciation 37,399 39,533 38,601 78,134 3% 3% 3% 40,514 30,000 70,514 3% 3% 3% (981) 8,601 7,620 106% Utilities 48,697 49,335 48,672 98,007 4% 4% 4% 58,332 48,000 106,332 5% 4% 5% (8,997) 672 (8,325) 101% 15 Freight 49,601 51,808 49,439 101,247 4% 4% 4% 41,390 26,400 67,790 4% 2% 3% 10,418 23,039 33,457 104% 16 17 Phone & Internet 8,231 8,343 8,429 16,772 1% 1% 1% 6,490 9,000 15,490 1% 1% 1% 1,853 (571) 1,282 101% 18 Packaging 21,793 22,203 21,411 43,614 2% 2% 2% 15,478 14,763 30,241 1% 1% 1% 6,725 6,648 13,373 102% 19 41,678 43,604 43,931 87,535 4% 3% 4% 37,562 27,000 64,562 3% 2% 3% 16,931 22,973 105% Bank and Moneris 6,042 20 Marketing 48,483 44,417 49,523 93,940 4% 4% 4% 42,549 48,000 90,549 4% 4% 4% 1,868 1,523 3,391 92% 54,645 52,350 52,632 104,982 4% 4% 4% 49,640 18,306 67,946 4% 2% 3% 2,710 34,326 37,036 96% IN Other Exp 22 Total Expenses 723,369 707,678 713,729 1,421,407 58% 56% 57% 677,512 500,468 1,177,980 58% 42% 50% 30,166 213,261 243,427 98% 104% 23 Op Income 69,677 72,277 94,398 166,675 6% 7% 7% 85,201 140,532 225,733 7% 12% 10% (12,924) (46,134) (59,058) 24 Interest Expense 48,382 47,510 47,644 95,154 4% 4% 4% 48,467 57,600 106,067 4% 5% 4% (957 (9,956) (10,913) 98% 25 Net Income 21,295 24,767 46,734 71,501 2% 4% 3% 36,734 82,932 119,666 3% 7% 5% (11,967) (36,198) (48,165) 116% 26 27 28 29 2. Use common size and specific ratios to analysize year over year results and to compare to budget data. (only 5 ratios)Database includes 2 years worth of actual data and one year of budget data for a retail business with 2 locations. In 2021, there was just one location, North. In January 2022, 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 2021 and 2022. 2. Use common size and specific ratios to analysize year over year results and to compare to budget data.Act/Bud Month Year Location Sales Sales Discounts Net Sales COGS Gross Margin Wages Fringe benefits Rent Depreciation Utilities Actual January 2021 North Freight 121,193 3,636 117,557 Phone & Intemet Packaging Bank and Moneris Marketing 7,570 Other Exp Total Expenses 79,987 Op Income 26,662 3,733 7,141 2,587 Interest Exp Net Income 3,623 4,508 609 Actual February 2021 North 98,742 2,254 2,351 2,962 95,780 33,572 4,207 4,702 62,207 62,378 23,698 17,609 4,442 13,167 3,081 7,231 3,480 Actual March 2,748 2021 North 95,574 3,693 623 1,911 93,663 30,584 1,679 4,789 4,789 53,07 4,322 21,982 60,132 2,075 4,099 (2,024) 3,077 7,347 3,468 4,444 3,364 Actual 702 April 2021 North 106,701 102,433 1,223 34,144 3,747 3,910 4,268 68,289 5,620 58,884 23,474 4,195 87 2,582 7,269 4,108 3,011 4,801 4,097 Actual May 2021 North 84,309 627 1,366 4,215 80,094 2,049 4,212 8.655 53,958 1,939 6,146 59,634 4,129 19,391 7,435 1,526 Actual 3,320 4,109 2021 North 2,614 679 June 110,204 1,830 2,403 5,510 3,741 4,005 51,465 04,694 7,46 57,224 2,493 26,449 3,967 3,683 7,286 3,523 4,122 (1,190) 781 Actual July 2021 4,480 North 95,207 1,499 2,856 92,351 29,514 1,188 62,837 4,126 3,141 63,561 3,663 21,898 2,628 7,201 1,294 2,696 (631 4,708 Actual 4,132 August 2021 658 North 114,815 4,593 110,222 1,476 4,618 35,593 4,634 74,630 4,618 3,572 26,407 59,265 4,158 (586) 3,697 7,396 3,326 Actual September 3,932 95,695 3,915 2021 91,867 610 North 1,780 4,409 3,828 29,665 62,202 23,924 4,272 5,511 65,255 9,374 3,505 2,392 7,238 5,869 3,025 Actual October 3,655 2021 4,153 North 100,610 674 1,187 4,024 96,586 4,148 61,372 1,837 54,989 25,153 2,756 7,212 3,270 7,367 4,163 3,142 3,049 3,244 12 Actual 4,930 November 2021 727 North 17,184 2,465 114,840 3,863 2,898 2,344 39,843 3,759 60,817 555 74,998 28,124 7,487 3,765 (3,210) 3,094 Actual 2021 ,215 4,924 December 5,578 741 North 97,287 2,919 94,368 2,789 1,594 3,663 32,105 62,264 5,742 22,376 69,954 5,043 4,013 1,030 2,238 7,208 3,334 3,297 4,495 800 1,237,521 43,067 2,247 2,831 3,489 1,194,45 4,718 401,408 793,046 289,538 57,033 5,231 35,698 87,606 37,399 48,697 4,023 1,208 5 Actual January 2022 North 19,601 83,825 1,677 82,149 1,231 21,793 28,501 41,67 18,48 54,645 53,648 17,603 723,369 69,677 48,382 1,760 7,492 21,295 3,123 4,423 16 Actual February 2022 North 106,010 2,850 642 1,425 2,120 103,890 37,104 66,786 2,464 3,412 4,107 49,303 22,267 4,34 4,377 3,117 7,060 (32) 2022 3,245 4,198 4,823 17 Actual March 617 1,484 North 94,430 4,722 89,709 3,117 33,051 56,658 4,577 5,194 59,695 7,092 21,719 4,40 2,172 7,416 3,915 2,686 Actual 3,254 April 3,636 689 2022 North 111,832 1,653 2,237 109,595 4,020 34,668 2,691 74,927 26,840 3,588 54,752 1,906 3,489 7,272 4,092 3,043 4,262 4,854 2,186 19 Actual May 762 2022 North 122,547 1,733 3,28 120,096 3,890 2,451 42,891 7,205 3,741 3,664 10 Actual 7,301 6,576 66,008 8,919 28,186 5,178 3,425 3,822 5,576 614 June 2022 North 122,742 39,277 3,002 2,455 120,287 6,005 81,010 3,189 5,005 25,776 70,789 6,416 3,989 3,093 7,018 2,427 3,812 5,499 21 Actual 3,694 July 2022 91,264 629 2,357 North 4,811 4,563 3,616 86,701 31,030 55,671 6,014 20,078 6,319 14,690 3,761 2,209 7,404 3,658 10,929 4,654 Actual 2022 4,351 112,055 800 August North 4,482 107,573 33,617 2,172 1,734 3,848 2,601 73,956 53,509 26,893 2,162 3,496 3,934 (1,772 7,075 2,981 3,881 Actual September 2022 4,034 North 120,016 617 1,345 4,801 115,215 4,303 4,512 42,006 73,210 6,454 65,591 8,365 24,003 2,880 3,620 7,197 4,745 24 Actual 3,296 4,635 5,461 783 October 2022 North 91,372 2,520 3,456 3,458 4,569 86,803 30,153 56,651 3,456 61,147 12,063 4,099 7,964 22,843 3,426 7,067 2,894 4,825 3,618 794 25 Actual November 2022 North 86,667 3,467 1,809 4,340 83,200 27,733 55,467 3,203 57,424 (774) 19,933 2,604 4,437 1,993 7,381 16 Actual 2,823 4,033 December 2022 North 3,883 754 (5,211) $2,980 1,109 1,660 3,328 81,320 26,554 54,767 19,085 3,082 2,496 50,816 4,651 2,672 3,528 1,123 7,208 3,436 3,839 2,921 642 1,225,740 1,593 4,066 39,202 1,186,538 406,583 779,955 3,610 275,222 3,253 52,325 2,441 33,972 (1,085 86,891 8 Budget 9,533 3,526 January 2022 North 49,335 83,464 51,808 8,343 43,604 1,669 81,795 22,203 44,417 27,543 54,252 19,197 52,350 707,678 72,277 2,304 47,510 6,256 2,886 24,767 5,474 29 Budget February 2,203 2022 North 527 826 102,161 2,043 1,636 30,648 3,439 4,090 00,118 69,469 48,838 5,414 22,475 1,864 2,697 3,550 6,595 3,881 4,805 Budget March 535 2022 North 2,75 86,630 1,226 4,005 3,041 1,733 84,897 27,722 55,022 57,176 21,658 3,004 2,599 6,495 14,447 4,572 9,875 2,959 4,526 3,604 Budget April 2022 529 North 86,616 1,663 2,598 84,018 25,985 58,033 1,698 3,840 4,245 21,654 53,81 2,382 3.738 3,360 3,57 (214) 6,814 5,422 3,118 554 32 Budget May 2022 780 North 108,283 2,166 3,808 106,117 2,521 3,361 54,151 35,733 70,384 3,882 27,071 2,978 6,896 4,935 1,053) 3,447 4,326 Budget Jun 2022 North 2,859 124,137 570 3,724 120,413 37,241 1,429 3,184 83,172 3,686 4,245 29,793 60,690 9,694 2,979 4,692 6,282 2,554 5.431 5,002 4,097 July 2022 North 80,075 589 2,234 76,872 6,021 3,566 3,203 24,823 4,817 52,049 68,362 14,809 18,417 3,924 10,885 2,210 6,957 Budget 3,511 4,848 2,979 August 2022 North 88,893 501 74! 87,115 1,537 3,799 1,778 60,447 3,844 49,348 1,701 3,660 20,445 (959) 2,249 6,625 3,417 5,357 September 3,467 5 Budget 2022 521 800 North $4,761 27,971 4,356 3,557 2,543 82,218 54,247 3,485 54,279 6,169 18,647 4,635 2,051 6,961 1,534 4,316 37 Budget 2022 3,382 3,357 October 518 North 102,445 100,396 1,119 2,46 2,049 31,758 68,638 3,719 50,647 25,611 3,60 ,157 (557) 3,073 6,701 4,111 38 Budget November 2022 3,557 4,237 3,811 500 North 115,884 953 5,020 4,635 111,249 3,445 9,401 5,020 71,848 25,494 61,928 6,710 3,486 3,224 3,059 6,551 3,896 4,556 Budget 98,435 5,127 December 2022 North 556 2,364 2,225 3,205 1,969 96,466 33,468 62,998 5,562 24,609 62,591 9,257 2,461 3,187 6,310 6,070 3,286 5,034 4,016 590 1,161,784 30,110 1,131,674 368,961 1,339 762,713 2,894 3,444 3,859 275,072 57,841 5,157 31,042 4,095 Actual 79,443 40,514 58,332 1,062 January 2022 41,390 6,490 South 88,201 86,437 15,478 37,562 42,549 49.640 1,764 29,106 57,331 18,522 677,512 85,201 7,288 48,467 36,734 2,408 2,673 Actual 3,185 4,36 729 February 2022 South 121,848 2,037 4,322 3,434 3.457 5,092 115,756 75,546 52,422 4,909 0,210 4,285 29,244 3,509 7,091 624 3,645 43 Actual 4,732 4,423 March 2022 South 93,699 744 1,608 91,825 5,788 4,111 4,630 1,874 29,984 61,841 20,614 69,525 4,30 2,061 7,099 6,021 1,719 Actual 2022 South 3,662 3,215 4,198 696 April 106,249 104,124 1,799 2,125 34,000 1,591 4,667 70,124 2,755 22,312 55,357 6,484 3,347 7,416 4,117 3,996 3,841 2,36 Actual May 2022 South 5,100 625 2,380 106,236 3,187 103,049 2,082 4,308 33,996 69,053 6,247 61,655 8,469 3,779 4.690 26,559 3,453 7,324 2,678 3,415 Actual June 2022 South 3,740 123,319 778 2,040 6,166 117,153 39,462 ,122 4,207 77,691 3,091 61,406 27,130 7,647 3,615 2,984 7,281 4,032 3,812 Actual 4,318 3,946 July 2022 South 86,306 631 2,368 4,315 81,99 4,68 3,024 1,686 25,892 64,867 56,099 18,124 12,824 3,638 9.186 2,537 7,117 3,105 3,956 2,848 18 Actual August 2022 778 1,036 South 95,059 1,640 3,959 4,753 10,30 48,380 2,320 57,986 3,280 19,012 2,472 7,264 7,719 4,109 3,610 3,341 4,338 19 Actual 2022 4,202 618 September South 108,364 1,293 5,418 102,946 37,927 65,018 2,709 4,452 4,515 21,673 54,215 3,771 3,251 7,245 4,230 (459 0 Actual 3,316 October 2022 South 4,931 3,793 728 122,442 4,898 117,544 1,896 3,088 3,774 36,733 80,812 6,177 25,713 3,600 59,872 5,146 3,866 1,280 2022 119,659 7,292 November 3,014 Actual 3,465 4,041 South 717 5,983 113,676 40,684 72,992 2,204 5,877 4,928 3,526 64,377 26,325 16,435 3,936 12,499 3,422 7,016 2,670 4,517 December 2022 93,579 5,696 2 Actual 619 South 1,872 91,707 1,627 63,634 4,804 28,074 23,395 2,274 5,684 64,654 8,338 3,675 2,807 7,181 4,663 2,689 1,264,961 4,759 3,088 766 1,123 48,447 ,216,514 408,387 308,127 278,622 2,751 3,855 4,585 57,000 6,634 35,852 4,112 2,522 38,601 Budget January 2022 86,614 48,672 49,439 3,429 South 100,000 1,000 99,000 21,411 45,000 54,000 43,931 49,523 52,635 14,000 713,729 94,398 1,750 7,500 17,664 46,734 2,500 4,000 65 Budget February 2022 South 2,200 750 100,000 1,238 1,000 99,000 2,250 4,000 45,000 54,000 1,535 4,000 1,722 12,278 4,800 1,750 7,500 2,500 7,478 6 Budget March 4,000 2,200 750 2022 South 100,000 45,000 54,000 1,238 2,250 1,000 39,000 4,000 1,535 41,722 14,000 12,278 1,750 7,500 4,800 7 Budget 2,500 April 2022 South 4,000 7,478 2,200 750 100,00 1,000 45,000 54,000 1,238 2,25 4,000 39,00 14,000 1,535 41,722 1,750 7,500 2,500 12,278 4,800 4,000 7,478 8 Budget 2,200 100,000 750 May 2022 South 1,238 99,000 2,250 4,000 1,000 1,535 45,000 41,722 14,000 12,27 4,80 7,478 1,750 7,500 2,500 4,000 Budget June 2,200 2022 South 750 100,000 2,000 1,238 2,250 4,000 98,00 1,535 45,000 53,000 14,000 41,722 4,80 1,750 7,500 2,500 12,278 7,478 4,000 Budget July 2022 2,200 South 100,000 750 98,000 53,000 1,225 2,250 4,000 1,519 2,000 45,000 14,000 41,694 11,306 4,800 ,750 6,506 7,500 2,500 4,000 2,200 Budget August 2022 750 South 100,000 1,225 4,000 2,000 45,000 2,25 1,519 $8,00 1,694 53,00 14,000 11,306 4,80 1,750 7,500 2,500 5,506 4,000 Budget September 2022 South 2,200 750 100,000 1,225 2,000 98,000 45,000 2,25 4,000 53,000 1,519 41,694 14,000 11,306 4,800 1,750 7,500 3 Budget October 100,000 2,500 4,000 6,506 2022 South 98,000 2,200 750 45,000 1,2 25 2,000 53,000 2,250 4,000 14,000 1,519 41,694 11,306 4,800 1,750 7,500 2,500 6,506 4,000 Budget November 2,200 2022 South 100,000 2.000 750 8,00 1,225 45,000 2,250 4,000 53,000 1,519 41,694 14,000 7,500 2.500 11,306 4,800 6,506 1,750 4,000 65 Budget December 2,200 750 2022 South 100,000 1,225 2,250 2,000 4,000 98,000 45,000 14,000 1,519 53,000 41,694 11,306 4,80 6.506 1,750 7.500 2.500 4,000 2,200 750 1,200,000 19,000 1,225 1,181,000 2,250 540,000 641,000 168,000 4,000 1,519 21,000 41,694 11,306 4,800 90,000 6,506 30,000 48,000 26,400 9,000 14,763 27,000 48,000 18,306 500,468 140,532 57,600 82,932
