Excel project
U.S. 2020 Sales: United States TypeQ1Q2Q3Q4Total Mini$ 986$ 985$ 988$ 982$ 3,941 Voice-activated801805806802$ 3,214 Waterproof682687684688$ 2,741 Total$ 2,469$ 2,477$ 2,478$ 2,472$ 9,896 in $000 Canada 2020 Sales: Canada TypeQ1Q2Q3Q4Total Mini$ 584$ 588$ 588$ 589$ 2,349 Voice-activated414409411412$ 1,646 Waterproof390390392394$ 1,566 Total$ 1,388$ 1,387$ 1,391$ 1,395$ 5,561 in $000 Mexico 2020 Sales: Mexico TypeQ1Q2Q3Q4Total Mini$ 720$ 724$ 722$ 718$ 2,884 Voice-activated294295297297$ 1,183 Waterproof445443448446$ 1,782 Total$ 1,459$ 1,462$ 1,467$ 1,461$ 5,849 in $000 All Locations 2020 Sales: All Locations TypeQ1Q2Q3Q4Total 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 (865) 555-2771 Canada Natalie Pierre Windsor, ON N8N OA6 (519) 555-3135 Mexico Vicente Medina Partido Senecu, Ciudad Juarez, CP 32543 (656) 555-0228 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 TypeQ1Q2Q3Q4Total Mini$ 1,021$ 1,025$ 1,076$ 1,027 Voice-activated9879901,0151,016 Waterproof815826834859 Total$ 2,823$ 2,841$ 2,925$ 2,902$ - 0 in $000 Canada 2021 Sales: Canada TypeQ1Q2Q3Q4Total Mini$ 604$ 609$ 601$ 602 Voice-activated497495502523 Waterproof429423431435 Total$ 1,530$ 1,527$ 1,534$ 1,560$ - 0 in $000 Mexico 2021 Sales: Mexico TypeQ1Q2Q3Q4Total Mini$ 741$ 740$ 744$ 750 Voice-activated328312327324 Waterproof504506499503 Total$ 1,573$ 1,558$ 1,570$ 1,577$ - 0 in $000 All Locations 2021 Sales: All LocationsManagers TypeQ1Q2Q3Q4Total2020 TotalCountryNameLocationEmail Address MiniU.S.Todd
[email protected] Voice-activatedCanadaNatalie
[email protected] WaterproofMexicoVicente
[email protected] Total$ - 0$ - 0$ - 0$ - 0$ - 0 Management Details in $000 Performance Revenue Amts$ 5,700$ 5,800$ 5,900$ 6,000$ 6,100 RatingPoorFairGoodVery goodFantastic` mailto:
[email protected]:
[email protected] Current Sales Monthly Sales: January 2022Product Details Sale IDChannel TypeDateProductLocationAmountPromotion?Product IDNameColorNo. of SpeakersRetail Price DW-1033DIG website1/3/22Mini 2U.S.129.99M2016Mini 1Black1119.99 DW-1039DIG website1/4/22Mini 1Canada119.99M2018Mini 1Silver1119.99 DW-1040DIG website1/4/22Voice XP10U.S.145.95M2020Mini 1White1119.99 DW-1049DIG website1/7/22Mini 2U.S.129.99M2205Mini 2Black2129.99 DW-1053DIG website1/8/22Mini 1U.S.119.99M2210Mini 2Silver2129.99 DW-1059DIG website1/13/22Mini 1U.S.119.99M2215Mini 2White2129.99 DW-1061DIG website1/18/22Mini 2U.S.129.99V3010Voice XPBlack2139.99 DW-1062DIG website1/22/22WaterproofMexico135.95V3012Voice XPWhite2139.99 DW-1065DIG website1/26/22Voice XPCanada139.99V3220Voice XP 10Black3145.95 DW-1066DIG website1/30/22WaterproofU.S.135.95V3225Voice XP 10White3145.95 DW-1070DIG website1/6/22Voice XP10Canada145.95W4150WaterproofBlack2135.95 DW-1071DIG website1/17/22Mini 2U.S.129.99W4155WaterproofWhite2135.95 OW-1041Other website1/3/22Voice XPU.S.139.99 OW-1047Other website1/5/22WaterproofMexico135.95 OW-1102Other website1/10/22Voice XPMexico139.99 OW-1102Other website1/10/22Voice XPMexico139.99 OW-1104Other website1/11/22Mini 2U.S.129.99 OW-1108Other website1/12/22WaterproofU.S.135.95 OW-1123Other website1/15/22Voice XPU.S.139.99 OW-1126Other website1/16/22Voice XP10U.S.145.95 OW-1130Other website1/17/22Mini 2U.S.129.99 OW-1132Other website1/19/22Voice XPCanada139.99 OW-1134Other website1/20/22Mini 1U.S.119.99 OW-1139Other website1/21/22Mini 1U.S.119.99 OW-1141Other website1/25/22Mini 2U.S.129.99 OW-1144Other website1/27/22Mini 1U.S.119.99 OW-1145Other website1/28/22Mini 2U.S.129.99 OW-1148Other website1/29/22Voice XPU.S.139.99 OW-1150Other website1/12/22Mini 1U.S.119.99 OW-1155Other website1/20/22Mini 1U.S.119.99 ST-1134Store1/5/22Mini 1Mexico119.99 ST-1142Store1/7/22Voice XPCanada139.99 ST-1148Store1/14/22Mini 1Canada119.99 ST-1151Store1/23/22Voice XP10U.S.145.95 ST-1152Store1/24/22Mini 2U.S.129.99 ST-1153Store1/31/22Mini 2U.S.129.99 Sales Projections New Product: Virtual AssistantVirtual Assistant: Gross Profit Analysis Sales$ 294,780.00$ 129.99$ 139.99$ 149.99$ 159.99$ 169.99$ 179.99$ 189.99 Units sold22,00018,000 Price per unit$ 159.9920,000 Total sales$ 3,519,780.0022,000 24,000 Expenses26,000 Variable expenses Units manufactured22,000 Variable cost per unit$ 135.00 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$ 146.59 Profit Total sales$ 3,519,780.00 Total expenses$ 3,225,000.00 Gross profit$ 294,780.00 Suppliers Virtual Assistant Suppliers Jenji ManufacturingAS&P ElectronicsHancock GlobalTotal Units Produced3,9003,6004,50012,000 Fixed costs$ 70,500.00$ 74,625.00$ 84,120.00 Fixed costs per unit18.0820.7318.69 Variable costs234,975.00218,700.00272,250.00 Variable cost per unit60.2560.7560.50 Total costs$ 305,475.00$ 293,325.00$ 356,370.00$ 955,170.00 Minimum total cost model Product Mix Mini 1Mini 2Voice XPVoice XP10Waterproof Revenue Units sold20,15017,02513,53012,94510,750 Price per unit$ 119.99$ 129.99$ 139.99$ 145.95$ 135.95 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 manufactured20,15017,02513,53012,94510,750 Fixed costs$ 265,000.00$ 270,000.00$ 279,000.00$ 282,000.00$ 280,000.00 Variable cost per unit$ 95.00$ 105.00$ 110.00$ 115.00$ 102.00 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