This is the Excel project.
Shelby4
IL_Excel2013_UK_P1a_HanaOh_1copy.xlsxDocumentation
| Illustrated Excel 2013 | |
| Unit K: SAM Project 1a | |
| Clef Notes Software | |
| MODELING DATA WITH SCENARIOS, WHAT-IF ANALYSES, GOAL SEEK, AND SOLVER | |
| Author: | Hana Oh |
| 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. | |
Current Year
| Sales and Expenses | Operating Statistics | Cost Detail | ||||||||
| Revenues | % of Total | Basic Clef | Premium Clef | Ultra Clef | Customer Acquisition (Mktg) | |||||
| Basic Clef | $ 9,405,000 | 37% | Price | $99 | $299 | $599 | Basic Clef | $ 1,258,750 | ||
| Premium Clef | $ 14,053,000 | 55% | Acquisition Cost per Unit | $13.25 | $52.75 | $85.00 | Premium Clef | $ 2,479,250 | ||
| Ultra Clef | $ 2,096,500 | 8% | Mfg. Cost per Unit | $3.15 | $3.25 | $3.55 | Ultra Clef | $ 297,500 | ||
| Total Sales | $ 25,554,500 | R&D Cost per Unit | $1.05 | $2.55 | $42.86 | Manufacturing | ||||
| Units Sold | 95,000 | 47,000 | 3,500 | Basic Clef | $ 299,250 | |||||
| Cost of Goods Sold | Gross Profit per Unit | $81.55 | $240.45 | $467.59 | Premium Clef | $ 152,750 | ||||
| Basic Clef | $ 1,658,000 | 34% | Ultra Clef | $ 12,425 | ||||||
| Premium Clef | $ 2,752,000 | 57% | R&D | |||||||
| Ultra Clef | $ 459,925 | 9% | Basic Clef | $ 100,000 | ||||||
| Total COGS | $ 4,869,925 | Premium Clef | $ 120,000 | |||||||
| Ultra Clef | $ 150,000 | |||||||||
| Gross Profit | $ 20,684,575 | Total cost of goods sold | $ 4,869,925 | |||||||
Clef Notes Software
Profitability
| Operating Statistics | Basic Clef Sales | ||||
| Basic Clef | Premium Clef | Ultra Clef | 95,000 | ||
| Price | $99 | $299 | $599 | 75,000 | |
| Acquisition Cost per Unit | $13.25 | $52.75 | $85.00 | 85,000 | |
| Mfg. Cost per Unit | $3.15 | $3.25 | $3.55 | 95,000 | |
| R&D Cost per Unit | $1.05 | $2.55 | $38.96 | 105,000 | |
| Units sold | 95,000 | 47,000 | 3,500 | 115,000 | |
| Gross Profit | $7,747,000 | $11,301,000 | $1,650,211 | ||
| Gross Profit per Unit | $81.55 | $240.45 | $471.49 | ||
Clef Notes Software
Subcontractor
| Treble Clef | ||||
| Device Development Subcontractors | ||||
| Karma Inc. | Code Crew | Lorenz Systems | Grand Total | |
| Fixed Contract Cost | $750 | $550 | $600 | - |
| Setup Cost per Unit | $5.50 | $4.00 | $6.00 | - |
| Manufacturing Cost per Unit | $3.15 | $4.50 | $2.75 | - |
| Cost per Unit | $8.78 | $8.59 | $8.79 | - |
| Units Produced | 6,000 | 6,000 | 15,000 | 27,000 |
| Total Cost | $52,650 | $51,550 | $131,850 | $236,050 |
| Constraints | Solver Model | |||
| Minimum Units Product per Subcontractor | 5,000 | |||
| Maximum Units Producible by Karma Inc. | 15,000 | |||
| Maximum Units Producible by Code Crew | 7,500 | |||
| Maximum Units Producible by Lorenz Systems | 15,000 | |||
| Required Number of Units Produced | 27,000 |
Clef Notes Software
