Excel

gangnanren
78a124434ca5cdcc.xlsx
Home>Computer Science homework help>Excel

2016 Yearly Sales Summary

2016 Yearly Sales Analysis
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Plain Bagles-Mini $9,067 $11,360 $12,722 $4,278
Plain Bagles-Standard $8,370 $8,570 $8,482 $17,111
Poppy Bagles-Mini $11,143 $12,262 $8,141 $10,372
Poppy Bagles-Standard $11,143 $18,393 $16,529 $22,039
Cream Cheese-2oz $15,095 $18,165 $13,677 $11,584
Cream Cheese-8oz $11,387 $5,426 $3,636 $6,238
Catering $34,224 $33,392 $23,933 $22,970
2016 Costs and Expenses
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Product Cost $27,421 $27,622 $21,525 $22,672
Overhead* $11,308 $11,688 $8,871 $9,041
Rent/Utilities $26,800 $26,800 $26,800 $26,800
Other** $4,500 $4,500 $4,500 $4,500
*Overhead includes plates, napkins, labels, labor and other costs associated with the product that cannot be traced
**includes set up and other costs

2016 Yearly Sales Analysis

1st Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 9067 8370 11143 11143 15095 11387 34224 2nd Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 11360 8570 12262 18393 18165 5426 33392 3rd Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 12722 8482 8141 16529 13677 3636 23933 4th Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 4278 17111 10372 22039 11584 6238 22970

2016 Costs and Expenses

Product Cost 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 27421 27622 21525 22672 Overhead* 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 11308 11688 8871 9041 Rent/Utilities 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 26800 26800 26800 26800 Other** 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 4500 4500 4500 4500

2017 Yearly Sales Summary

2017 Yearly Sales Analysis
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Plain Bagles-Mini $6,138 $12,770 $10,222 $12,300
Plain Bagles-Standard $15,784 $13,292 $10,222 $8,547
Poppy Bagles-Mini $15,541 $21,172 $16,106 $8,223
Poppy Bagles-Standard $10,360 $18,035 $13,178 $30,934
Cream Cheese-2oz $7,909 $20,314 $14,593 $16,118
Cream Cheese-8oz $22,510 $14,710 $11,940 $19,699
Catering $49,515 $31,940 $34,580 $49,244
2017 Costs and Expenses
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Product Cost $34,476 $34,852 $29,163 $38,760
Overhead* $15,484 $12,439 $11,975 $16,252
Rent/Utilities $26,800 $26,800 $26,800 $26,800
Other** $4,500 $4,500 $4,500 $4,500
*Overhead includes plates, napkins, labels, labor and other costs associated with the product that cannot be traced
**includes set up and other costs

2017 Yearly Sales Analysis

1st Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 6138.3167999999996 15784.243200000001 15540.624000000002 10360.415999999999 7909.0439999999999 22510.356 49514.520000000004 2nd Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 12770.419199999998 13291.660800000001 21171.585600000002 18035.054400000001 20313.664799999999 14709.895200000001 31940.04 3rd Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 10222.080000000002 10222.080000000002 16106.310000000001 13177.890000000001 14593.325999999999 11939.994000000001 34580.04 4th Qtr Plain Bagles-Mini Plain Bagles-Standard Poppy Bagles-Mini Poppy Bagles-Standard Cream Cheese-2oz Cream Cheese-8oz Catering 12299.588400000001 8547.1715999999997 8222.8608000000004 30933.619200000001 16117.596 19699.284000000003 49243.920000000006

2017 Costs and Expenses

Product Cost 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 34475.927833126349 34851.667902854606 29163.077442095535 38760.03029754234 Overhead* 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 15483.693806555846 12438.79821982823 11974.865627861756 16252.281092918001 Rent/Utilities 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 26800 26800 26800 26800 Other** 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 4500 4500 4500 4500

Sheet1

Values
Row Labels Sum of Amount Ordered Sum of Sale/Cost $ Value per Unit
COST 12 122929.47
OTHER 12 18000
OVERHEAD 12 43983.77
RENT/UTIL 12 107200
Grand Total 48 292113.24

Costs analysis 2018

Sum of Amount Ordered COST OTHER OVERHEAD RENT/UTIL 12 12 12 12 Sum of Sale/Cost $ Value per Unit COST OTHER OVERHEAD RENT/UTIL 122929.47 18000 43983.770000000011 107199.99999999999

Sheet2

Amount Ordered (All)
Row Labels Sum of Amount Ordered
Catering 6963
Cheese Cream 75566
Plain Bagel 176301
Poppy Bagel 208432
(blank) 48
Grand Total 467310

Amount Ordered per product

汇总 Catering Cheese Cream Plain Bagel Poppy Bagel (空白) 6963 75566 176301 208432 48

2018 Yearly Sales Summary

Month Transaction type Product (applies only to sales) Size (applies only to sales) Amount Ordered Sale/Cost $ Value per Unit Key to Table Data Description
1 SALE Plain Bagel Mini 4945 $ 0.60 SALE Daily Sales of Drinks
1 SALE Plain Bagel Standard 3263 $ 1.09 COST Cost of Product
1 SALE Poppy Bagel Mini 8681 $ 0.60 OVERHEAD Overhead
1 SALE Poppy Bagel Standard 9381 $ 1.09 RENT/UTIL Payment of Rent/Utilities
1 SALE Cheese Cream 2oz 5599 $ 0.92 OTHER Restructuring and other costs
1 SALE Cheese Cream 8oz 658 $ 3.69
1 SALE Catering 12 Standard & 8oz 507 $ 14.25
1 COST 1 $ 8,855.14
1 OVERHEAD 1 $ 3,210.13
1 RENT/UTIL 1 $ 8,933.33
1 OTHER 1 $ 1,500.00
2 SALE Plain Bagel Mini 4311 $ 0.60
2 SALE Plain Bagel Standard 5636 $ 1.09
2 SALE Poppy Bagel Mini 7688 $ 0.60
2 SALE Poppy Bagel Standard 6846 $ 1.09
2 SALE Cheese Cream 2oz 4737 $ 0.92
2 SALE Cheese Cream 8oz 888 $ 3.69
2 SALE Catering 12 Standard & 8oz 370 $ 14.25
2 COST 1 $ 8,179.62
2 OVERHEAD 1 $ 2,628.68
2 RENT/UTIL 1 $ 8,933.33
2 OTHER 1 $ 1,500.00
3 SALE Plain Bagel Mini 3424 $ 0.60
3 SALE Plain Bagel Standard 5933 $ 1.09
3 SALE Poppy Bagel Mini 8433 $ 0.60
3 SALE Poppy Bagel Standard 9127 $ 1.09
3 SALE Cheese Cream 2oz 11198 $ 0.92
3 SALE Cheese Cream 8oz 1745 $ 3.69
3 SALE Catering 12 Standard & 8oz 494 $ 14.25
3 COST 1 $ 13,218.34
3 OVERHEAD 1 $ 3,471.40
3 RENT/UTIL 1 $ 8,933.33
3 OTHER 1 $ 1,500.00
4 SALE Plain Bagel Mini 10100 $ 0.60
4 SALE Plain Bagel Standard 3861 $ 1.09
4 SALE Poppy Bagel Mini 12159 $ 0.60
4 SALE Poppy Bagel Standard 6881 $ 1.09
4 SALE Cheese Cream 2oz 2692 $ 0.92
4 SALE Cheese Cream 8oz 888 $ 3.69
4 SALE Catering 12 Standard & 8oz 585 $ 14.25
4 COST 1 $ 8,850.72
4 OVERHEAD 1 $ 3,585.69
4 RENT/UTIL 1 $ 8,933.33
4 OTHER 1 $ 1,500.00
5 SALE Plain Bagel Mini 15992 $ 0.60
5 SALE Plain Bagel Standard 3243 $ 1.09
5 SALE Poppy Bagel Mini 10063 $ 0.60
5 SALE Poppy Bagel Standard 5898 $ 1.09
5 SALE Cheese Cream 2oz 2619 $ 0.92
5 SALE Cheese Cream 8oz 1333 $ 3.69
5 SALE Catering 12 Standard & 8oz 585 $ 14.25
5 COST 1 $ 9,675.09
5 OVERHEAD 1 $ 3,617.69
5 RENT/UTIL 1 $ 8,933.33
5 OTHER 1 $ 1,500.00
6 SALE Plain Bagel Mini 15992 $ 0.60
6 SALE Plain Bagel Standard 8340 $ 1.09
6 SALE Poppy Bagel Mini 19707 $ 0.60
6 SALE Poppy Bagel Standard 6882 $ 1.09
6 SALE Cheese Cream 2oz 1965 $ 0.92
6 SALE Cheese Cream 8oz 1817 $ 3.69
6 SALE Catering 12 Standard & 8oz 718 $ 14.25
6 COST 1 $ 12,706.46
6 OVERHEAD 1 $ 4,797.10
6 RENT/UTIL 1 $ 8,933.33
6 OTHER 1 $ 1,500.00
7 SALE Plain Bagel Mini 8923 $ 0.60
7 SALE Plain Bagel Standard 4173 $ 1.09
7 SALE Poppy Bagel Mini 7145 $ 0.60
7 SALE Poppy Bagel Standard 5956 $ 1.09
7 SALE Cheese Cream 2oz 3549 $ 0.92
7 SALE Cheese Cream 8oz 1180 $ 3.69
7 SALE Catering 12 Standard & 8oz 683 $ 14.25
7 COST 1 $ 9,342.47
7 OVERHEAD 1 $ 3,699.07
7 RENT/UTIL 1 $ 8,933.33
7 OTHER 1 $ 1,500.00
8 SALE Plain Bagel Mini 8237 $ 0.60
8 SALE Plain Bagel Standard 6183 $ 1.09
8 SALE Poppy Bagel Mini 6086 $ 0.60
8 SALE Poppy Bagel Standard 9063 $ 1.09
8 SALE Cheese Cream 2oz 2957 $ 0.92
8 SALE Cheese Cream 8oz 1217 $ 3.69
8 SALE Catering 12 Standard & 8oz 726 $ 14.25
8 COST 1 $ 10,011.34
8 OVERHEAD 1 $ 4,058.38
8 RENT/UTIL 1 $ 8,933.33
8 OTHER 1 $ 1,500.00
9 SALE Plain Bagel Mini 17161 $ 0.60
9 SALE Plain Bagel Standard 5101 $ 1.09
9 SALE Poppy Bagel Mini 13231 $ 0.60
9 SALE Poppy Bagel Standard 10876 $ 1.09
9 SALE Cheese Cream 2oz 8281 $ 0.92
9 SALE Cheese Cream 8oz 1290 $ 3.69
9 SALE Catering 12 Standard & 8oz 726 $ 14.25
9 COST 1 $ 14,101.23
9 OVERHEAD 1 $ 4,831.41
9 RENT/UTIL 1 $ 8,933.33
9 OTHER 1 $ 1,500.00
10 SALE Plain Bagel Mini 3064 $ 0.60
10 SALE Plain Bagel Standard 6723 $ 1.09
10 SALE Poppy Bagel Mini 9805 $ 0.60
10 SALE Poppy Bagel Standard 6775 $ 1.09
10 SALE Cheese Cream 2oz 5109 $ 0.92
10 SALE Cheese Cream 8oz 313 $ 3.69
10 SALE Catering 12 Standard & 8oz 565 $ 14.25
10 COST 1 $ 8,354.98
10 OVERHEAD 1 $ 3,379.45
10 RENT/UTIL 1 $ 8,933.33
10 OTHER 1 $ 1,500.00
11 SALE Plain Bagel Mini 4086 $ 0.60
11 SALE Plain Bagel Standard 7261 $ 1.09
11 SALE Poppy Bagel Mini 9805 $ 0.60
11 SALE Poppy Bagel Standard 3744 $ 1.09
11 SALE Cheese Cream 2oz 4127 $ 0.92
11 SALE Cheese Cream 8oz 508 $ 3.69
11 SALE Catering 12 Standard & 8oz 596 $ 14.25
11 COST 1 $ 8,063.85
11 OVERHEAD 1 $ 3,347.84
11 RENT/UTIL 1 $ 8,933.33
11 OTHER 1 $ 1,500.00
12 SALE Plain Bagel Mini 7442 $ 0.60
12 SALE Plain Bagel Standard 12907 $ 1.09
12 SALE Poppy Bagel Mini 6890 $ 0.60
12 SALE Poppy Bagel Standard 7310 $ 1.09
12 SALE Cheese Cream 2oz 10415 $ 0.92
12 SALE Cheese Cream 8oz 481 $ 3.69
12 SALE Catering 12 Standard & 8oz 408 $ 14.25
12 COST 1 $ 11,570.23
12 OVERHEAD 1 $ 3,356.93
12 RENT/UTIL 1 $ 8,933.33
12 OTHER 1 $ 1,500.00

Sheet3

Amount Ordered (All)
Row Labels Sum of Amount Ordered
Catering 7957
Cheese Cream 89147
Plain Bagel 180821
Poppy Bagel 269375
(blank) 48
Grand Total 547348

Sales per Product in 2019

汇总 Catering Cheese Cream Plain Bagel Poppy Bagel (空白) 7957 89147 180821 269375 48

Sheet4

Row Labels Sum of Sale/Cost $ Value per Unit
COST 149544.07
OTHER 18000
OVERHEAD 51282.8
RENT/UTIL 107200
SALE 266.88
Grand Total 326293.75

Costs Analysis 2019

汇总 COST OTHER OVERHEAD RENT/UTIL SALE 149544.07 18000 51282.8 107199.99999999999 266.87999999999988

2019 Yearly Sales Summary

Month Transaction type Product (applies only to sales) Size (applies only to sales) Amount Ordered Sale/Cost $ Value per Unit Key to Table Data Description
1 SALE Plain Bagel Mini 9757 $ 0.60 SALE Daily Sales of Drinks
1 SALE Plain Bagel Standard 4041 $ 1.09 COST Cost of Product
1 SALE Poppy Bagel Mini 8137 $ 0.60 OVERHEAD Overhead
1 SALE Poppy Bagel Standard 7641 $ 1.09 RENT/UTIL Payment of Rent/Utilities
1 SALE Cheese Cream 2oz 4640 $ 0.92 OTHER Restructuring and other costs
1 SALE Cheese Cream 8oz 1301 $ 3.69
1 SALE Catering 12 Standard & 8oz 321 $ 14.25
1 COST 1 $ 9,103.94
1 OVERHEAD 1 $ 2,632.47
1 RENT/UTIL 1 $ 8,933.33
1 OTHER 1 $ 1,500.00
2 SALE Plain Bagel Mini 8257 $ 0.60
2 SALE Plain Bagel Standard 5174 $ 1.09
2 SALE Poppy Bagel Mini 6164 $ 0.60
2 SALE Poppy Bagel Standard 9485 $ 1.09
2 SALE Cheese Cream 2oz 3907 $ 0.92
2 SALE Cheese Cream 8oz 818 $ 3.69
2 SALE Catering 12 Standard & 8oz 354 $ 14.25
2 COST 1 $ 8,266.19
2 OVERHEAD 1 $ 2,735.54
2 RENT/UTIL 1 $ 8,933.33
2 OTHER 1 $ 1,500.00
3 SALE Plain Bagel Mini 7007 $ 0.60
3 SALE Plain Bagel Standard 6952 $ 1.09
3 SALE Poppy Bagel Mini 10357 $ 0.60
3 SALE Poppy Bagel Standard 9221 $ 1.09
3 SALE Cheese Cream 2oz 3662 $ 0.92
3 SALE Cheese Cream 8oz 1599 $ 3.69
3 SALE Catering 12 Standard & 8oz 931 $ 14.25
3 COST 1 $ 12,173.49
3 OVERHEAD 1 $ 5,005.64
3 RENT/UTIL 1 $ 8,933.33
3 OTHER 1 $ 1,500.00
4 SALE Plain Bagel Mini 12898 $ 0.60
4 SALE Plain Bagel Standard 5823 $ 1.09
4 SALE Poppy Bagel Mini 22142 $ 0.60
4 SALE Poppy Bagel Standard 6470 $ 1.09
4 SALE Cheese Cream 2oz 6602 $ 0.92
4 SALE Cheese Cream 8oz 2312 $ 3.69
4 SALE Catering 12 Standard & 8oz 774 $ 14.25
4 COST 1 $ 15,130.09
4 OVERHEAD 1 $ 5,020.87
4 RENT/UTIL 1 $ 8,933.33
4 OTHER 1 $ 1,500.00
5 SALE Plain Bagel Mini 6448 $ 0.60
5 SALE Plain Bagel Standard 6138 $ 1.09
5 SALE Poppy Bagel Mini 20946 $ 0.60
5 SALE Poppy Bagel Standard 5953 $ 1.09
5 SALE Cheese Cream 2oz 5777 $ 0.92
5 SALE Cheese Cream 8oz 2383 $ 3.69
5 SALE Catering 12 Standard & 8oz 464 $ 14.25
5 COST 1 $ 12,950.73
5 OVERHEAD 1 $ 3,659.48
5 RENT/UTIL 1 $ 8,933.33
5 OTHER 1 $ 1,500.00
6 SALE Plain Bagel Mini 12898 $ 0.60
6 SALE Plain Bagel Standard 3777 $ 1.09
6 SALE Poppy Bagel Mini 16756 $ 0.60
6 SALE Poppy Bagel Standard 13459 $ 1.09
6 SALE Cheese Cream 2oz 4127 $ 0.92
6 SALE Cheese Cream 8oz 2312 $ 3.69
6 SALE Catering 12 Standard & 8oz 976 $ 14.25
6 COST 1 $ 15,143.74
6 OVERHEAD 1 $ 5,755.22
6 RENT/UTIL 1 $ 8,933.33
6 OTHER 1 $ 1,500.00
7 SALE Plain Bagel Mini 7124 $ 0.60
7 SALE Plain Bagel Standard 7196 $ 1.09
7 SALE Poppy Bagel Mini 18132 $ 0.60
7 SALE Poppy Bagel Standard 5536 $ 1.09
7 SALE Cheese Cream 2oz 5617 $ 0.92
7 SALE Cheese Cream 8oz 1298 $ 3.69
7 SALE Catering 12 Standard & 8oz 601 $ 14.25
7 COST 1 $ 11,465.54
7 OVERHEAD 1 $ 3,993.83
7 RENT/UTIL 1 $ 8,933.33
7 OTHER 1 $ 1,500.00
8 SALE Plain Bagel Mini 10960 $ 0.60
8 SALE Plain Bagel Standard 5398 $ 1.09
8 SALE Poppy Bagel Mini 11734 $ 0.60
8 SALE Poppy Bagel Standard 4872 $ 1.09
8 SALE Cheese Cream 2oz 8113 $ 0.92
8 SALE Cheese Cream 8oz 1189 $ 3.69
8 SALE Catering 12 Standard & 8oz 560 $ 14.25
8 COST 1 $ 11,503.48
8 OVERHEAD 1 $ 3,647.00
8 RENT/UTIL 1 $ 8,933.33
8 OTHER 1 $ 1,500.00
9 SALE Plain Bagel Mini 9315 $ 0.60
9 SALE Plain Bagel Standard 7399 $ 1.09
9 SALE Poppy Bagel Mini 23467 $ 0.60
9 SALE Poppy Bagel Standard 11739 $ 1.09
9 SALE Cheese Cream 2oz 7073 $ 0.92
9 SALE Cheese Cream 8oz 1117 $ 3.69
9 SALE Catering 12 Standard & 8oz 840 $ 14.25
9 COST 1 $ 14,503.68
9 OVERHEAD 1 $ 5,522.80
9 RENT/UTIL 1 $ 8,933.33
9 OTHER 1 $ 1,500.00
10 SALE Plain Bagel Mini 4562 $ 0.60
10 SALE Plain Bagel Standard 6391 $ 1.09
10 SALE Poppy Bagel Mini 6574 $ 0.60
10 SALE Poppy Bagel Standard 10830 $ 1.09
10 SALE Cheese Cream 2oz 4694 $ 0.92
10 SALE Cheese Cream 8oz 1613 $ 3.69
10 SALE Catering 12 Standard & 8oz 449 $ 14.25
10 COST 1 $ 10,392.77
10 OVERHEAD 1 $ 3,194.40
10 RENT/UTIL 1 $ 8,933.33
10 OTHER 1 $ 1,500.00
11 SALE Plain Bagel Mini 4562 $ 0.60
11 SALE Plain Bagel Standard 9741 $ 1.09
11 SALE Poppy Bagel Mini 6086 $ 0.60
11 SALE Poppy Bagel Standard 10830 $ 1.09
11 SALE Cheese Cream 2oz 4490 $ 0.92
11 SALE Cheese Cream 8oz 1906 $ 3.69
11 SALE Catering 12 Standard & 8oz 748 $ 14.25
11 COST 1 $ 12,432.31
11 OVERHEAD 1 $ 4,403.27
11 RENT/UTIL 1 $ 8,933.33
11 OTHER 1 $ 1,500.00
12 SALE Plain Bagel Mini 4699 $ 0.60
12 SALE Plain Bagel Standard 14304 $ 1.09
12 SALE Poppy Bagel Mini 11689 $ 0.60
12 SALE Poppy Bagel Standard 11155 $ 1.09
12 SALE Cheese Cream 2oz 11226 $ 0.92
12 SALE Cheese Cream 8oz 1371 $ 3.69
12 SALE Catering 12 Standard & 8oz 939 $ 14.25
12 COST 1 $ 16,478.11
12 OVERHEAD 1 $ 5,712.28
12 RENT/UTIL 1 $ 8,933.33
12 OTHER 1 $ 1,500.00

Business Model

Product Size Cost per Unit Overhead Retail Price Profit per unit
Plain Bagel Mini $ 0.10 $ 0.03 $ 0.60 $ 0.47
Plain Bagel Standard $ 0.17 $ 0.05 $ 1.09 $ 0.87
Poppy Bagel Mini $ 0.10 $ 0.04 $ 0.60 $ 0.46
Poppy Bagel Standard $ 0.17 $ 0.06 $ 1.09 $ 0.86
Cream Cheese 2oz $ 0.42 $ 0.03 $ 0.92 $ 0.47
Cream Cheese 8oz $ 1.68 $ 0.07 $ 3.69 $ 1.94
Catering 12 Standard &8oz Cream Cheese $ 3.72 $ 3.50 $ 14.25 $ 7.03
Row Labels
Catering
Cream Cheese
Cream Cheese Retail Price (All)
Plain Bagel
Poppy Bagel Row Labels Sum of Retail Price
Grand Total Catering 14.25
Cream Cheese 0.92
Cream Cheese 3.69
Plain Bagel 1.69
Poppy Bagel 1.69
Grand Total 22.24

Best returns per Unit

汇总 Catering Cream Cheese Cream Cheese Plain Bagel Poppy Bagel 14.25 0.92 3.69 1.69 1.69