Excel Modules 9-16 SAM Capstone Project 1
triceyr
IL_EX16_CS9-16a_LatriceReaves_11.xlsmDocumentation
| Illustrated Excel 2016 | Modules 9-16: SAM Capstone Project 1a | |
| Breeze Smart Home Devices | |
| AUTOMATING AND ANALYZING DATA | |
| Author: | Latrice Reaves |
| 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. | |
Sales Overview
| Total Sales 2019 | ||||||
| Mid-Atlantic | $1,809,000 | |||||
| Midwest | $1,612,000 Will Deyes: Will Deyes: This sum is not correct because the sales amount in cell C16 should be $416,000, not $406,000. |
|||||
| Mountain | $2,087,000 | |||||
| New England | $1,932,000 | |||||
| Pacific | $2,249,500 | |||||
| South Atlantic | $1,336,000 | |||||
|
Will Deyes: Will Deyes: I think the Total Sales by Region chart would work better as a bar chart. | Quarterly Sales 2019 | |||||
| Q1 | Q2 | Q3 | Q4 | Trends | ||
| Mid-Atlantic | $417,000 | $457,000 | $450,000 | $485,000 | ||
| Midwest | $406,000 | $421,000 | $410,000 | $375,000 | ||
| Mountain | $504,000 | $526,000 | $510,000 | $547,000 | ||
| New England | $479,000 | $485,000 | $489,000 | $479,000 | ||
| Pacific | $578,000 | $500,500 | $597,000 | $574,000 | ||
| South Atlantic | $326,000 | $335,000 | $338,000 | $337,000 | ||
| Total | $2,710,000 | $2,724,500 | $2,794,000 | $2,797,000 | ||
Total Sales by Region
Mid-Atlantic Midwest Mountain New England Pacific 1809000 1612000 2087000 1932000 2249500
Quarterly Sales by Region
Mid-Atlantic Q1 Q2 Q3 Q4 417000 457000 450000 485000 Midwest Q1 Q2 Q3 Q4 406000 421000 410000 375000 Mountain Q1 Q2 Q3 Q4 504000 526000 510000 547000 New Englan d Q1 Q2 Q3 Q4 479000 485000 489000 479000 Pacific Q1 Q2 Q3 Q4 578000 500500 597000 574000 South Atlantic Q1 Q2 Q3 Q4 326000 335000 338000 337000
Total Quarterly Sales - 2019
Q1 Q2 Q3 Q4 2710000 2724500 2794000 2797000
Sales by Product
| Total Sales 2019 | |||||
| Lights | $2,410,955 | ||||
| Locks | $2,992,785 | ||||
| Plugs | $3,009,876 | ||||
| Thermostats | $2,611,884 | ||||
| Quarterly Sales 2019 | |||||
| Q1 | Q2 | Q3 | Q4 | Trends | |
| Lights | $586,525 | $608,135 | $614,445 | $601,850 | |
| Locks | $700,950 | $711,270 | $720,565 | $860,000 | |
| Plugs | $747,670 | $775,005 | $765,426 | $721,775 | |
| Thermostats | $674,855 | $630,090 | $693,564 | $613,375 | |
| Average | $677,500 | $681,125 | $698,500 | $699,250 | |
Quarterly Sales Trend
Lights Q1 Q2 Q3 Q4 586525 608135 614445 601850 Locks Q1 Q2 Q3 Q4 700950 711270 720565 860000 Plugs Q1 Q2 Q3 Q4 747670 775005 765426 721775 Thermostats Q1 Q2 Q3 Q4 674855 630090 693564 613375
2019 Sales by Product Type
Lights Q1 Q2 Q3 Q4 586525 608135 614445 601850 Locks Q1 Q2 Q3 Q4 700950 711270 720565 860000 Plugs Q1 Q2 Q3 Q4 747670 775005 765426 721775 Thermostats Q1 Q2 Q3 Q4 674855 630090 693564 613375 Average Q1 Q2 Q3 Q4 677500 681125 698500 699250
Category
Sales
Sales by Rep
| Sales by Rep - South Atlantic Region | |||
| Q1 2020 | |||
| Rep | Quotas | Sales | Bonus |
| Harsia | $355,000 | $352,564 | No |
| Lewison | $350,000 | $357,015 | Yes |
| Sattinger | $450,000 | $470,052 | No |
Sales by Rep
Sales Harsia Lewison 352564 357015
Chart Sales by Rep
Thermostats
| Revenue and Expenses | Profit | Detailed Costs | ||||||||
| Revenue | % of Total | Aurora | Crystal | Glide | Marketing | |||||
| Aurora | $ 3,718,000 | ERROR:#DIV/0! | Price | $ 169.00 | $ 189.00 | $ 219.00 | Aurora | $ 1,109,750 | ||
| Crystal | $ 3,846,150 | 34% | Mktg. cost | 50.44 | 59.03 | 73.36 | Crystal | $ 1,201,250 | ||
| Glide | $ 3,876,300 | 34% | Mfg. cost | 89.15 | 93.25 | 96.55 | Glide | $ 1,298,500 | ||
| Total sales | $ 11,440,450 | R&D cost | ERROR:#VALUE! | 20.64 | 31.07 | Manufacturing | ||||
| Units sold | 22,000 | 20,350 | 17,700 | Aurora | $ 1,961,300 | |||||
| Cost of Goods Sold | Gross profit | ERROR:#VALUE! | $ 327,263 | $ 318,865 | Crystal | $ 1,897,638 | ||||
| Aurora | $ 3,396,050 | 32% | Gross profit / unit | ERROR:#VALUE! | $16.08 | $18.01 | Glide | $ 1,708,935 | ||
| Crystal | $ 3,518,888 | 34% | R&D | |||||||
| Glide | $ 3,557,435 | 34% | Aurora Sales | Aurora | $ 325,000 | |||||
| Total COGS | $ 10,472,373 | 22,000 | Crystal | $ 420,000 | ||||||
| 20,000 | Glide | $ 550,000 | ||||||||
| Gross profit | $ 968,078 | 22,500 | Total cost of goods sold | $ 10,472,373 | ||||||
| 25,000 | ||||||||||
| 27,500 | ||||||||||
| 30,000 | ||||||||||
Controllers
| Vendors | Projections | ||||||||||
| Beckes | SmartEnergy | TW Assembly | Grand Total | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |||
| Fixed costs | $ 550.00 | $ 550.00 | $ 400.00 | - | Price | $ 659.00 | $ 659.00 | $ 659.00 | $ 659.00 | $ 659.00 | |
| Setup cost per unit | 7.50 | 6.00 | 8.00 | - | Mktg. cost | 300.00 | 300.00 | 285.00 | 275.00 | 265.00 | |
| Manufacturing cost per unit | 23.15 | 24.50 | 22.75 | - | Mfg. cost | 220.00 | 220.00 | 220.00 | 220.00 | 220.00 | |
| Cost per unit | 30.74 | 30.59 | 30.79 | - | R&D cost | 70.00 | 70.00 | 55.00 | 55.00 | 40.00 | |
| Units produced | 6,000 | 6,000 | 10,000 | 22,000 | Units sold | 22,000 | 24,000 | 27,000 | 30,000 | 31,000 | |
| Total cost | $ 184,450 | $ 183,550 | $ 307,900 | $ 675,900 | Gross profit | $1,518,000 | $1,656,000 | $2,673,000 | $3,270,000 | $4,154,000 | |
| Gross profit / unit | $69.00 | $69.00 | $99.00 | $109.00 | $134.00 | ||||||
| Constraints | Solver Model | ||||||||||
| Minimum units per vendor | 5,000 | ||||||||||
| Maximum units - Beckes | 12,000 | ||||||||||
| Maximum units - SmartEnergy | 7,500 | ||||||||||
| Maximum units - TW Assembly | 12,000 | ||||||||||
| Units required | 25,000 | ||||||||||
Orders
| Orders - Q1 2020 | |||||||||
| Quantity per Product | |||||||||
| Lights | |||||||||
| Locks | |||||||||
| Plugs | |||||||||
| Thermostats |
