Financial Modelling

profilezyber
AdditionalInformationforModelCaseStudy-Salesreports2022_v0.1.xlsx
Home>Business & Finance homework help>Financial Modelling

Sales by category and channel

Sales Report 2022 Sales Report 2022
By product category By sales channel
Category Sub-category Gross sales value (RRP) Discounts Net sales value COGS Gross margin Channel Sub-category Gross sales value (RRP) Discounts Net sales value COGS Gross margin
Clothing Retail sales (stores)
Men 25,164,218 -4,537,810 20,626,409 -9,075,620 11,550,789 Clothing 38,447,316 -6,102,196 32,345,120 -14,511,864 17,833,256
Womens 21,826,802 -2,943,642 18,883,160 -8,692,095 10,191,064 Footwear 12,891,326 -1,636,211 11,255,114 -5,383,384 5,871,730
Childrens 5,569,130 -412,528 5,156,602 -1,871,847 3,284,756 Accessories 31,260,245 -7,492,743 23,767,502 -12,728,274 11,039,228
Clothing total 52,560,151 -7,893,980 44,666,170 -19,639,562 25,026,609 Retail total 82,598,886 -15,231,150 67,367,736 -32,623,522 34,744,215 51.57%
74.73%
Footwear Online
Men 7,152,892 -878,425 6,274,467 -2,948,999 3,325,467 Clothing 3,597,609 -757,694 2,839,915 -1,265,432 1,574,483
Womens 6,671,850 -920,255 5,751,595 -2,588,218 3,163,377 Footwear 545,232 -118,868 426,364 -222,077 204,287
Childrens 1,150,319 -104,574 1,045,744 -418,298 627,447 Accessories 1,655,748 -328,885 1,326,862 -763,431 563,431
Footwear total 14,975,061 -1,903,255 13,071,806 -5,955,515 7,116,291 Online total 5,798,589 -1,205,447 4,593,141 -2,250,940 2,342,201 50.99%
5.09%
Accessories Corporate and clubs
Camping and hiking 21,628,728 -4,412,012 17,216,716 -9,250,525 7,966,191 Clothing 10,515,226 -1,034,091 9,481,135 -3,862,265 5,618,870
Packs and bags 11,616,553 -1,515,203 10,101,351 -5,343,581 4,757,770 Footwear 1,538,504 -148,176 1,390,328 -350,054 1,040,274
Travel gear 7,939,251 -2,844,907 5,094,344 -2,904,154 2,190,190 Accessories 8,268,540 -950,494 7,318,046 -4,006,555 3,311,491
Accessories total 41,184,532 -8,772,122 32,412,411 -17,498,260 14,914,151 Corporate and clubs total 20,322,269 -2,132,760 18,189,510 -8,218,874 9,970,635 54.82%
20.18%
Total 108,719,744 -18,569,357 90,150,387 -43,093,336 47,057,051 Total 108,719,744 -18,569,357 90,150,387 -43,093,336 47,057,051
2023 Budget Assumptions
Channel Revenue GM 2022 2021
Retail 75.00% 70,875 38,273 54.00% 51.57%
Online 5.00% 4,725 2,552 54.00% 50.99%
Corp/clubs 20.00% 18,900 10,276 54.37% 54.82%
94,500 51,100 54.07%

Store closures

Store name  2024 Planned Closure date  2023 Budget Revenue  2023 Budget Gross margin 
Bass Coast 5/31/24 452,076 216,996
Orange 6/15/24 624,900 287,454
Alice Springs 4/30/24 756,204 370,553
Sydney - Auburn 7/1/24 1,490,324 760,238
Townsville 9/30/24 956,213 478,107
Tauranga 8/1/24 784,529 381,429
Melbourne - Chadstone 10/1/24 1,634,890 784,747
Greenhills 11/1/24 653,078 326,539
Mildura 7/15/24 590,342 295,835
Perisher 9/1/24 402,389 285,104

Sheet1

Sales report 2021 By product category
Category Sub-category Gross sales value (RRP) Discounts Net sales value COGS Gross margin GM% Products in category
Clothing
Men 25,164,218.43 - 4,537,809.88 20,626,408.55 - 9,075,619.76 11,550,788.79 56% 659
Womens 15,826,801.94 - 1,943,642.34 13,883,159.60 - 5,692,095.44 8,191,064.16 59% 1036
Childrens 5,569,130.31 - 412,528.17 5,156,602.14 - 1,871,846.58 3,284,755.56 64% 112
44% Clothing total 46,560,150.67 - 6,893,980.39 39,666,170.28 - 16,639,561.77 23,026,608.51 58%
-15%
Footwear
Men 7,152,892.31 - 878,425.37 6,274,466.94 - 2,948,999.46 3,325,467.48 53% 39
Womens 6,671,849.84 - 920,255.15 5,751,594.69 - 2,588,217.61 3,163,377.08 55% 28
Childrens 1,150,318.94 - 104,574.45 1,045,744.49 - 418,297.80 627,446.69 60% 12
15% Footwear total 14,975,061.09 - 1,903,254.97 13,071,806.11 - 5,955,514.87 7,116,291.25 54%
-13%
Accessories
Camping & hiking 17,628,727.88 - 3,412,011.85 14,216,716.03 - 7,250,525.18 6,966,190.85 49% 560
Packs & bags 11,616,553.49 - 1,515,202.63 10,101,350.86 - 4,343,580.87 5,757,769.99 57% 176
Travel gear 17,939,250.89 - 4,844,907.17 13,094,343.71 - 8,904,153.72 4,190,189.99 32% I need to make this lower. 236
42% Accessories total 47,184,532.26 - 9,772,121.65 37,412,410.61 - 20,498,259.77 16,914,150.83 45%
-21%
Total 108,719,744.02 - 18,569,357.02 90,150,387.00 - 43,093,336.41 47,057,050.59 52% 2858 2858
-17% -48%
Sales report 2021 By sales channel
Clothing 46,560,150.68 - 6,893,980.40 39,666,170.28 - 16,639,561.77 23,026,608.51 58%
Footwear 14,975,061.08 - 1,903,254.97 13,071,806.11 - 5,955,514.87 7,116,291.25 54%
Accessories 47,184,532.26 - 9,772,121.65 37,412,410.60 - 20,498,259.77 16,914,150.83 45%
108,719,744.02 - 18,569,357.02 90,150,387.00 - 43,093,336.41 47,057,050.59 52%
-17%
Retail sales
44% Clothing 35,847,316.02 - 6,002,195.62 29,845,120.40 - 12,511,864.28 17,333,256.12 58%
18% Footwear 13,491,325.52 - 1,721,017.59 11,770,307.93 - 5,383,383.86 6,386,924.07 54% Sales GM GM%
38% Accessories 34,260,244.51 - 8,392,742.59 25,867,501.92 - 14,728,273.62 11,139,228.30 43%
74.9% Retail total 83,598,886.05 - 16,115,955.80 67,482,930.25 - 32,623,521.77 34,859,408.48 52% 1,405,894.38
77% -19% 75% -48% 74%
Online 402,389 213,266 53%
63% Clothing 3,197,609.04 - 357,694.22 2,839,914.82 - 1,265,432.11 1,574,482.71 55%
10% Footwear 545,231.90 - 118,867.92 426,363.98 - 222,077.13 204,286.85 48%
25% Accessories 1,655,747.56 - 528,885.32 1,126,862.24 - 563,431.12 563,431.12 50%
4.9% Online total 5,398,588.51 - 1,005,447.46 4,393,141.05 - 2,050,940.37 2,342,200.68 53%
5% -19% 5% -47% 5%
Corporate & Clubs
38% Clothing 7,515,225.61 - 534,090.55 6,981,135.06 - 2,862,265.37 4,118,869.69 59%
5% Footwear 938,503.67 - 63,369.47 875,134.20 - 350,053.87 525,080.33 60%
60% Accessories 11,268,540.18 - 850,493.74 10,418,046.44 - 5,206,555.02 5,211,491.42 50%
20.3% Corporate & Clubs total 19,722,269.46 - 1,447,953.76 18,274,315.70 - 8,418,874.27 9,855,441.43 54%
18% -7% 20% -46% 21%
Total revenue 108,719,744.02 - 18,569,357.02 90,150,387.00 - 43,093,336.41 47,057,050.59 52%