Project 1b Excel Module 12 Performing What-If Analysis
BLucas88instructions and start files are attached
2 years ago
10
files (3)Support_EX365_2021_12b_Orders.accdb
IL_EX365_2021_12b_TimothyLynch_1.xlsx
- Instructions_IL_EX365_2021_12b.docx
Support_EX365_2021_12b_Orders.accdb
| OrderID | ProductID | ProductName | Channel | Units |
|---|---|---|---|---|
| 105-11 | 1 | Aspen | Retailer | 10 |
| 105-14 | 1 | Aspen | Web | 5 |
| 105-15 | 3 | Santa Fe | Web | 4 |
| 105-20 | 2 | Napa | Retailer | 4 |
| 105-21 | 3 | Santa Fe | Web | 1 |
| 105-23 | 1 | Aspen | Web | 2 |
| 105-25 | 2 | Napa | Web | 5 |
| 105-32 | 3 | Santa Fe | Retailer | 10 |
| 105-37 | 1 | Aspen | Retailer | 3 |
| 105-44 | 3 | Santa Fe | Web | 2 |
| 105-46 | 1 | Aspen | Web | 1 |
| 105-49 | 1 | Aspen | Web | 5 |
| 105-81 | 2 | Napa | Web | 3 |
| 105-86 | 2 | Napa | Web | 1 |
| 105-88 | 1 | Aspen | Web | 12 |
| ProductID | ProductName | Price |
|---|---|---|
| 1 | Aspen | ¤ 350.00 |
| 2 | Napa | ¤ 370.00 |
| 3 | Santa Fe | ¤ 390.00 |
IL_EX365_2021_12b_TimothyLynch_1.xlsx
Documentation
| Avant Quilts | |
| PERFORM WHAT-IF ANALYSES | |
| Author: | Timothy Lynch |
| 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. | |
Expenses
| Revenue and Expenses | Per Unit Profit | Detailed Costs | ||||||||
| Revenue | % of Total | Aspen | Napa | Santa Fe | Marketing | |||||
| Aspen | $ 8,750 | 32% | Price | $ 350.00 | $ 370.00 | $ 390.00 | Aspen | $ 850 | ||
| Napa | $ 8,140 | 30% | Mktg. cost | 34.00 | 40.00 | 34.62 | Napa | $ 880 | ||
| Santa Fe | $ 10,140 | 38% | Mfg. cost | 195.00 | 207.00 | 221.00 | Santa Fe | $ 900 | ||
| Total sales | $ 27,030 | R&D cost | 32.00 | 38.64 | 34.62 | Manufacturing | ||||
| Units sold | 25 | 22 | 26 | Aspen | $ 4,875 | |||||
| Cost of Goods Sold | Gross profit / unit | $ 89.00 | $ 84.36 | $ 99.77 | Napa | $ 4,554 | ||||
| Aspen | $ 6,525 | 32% | Santa Fe | $ 5,746 | ||||||
| Napa | $ 6,284 | 31% | R&D | |||||||
| Santa Fe | $ 7,546 | 37% | Aspen | $ 800 | ||||||
| Total COGS | $ 20,355 | Napa | $ 850 | |||||||
| Santa Fe | $ 900 | |||||||||
| Gross profit | $ 6,675 | Total cost of goods sold | $ 20,355 | |||||||
Avant Quilts Expense Report
Profit Analysis
| Profit | Aspen Sales | |||||||||
| Aspen | Napa | Santa Fe | 25 | |||||||
| Price | $ 350.00 | $ 370.00 | $ 390.00 | 22 | ||||||
| Mktg. cost | 34.00 | 40.00 | 34.62 | 24 | ||||||
| Mfg. cost | 195.00 | 207.00 | 221.00 | 26 | ||||||
| R&D cost | 32.00 | 38.64 | 34.62 | 28 | ||||||
| Units sold | 25 | 22 | 26 | 30 | ||||||
| Gross profit | $2,225 | $1,856 | $2,594 | |||||||
| Gross profit / unit | $89.00 | $84.36 | $99.77 | |||||||
| Aspen Projections | Aspen Sales and Expense Increases | |||||||||
| Aspen sales | $8,750 | 0.5% | 1.0% | 1.5% | 2.0% | 2.5% | ||||
| Projected growth | 5.0% | 3.5% | ||||||||
| Current expenses | $6,525 | 4.0% | ||||||||
| Expense increase | 1.5% | 4.5% | ||||||||
| Projected profit | $2,565 | 5.0% | ||||||||
| 5.5% | ||||||||||
| 6.0% |
Avant Quilts Profit Analysis
Tahoe
| Tahoe Quilters | Tahoe Projections | ||||||||||
| HireOut | Penn | Varia | Total | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |||
| Fixed costs | $ 250.00 | $ 220.00 | $ 230.00 | - | Price | $ 325.00 | $ 325.00 | $ 325.00 | $ 335.00 | $ 335.00 | |
| Setup cost per unit | 1.50 | 1.50 | 1.75 | - | Mktg. cost | 14.15 | 14.15 | 16.45 | 16.45 | 17.10 | |
| Manufacturing cost per unit | 165.00 | 160.00 | 168.00 | - | Mfg. cost | 195.00 | 195.00 | 195.00 | 195.00 | 195.00 | |
| Cost per unit | 176.50 | 168.83 | 177.96 | - | R&D cost | 15.00 | 15.00 | 15.00 | 15.00 | 15.00 | |
| Units produced | 25 | 30 | 28 | 83 | Units sold | 40 | 42 | 44 | 46 | 50 | |
| Total cost | $ 4,413 | $ 5,065 | $ 4,983 | $ 14,461 | Gross profit | $4,034 | $4,236 | $4,336 | $4,993 | $5,395 | |
| Gross profit / unit | $100.85 | $100.85 | $98.55 | $108.55 | $107.90 | ||||||
| Constraints | Solver Model | ||||||||||
| Minimum units per quilter | 20 | ||||||||||
| Maximum units - HireOut | 50 | ||||||||||
| Maximum units - Penn | 55 | ||||||||||
| Maximum units - Varia | 60 | ||||||||||
| Units required | 100 | ||||||||||
Avant Quilts Tahoe Projections
image1.png
image2.png
Instructions_IL_EX365_2021_12b.docx
This file is too large to display.View in new window
other Questions(10)
- "Competitive Markets, Price, Quality, and Monopoly" Please respond to the following:
- Develop a consumer behavioral report to be submitted to the managing director of a luxury brand and product line to outline the key issues facing by the company in terms of consumer behavioral issues in about 2500 words.
- HCS 475 Week 1 Individual Assignment Leadership Style Paper
- HCS 320 Week 1 Individual Communication Opinion Paper
- Psychology assignments
- For 'The Think Think
- Geography in China short report
- Contract Law
- English help
- Analyze the following article
