U.S.
2020 Sales: United States
Type Q1 Q2 Q3 Q4 Total
Mini $ 986 $ 985 $ 988 $ 982 $ 3,941
Voice-activated 801 805 806 802 $ 3,214
Waterproof 682 687 684 688 $ 2,741
Total $ 2,469 $ 2,477 $ 2,478 $ 2,472 $ 9,896
in $000
Canada
2020 Sales: Canada
Type Q1 Q2 Q3 Q4 Total
Mini $ 584 $ 588 $ 588 $ 589 $ 2,349
Voice-activated 414 409 411 412 $ 1,646
Waterproof 390 390 392 394 $ 1,566
Total $ 1,388 $ 1,387 $ 1,391 $ 1,395 $ 5,561
in $000
Mexico
2020 Sales: Mexico
Type Q1 Q2 Q3 Q4 Total
Mini $ 720 $ 724 $ 722 $ 718 $ 2,884
Voice-activated 294 295 297 297 $ 1,183
Waterproof 445 443 448 446 $ 1,782
Total $ 1,459 $ 1,462 $ 1,467 $ 1,461 $ 5,849
in $000
All Locations
2020 Sales: All Locations
Type Q1 Q2 Q3 Q4 Total
Mini $ 2,290 $ 2,297 $ 2,298 $ 2,289 $ 9,174
Voice-activated $ 1,509 $ 1,509 $ 1,514 $ 1,511 $ 6,043
Waterproof $ 1,517 $ 1,520 $ 1,524 $ 1,528 $ 6,089
Total $ 5,316 $ 5,326 $ 5,336 $ 5,328 $ 21,306
in $000
DIG Portable Speakers
National Managers
United States
Todd Fenton
Knoxville, TN 37919
XXXXXXXXXX
Canada
Natalie Pie
e
Windsor, ON N8N OA6
XXXXXXXXXX
Mexico
Vicente Medina
Partido Senecu, Ciudad Juarez, CP 32543
XXXXXXXXXX
Documentation
New Perspectives Excel 2019 | Modules 5-8: SAM Capstone Project 1a
NewSight Consulting
ANALYZE DATA WITH TABLES AND WHAT-IF TOOLS
Author: Wood Balan
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.
U.S.
2021 Sales: United States
Type Q1 Q2 Q3 Q4 Total
Mini $ 1,021 $ 1,025 $ 1,076 $ 1,027
Voice-activated 987 990 1,015 1,016
Waterproof 815 826 834 859
Total $ 2,823 $ 2,841 $ 2,925 $ 2,902 $ - 0
in $000
Canada
2021 Sales: Canada
Type Q1 Q2 Q3 Q4 Total
Mini $ 604 $ 609 $ 601 $ 602
Voice-activated 497 495 502 523
Waterproof 429 423 431 435
Total $ 1,530 $ 1,527 $ 1,534 $ 1,560 $ - 0
in $000
Mexico
2021 Sales: Mexico
Type Q1 Q2 Q3 Q4 Total
Mini $ 741 $ 740 $ 744 $ 750
Voice-activated 328 312 327 324
Waterproof 504 506 499 503
Total $ 1,573 $ 1,558 $ 1,570 $ 1,577 $ - 0
in $000
All Locations
2021 Sales: All Locations Managers
Type Q1 Q2 Q3 Q4 Total 2020 Total Country Name Location Email Address
Mini U.S. Todd Fenton Knoxville XXXXXXXXXX
Voice-activated Canada Natalie Pie
e Windsor npie
XXXXXXXXXX
Waterproof Mexico Vicente Medina Juarez XXXXXXXXXX
Total $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Management Details
in $000
Performance
Revenue Amts $ 5,700 $ 5,800 $ 5,900 $ 6,000 $ 6,100
Rating Poor Fair Good Very good Fantastic `
mailto:npie
XXXXXXXXXX: XXXXXXXXXX
Cu
ent Sales
Monthly Sales: January 2022 Product Details
Sale ID Channel Type Date Product Location Amount Promotion? Product ID Name Color No. of Speakers Retail Price
DW-1033 DIG website 1/3/22 Mini 2 U.S. 129.99 M2016 Mini 1 Black 1 119.99
DW-1039 DIG website 1/4/22 Mini 1 Canada 119.99 M2018 Mini 1 Silver 1 119.99
DW-1040 DIG website 1/4/22 Voice XP10 U.S. 145.95 M2020 Mini 1 White 1 119.99
DW-1049 DIG website 1/7/22 Mini 2 U.S. 129.99 M2205 Mini 2 Black 2 129.99
DW-1053 DIG website 1/8/22 Mini 1 U.S. 119.99 M2210 Mini 2 Silver 2 129.99
DW-1059 DIG website 1/13/22 Mini 1 U.S. 119.99 M2215 Mini 2 White 2 129.99
DW-1061 DIG website 1/18/22 Mini 2 U.S. 129.99 V3010 Voice XP Black 2 139.99
DW-1062 DIG website 1/22/22 Waterproof Mexico 135.95 V3012 Voice XP White 2 139.99
DW-1065 DIG website 1/26/22 Voice XP Canada 139.99 V3220 Voice XP 10 Black 3 145.95
DW-1066 DIG website 1/30/22 Waterproof U.S. 135.95 V3225 Voice XP 10 White 3 145.95
DW-1070 DIG website 1/6/22 Voice XP10 Canada 145.95 W4150 Waterproof Black 2 135.95
DW-1071 DIG website 1/17/22 Mini 2 U.S. 129.99 W4155 Waterproof White 2 135.95
OW-1041 Other website 1/3/22 Voice XP U.S. 139.99
OW-1047 Other website 1/5/22 Waterproof Mexico 135.95
OW-1102 Other website 1/10/22 Voice XP Mexico 139.99
OW-1102 Other website 1/10/22 Voice XP Mexico 139.99
OW-1104 Other website 1/11/22 Mini 2 U.S. 129.99
OW-1108 Other website 1/12/22 Waterproof U.S. 135.95
OW-1123 Other website 1/15/22 Voice XP U.S. 139.99
OW-1126 Other website 1/16/22 Voice XP10 U.S. 145.95
OW-1130 Other website 1/17/22 Mini 2 U.S. 129.99
OW-1132 Other website 1/19/22 Voice XP Canada 139.99
OW-1134 Other website 1/20/22 Mini 1 U.S. 119.99
OW-1139 Other website 1/21/22 Mini 1 U.S. 119.99
OW-1141 Other website 1/25/22 Mini 2 U.S. 129.99
OW-1144 Other website 1/27/22 Mini 1 U.S. 119.99
OW-1145 Other website 1/28/22 Mini 2 U.S. 129.99
OW-1148 Other website 1/29/22 Voice XP U.S. 139.99
OW-1150 Other website 1/12/22 Mini 1 U.S. 119.99
OW-1155 Other website 1/20/22 Mini 1 U.S. 119.99
ST-1134 Store 1/5/22 Mini 1 Mexico 119.99
ST-1142 Store 1/7/22 Voice XP Canada 139.99
ST-1148 Store 1/14/22 Mini 1 Canada 119.99
ST-1151 Store 1/23/22 Voice XP10 U.S. 145.95
ST-1152 Store 1/24/22 Mini 2 U.S. 129.99
ST-1153 Store 1/31/22 Mini 2 U.S. 129.99
Sales Projections
New Product: Virtual Assistant Virtual Assistant: Gross Profit Analysis
Sales $ 294,780.00 $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX
Units sold 22,000 18,000
Price per unit $ XXXXXXXXXX 20,000
Total sales $ 3,519,780.00 22,000
24,000
Expenses 26,000
Variable expenses
Units manufactured 22,000
Variable cost per unit $ XXXXXXXXXX
Total variable costs $ 2,970,000.00
Fixed Expenses
Total fixed cost $ 255,000.00
Total expenses $ 3,225,000.00
Total expense per unit sold $ XXXXXXXXXX
Profit
Total sales $ 3,519,780.00
Total expenses $ 3,225,000.00
Gross profit $ 294,780.00
Suppliers
Virtual Assistant Suppliers
Jenji Manufacturing AS&P Electronics Hancock Global Total
Units Produced 3,900 3,600 4,500 12,000
Fixed costs $ 70,500.00 $ 74,625.00 $ 84,120.00
Fixed costs per unit 18.08 20.73 18.69
Variable costs 234,975.00 218,700.00 272,250.00
Variable cost per unit 60.25 60.75 60.50
Total costs $ 305,475.00 $ 293,325.00 $ 356,370.00 $ 955,170.00
Minimum total cost model
Product Mix
Mini 1 Mini 2 Voice XP Voice XP10 Waterproof
Revenue
Units sold 20,150 17,025 13,530 12,945 10,750
Price per unit $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX
Total sales $ 2,417,798.50 $ 2,213,079.75 $ 1,894,064.70 $ 1,889,322.75 $ 1,461,462.50
Cost of goods sold
Units manufactured 20,150 17,025 13,530 12,945 10,750
Fixed costs $ 265,000.00 $ 270,000.00 $ 279,000.00 $ 282,000.00 $ 280,000.00
Variable cost per unit $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX $ XXXXXXXXXX
Variable costs $ 1,914,250.00 $ 1,787,625.00 $ 1,488,300.00 $ 1,488,675.00 $ 1,096,500.00
Total costs $ 2,179,250.00 $ 2,057,625.00 $ 1,767,300.00 $ 1,770,675.00 $ 1,376,500.00
Total profit $ 238,549 $ 155,455 $ 126,765 $ 118,648 $ 84,962
Profit per unit sold $11.84 $9.13 $9.37 $9.17 $7.90