Scenario Summary Current Values: Original Financing Intermediate Car Changing Cells: Price $ 56,500 $ 21,595 $ 33,999 $ CashBack $ 2,500 $ $ 1,500 $ Months 60 24 36 APR 3.90% 1.90% 2.40% Result Cells: Payment $ 946.13 $ 811.47 $ 864.50 $ Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Luxury Car 56,500 2,500 60 3.90% 946.13 New Car Loan Sticker price Trade in Cash back offer Loan amount Loan Inputs $ 56,500 $ 2,500 $ 2,500 $ 51,500 Loan term (months) Loan interest (APR) Loan payment 60 3.90% 946.13 $ Total cost of the car $ 56,767.72 4 SemiAuto-6 4 SemiAuto-6 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-4 4 Auto-4 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-4 4 Auto-4 3 AutoMan-5 3 AutoMan-5 3 AutoMan-5 3 AutoMan-5 4 Auto-4 4 Auto-4 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 Sales Area Stnd Veh Class 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD Diesel Diesel CNG CNG Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Diesel Diesel Diesel Diesel Diesel Diesel CA FA CA FA CA FA CA FA CA FA CA CA FA CA FA CA FA CA FA CA FA CA FA CA FA CA FA U2 B5 S2 B2 U2 B5 U2 B5 U2 B5 S2 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 station wagon station wagon small car small car small car small car station wagon station wagon small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car station wagon station wagon Stnd Veh Class 7 6 9.5 9 7 6 7 6 7 6 9.5 7 6 7 6 7 6 7 6 7 6 7 6 7 6 7 6 Hwy City MPG MPG 30 30 24 24 26 26 28 28 27 27 27 27 27 27 27 33 33 33 33 29 29 30 30 30 30 30 30 Cmb MPG 42 42 36 36 36 36 35 35 36 36 36 36 36 36 36 41 41 41 41 35 35 42 42 42 42 42 42 34 34 28 28 29 29 31 31 30 30 30 30 30 30 30 36 36 36 36 31 31 34 34 34 34 34 34 Smart Way Fuel Green house Gas Score Drive 8 yes 8 yes 9 yes 9 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 9 yes 9 yes 9 yes 9 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes Criteria Sales Area Hwy City MPG MPG Cmb MPG Smart Way 2 2 1.8 1.8 1.8 1.8 1.5 1.5 1.6 1.6 2 2 2 1.6 1.6 1 1 1 1 1.5 1.5 2 2 2 2 2 2 Trans Green house Gas Score AUDI A3 AUDI A3 HONDA Civic HONDA Civic HONDA Civic HONDA Civic HONDA Fit HONDA Fit HYUNDAI Acc HYUNDAI Acc KIA Forte KIA Forte KIA Forte KIA Rio KIA Rio SMART ForTwo SMART ForTwo SMART ForTw SMART ForTw TOYOTA Yari TOYOTA Yari VOLKSWAGEN VOLKSWAGEN VOLKSWAGEN VOLKSWAGEN VOLKSWAGEN VOLKSWAGEN Cyl Air Polluti on Score Displ Air Polluti on Score Model AUDI A3 AUDI A3 HONDA Civic HONDA Civic HONDA Civic HONDA Civic HONDA Fit HONDA Fit HYUNDAI Accent HYUNDAI Accent KIA Forte KIA Forte KIA Forte KIA Rio KIA Rio SMART ForTwo Cabriolet SMART ForTwo Cabriolet SMART ForTwo Coupe SMART ForTwo Coupe TOYOTA Yaris TOYOTA Yaris VOLKSWAGEN Golf VOLKSWAGEN Golf VOLKSWAGEN Jetta VOLKSWAGEN Jetta VOLKSWAGEN Jetta SportWagen VOLKSWAGEN Jetta SportWagen 2 2 1.8 1.8 1.8 1.8 1.5 1.5 1.6 1.6 2 2 2 1.6 1.6 1 1 1 1 1.5 1.5 2 2 2 2 2 2 Cyl Trans 4 SemiAuto-6 4 SemiAuto-6 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-4 4 Auto-4 4 Auto-5 4 Auto-5 4 Auto-5 4 Auto-4 4 Auto-4 3 AutoMan-5 3 AutoMan-5 3 AutoMan-5 3 AutoMan-5 4 Auto-4 4 Auto-4 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 4 SemiAuto-6 Drive Fuel Sales Area Stnd Veh Class 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD Diesel Diesel CNG CNG Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Diesel Diesel Diesel Diesel Diesel Diesel CA FA CA FA CA FA CA FA CA FA CA CA FA CA FA CA FA CA FA CA FA CA FA CA FA CA FA U2 B5 S2 B2 U2 B5 U2 B5 U2 B5 S2 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 station wagon station wagon small car small car small car small car station wagon station wagon small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car station wagon station wagon 7 6 9.5 9 7 6 7 6 7 6 9.5 7 6 7 6 7 6 7 6 7 6 7 6 7 6 7 6 City MPG 30 30 24 24 26 26 28 28 27 27 27 27 27 27 27 33 33 33 33 29 29 30 30 30 30 30 30 Hwy MPG 42 42 36 36 36 36 35 35 36 36 36 36 36 36 36 41 41 41 41 35 35 42 42 42 42 42 42 Cmb MPG 34 34 28 28 29 29 31 31 30 30 30 30 30 30 30 36 36 36 36 31 31 34 34 34 34 34 34 SmartWay Displ Greenhous e Gas Score Model Air Pollution Score All Models 8 yes 8 yes 9 yes 9 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 9 yes 9 yes 9 yes 9 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes 8 yes Veh Class large car midsize car minivan pickup small car special purpose station wagon SUV van Total Result Data Average - Air Pollution Score Average - City MPG Average - Hwy MPG Average - Cmb MPG 6.5235849057 15.7547169811 23.3962264151 18.4811320755 6.6446808511 19.7531914894 27.4638297872 22.5106382979 6.4482758621 17 23.5517241379 19.1034482759 4.6116504854 16.0081967213 20.9262295082 17.8524590164 6.575 19.6736842105 27.8276315789 22.6789473684 2.2285714286 22 25 23 6.4908536585 21.2256097561 28.4695121951 23.8597560976 6.5304259635 17.5746421268 23.1288343558 19.5828220859 2.8 15 20 17 6.2356656948 18.7785340314 25.8654450262 21.3596858639 What is my profit? Available capital Working hours required Total cost of ingredients Birthday Cake Cupcakes (dozen) Cookies, Shortbread (dozen) Cookies, Lemon Bars (dozen) $ $ 200.00 - must be less than or equal to 0 must be less than or equal to $ Optimal number to make Price (retail) each day $ 60.00 $ 25.00 $ 18.00 $ 20.00 Total labor hours must be less than or equal to working hours per day Total cost must be less than or equal to capital Number to make of each item cannot be more than the maximum per day possible 12 200.00 Cost (ingredients) Labor (hours) $ 14.00 $ 8.00 $ 6.00 $ 6.25 Max per day 3 1 1 1 1 4 6 6 A Skills Approach: Excel 2013 Chapter 8: Exploring Advanced Data Analysis challenge yourself 8.4 In this project, you will use scenarios and a scenario summary to analyze car purchasing options. A large amount of data about car gas consumption was downloaded from https://explore.data.gov/d/9un4-5bz7 and are explained at this Web site: http://www.epa.gov/greenvehicles/Aboutratings.do. You will use custom filtering to find the data you are interested in and copy it from this very large data set to another worksheet. You will then use Advanced Filter, conditional formatting, sorting, and subtotals to further analyze this subset of data. You will manipulate a PivotTable created from the main vehicle data set. Finally, you will use Solver to solve a business problem to help you pay for your new car. Skills needed to complete this project: Step 1 Download start file Creating What-If Analysis Scenarios Creating Scenario Summary Reports Creating a Custom Filter Using Advanced Filter Creating New Conditional Formatting Rules Sorting Data on Multiple Criteria Adding Subtotals Changing the Look of a PivotTable Filtering PivotTable Data Activating the Solver Add-Inn Using Solver 1. Open the start file EX2013-ChallengeYourself-8-4. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. 2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify the workbook. 3. You want to purchase a new car. Your current car is worth $2,500 as a trade-in. The base sticker price on the car you want is $21,595. The first financing offer from the dealer is 1.9% APR for 24 months, with no cash back. The original financing offer terms are listed in cells A3:B9 on the Car Loan worksheet. Cell B10 contains a formula with a PMT function to calculate the monthly payment. Use what-if analysis tools to compare financing options. 4. Create scenarios to compare financing options for the car loan. a. Create names for the following cells to make the scenarios easier to follow: Name cell B3: Price Name cell B4: TradeIn Name cell B5: CashBack Name cell B6: Loan Name cell B8: Months Name cell B9: APR Name cell B10: Payment Name cell B12: TotalCost 1 | Page challenge yourself 8.4 Last Updated 2/22/16 A Skills Approach: Excel 2013 Chapter 8: Exploring Advanced Data Analysis b. Create a scenario named Original Financing to save the original values in cells B3, B5, B8, and B9. c. Create a second scenario named Intermediate Car. Change the values as follows: Cell B3: 33999 Cell B5: 1500 Cell B8: 36 Cell B9: 2.4% d. Show the Intermediate Car scenario to verify your data entry. e. Create a third scenario named Luxury Car. Change the values as follows: Cell B3: 56500 Cell B5: 2500 Cell B8: 60 Cell B9: 3.9% f. Show the Luxury Car scenario to verify your data entry. 5. Create a scenario summary report to compare the results for cells B10 (the monthly payment) and B12 (the total cost of the car including interest). 6. The data in the MPG Data worksheet were downloaded from the Web site https://explore.data.gov/d/9un4-5bz7 and are explained at this Web site: http://www.epa.gov/greenvehicles/Aboutratings.do. The greenhouse gas scores range from 0 to 10, where 10 is best. The vehicles with the best scores on both air pollution and greenhouse gas receive the SmartWay designation. Use custom filtering to find cars in this data set that meet your criteria and then copy the smaller data set to another worksheet where you can work with it further. a. Go to the MPG Data worksheet, and display the AutoFilter arrows for the data set. b. Create a custom number filter to show only rows where the greenhouse gas score is greater than or equal to 8. c. Create another custom number filter to show only rows where the highway MPG is greater than or equal to 35. d. Create a custom text filter to show only rows where the Trans column contains the word Auto. e. At this point, the list of more than 2,000 cars should be filtered down to just 27 records. f. Copy the filtered data to cell A1 in the My Car Data worksheet. Be sure to include the heading row and paste keeping the source columns widths. 7. On the My Car Data worksheet, use Advanced Filter to copy a subset of data to another location on this worksheet. a. Copy cells A1:O1 and paste them below the data set in row 31. b. In cell A30, type: Criteria c. Set up the criteria in the row(s) below row 31. Find cars where the value in the Fuel column is gasoline and the value in the Hwy MPG column is greater than 35. d. In cell A34, type: Filtered Data e. Use Advanced Filter to find the rows that meet the criteria and copy the filtered data to another location beginning in cell A35. f. 2 | Page Thirteen rows should have been copied from the main data set to cells A36:O48. challenge yourself 8.4 Last Updated 2/22/16 A Skills Approach: Excel 2013 Chapter 8: Exploring Advanced Data Analysis 8. Format the filtered data set (cells A35:O48) as a table. Use Table Style Light 9. Be careful not to include row 34 in the table data range. 9. Insert a new table column to the left of the Greenhouse Gas Score column. a. Copy the values from cells M36:M48 to N36:N48. b. Type this column label in cell N35: Cmb MPG Icon 10. Apply a new custom conditional formatting rule to cells N36:N48 to show the 4 Ratings icon set. Show the icon only. 11. Sort the table first alphabetically by the values in the Model column and then by icon in the Cmb MPG Icon column so the Signal Meter With Four Filled Bars icon appears at the top and the Signal Meter With One Filled Bar icon appears at the bottom. 12. Go back to the main data set at the top of the My Car Data worksheet and add subtotals for each change in Model to calculate the average for the following: Air Pollution Score, City MPG, Hwy MPG, and Cmb MPG. NOTE: To ensure accurate grading of your project in SIMnet, it is very important that you complete this step. Verify that the column header "Model" for the Criteria table is located in cell A44 and the column header "Model" for the Filtered Data table is located in cell A48. 13. Collapse the data to show just the total rows. 14. Go to the MPG PivotTable worksheet. This sheet includes a PivotTable created from the data on the MPG Data worksheet. a. Apply the Pivot Style Light 14 Quick Style to the PivotTable. b. Display a slicer for the SmartWay field and show only data where the SmartWay value is Yes. 15. To pay for the new car, you've decided to start a bakery business out of your home kitchen. You have limited capital and capacity, so you need to make wise decisions about which products to bake each day. Go to the Bakery Business worksheet. Cell B1 uses a SUMPRODUCT formula to calculate profit. Carefully review the assumptions and formulas in this worksheet before entering the following Solver parameters: a. Find the maximum possible profit (cell B1) by changing the values in cells C7:C10. b. The working hours required (B3) must be less than or equal to the number of working hours in the day (D3). c. The total cost of ingredients (B4) must be less than or equal to the available capital (D4). d. For each item in the range C7:C10, the number of items baked cannot exceed the maximum capacity available per day as defined in cells F7:F10. This restriction requires four separate constraints in the Solver Parameters dialog. e. The items in the variable cell range C7:C10 must be whole numbers. f. There should be a total of seven constraints. g. Run Solver and accept the Solver solution. Step 2 16. Save and close the workbook. Upload & Save 17. Upload and save your project file. Step 3 18. Submit project for grading. Grade my Project 3 | Page challenge yourself 8.4 Last Updated 2/22/16 Scenario Summary A 1 2 3 5 6 7 8 9 10 11 12 13 14 15 B C D E F G Scenario Summary Current Values: Original Financing Intermediate Car Luxury Car SOLUTION SAMPLE Changing Cells: Price $ 56,500 $ 21,595 $ 33,999 CashBack $ 2,500 $ $ 1,500 Months 60 24 36 APR 3.90% 1.90% 2.40% Result Cells: Payment $ 946.13 $ 811.47 $ 864.50 TotalCost $ 56,767.72 $ 19,475.21 $ 31,121.90 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. $ $ $ $ 56,500 2,500 60 3.90% 946.13 56,767.72 Car Loan A 1 2 3 4 5 6 7 8 9 10 11 12 B New Car Loan Sticker price Trade in Cash back offer Loan amount Loan Inputs $ 56,500 $ 2,500 $ 2,500 $ 51,500 SOLUTION SAMPLE Loan term (months) Loan interest (APR) Loan payment 60 3.90% 946.13 $ Total cost of the car $ 56,767.72 My Car Data Displ D Cyl Trans E Drive F Fuel G Sales Area H Stnd I Veh Class J K AUDI A3 Average HONDA Civic Average HONDA Fit Average HYUNDAI Accent Average KIA Forte Average KIA Rio Average SMART ForTwo Cabriolet Average SMART ForTwo Coupe Average TOYOTA Yaris Average VOLKSWAGEN Golf Average VOLKSWAGEN Jetta Average VOLKSWAGEN Jetta SportWagen Average Grand Average L City MPG 6.5 7.875 6.5 6.5 7.5 6.5 6.5 6.5 6.5 6.5 6.5 6.5 6.814814815 M Hwy MPG N Cmb MPG 30 42 25 36 28 35 27 36 27 36 27 36 33 41 33 41 29 35 30 42 30 42 30 42 28.7037037 38.37037037 O P SmartWay Model C Greenhous e Gas Score 1 4 9 12 15 19 22 25 28 31 34 37 40 41 42 43 B Air Pollution Score A 34 28.5 31 30 30 30 36 36 31 34 34 34 32 SOLUTION SAMPLE Fuel gasoline Sales Area Stnd Veh Class Trans 4 4 4 4 4 4 4 4 4 3 3 3 3 Drive Fuel Sales Area Stnd Veh Class Auto-5 Auto-5 Auto-4 Auto-4 Auto-5 Auto-5 Auto-5 Auto-4 Auto-4 AutoMan-5 AutoMan-5 AutoMan-5 AutoMan-5 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline CA FA CA FA CA CA FA CA FA CA FA CA FA U2 B5 U2 B5 S2 U2 B5 U2 B5 U2 B5 U2 B5 small car small car small car small car small car small car small car small car small car small car small car small car small car City MPG Hwy MPG >35 City MPG 7 6 7 6 9.5 7 6 7 6 7 6 7 6 Hwy MPG 26 26 27 27 27 27 27 27 27 33 33 33 33 Cmb MPG Icon Cmb MPG Cmb MPG 36 36 36 36 36 36 36 36 36 41 41 41 41 29 29 30 30 30 30 30 30 30 36 36 36 36 SmartW ay Cyl 1.8 1.8 1.6 1.6 2 2 2 1.6 1.6 1 1 1 1 Drive SmartWay Displ Trans Greenho use Gas Score Model HONDA Civic HONDA Civic HYUNDAI Accent HYUNDAI Accent KIA Forte KIA Forte KIA Forte KIA Rio KIA Rio SMART ForTwo Cabriolet SMART ForTwo Cabriolet SMART ForTwo Coupe SMART ForTwo Coupe Cyl Greenhous e Gas Score 48 49 50 51 52 53 54 55 56 57 58 59 60 61 Displ Air Pollution Score 44 Model 45 46 47 Filtered Data Air Pollutio n Score Criteria 8 8 8 8 8 8 8 8 8 9 9 9 9 yes yes yes yes yes yes yes yes yes yes yes yes yes MPG Data A B C D E F G H K L M N O P Q SmartWay Greenhouse Gas Score Air Pollution Score All Models 1 SOLUTION SAMPLE 2 42 46 869 870 872 874 885 888 914 916 1083 1087 1092 1101 1103 1800 1801 1802 1803 2044 2046 2075 2077 2091 2095 2103 2105 Model AUDI A3 AUDI A3 HONDA Civic HONDA Civic HONDA Civic HONDA Civic HONDA Fit HONDA Fit HYUNDAI Accent HYUNDAI Accent KIA Forte KIA Forte KIA Forte KIA Rio KIA Rio SMART ForTwo Cabriolet SMART ForTwo Cabriolet SMART ForTwo Coupe SMART ForTwo Coupe TOYOTA Yaris TOYOTA Yaris VOLKSWAGEN Golf VOLKSWAGEN Golf VOLKSWAGEN Jetta VOLKSWAGEN Jetta VOLKSWAGEN Jetta SportWagen VOLKSWAGEN Jetta SportWagen Displ 2 2 1.8 1.8 1.8 1.8 1.5 1.5 1.6 1.6 2 2 2 1.6 1.6 1 1 1 1 1.5 1.5 2 2 2 2 2 2 Cyl Trans 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 3 3 3 3 4 4 4 4 4 4 4 4 SemiAuto-6 SemiAuto-6 Auto-5 Auto-5 Auto-5 Auto-5 Auto-5 Auto-5 Auto-4 Auto-4 Auto-5 Auto-5 Auto-5 Auto-4 Auto-4 AutoMan-5 AutoMan-5 AutoMan-5 AutoMan-5 Auto-4 Auto-4 SemiAuto-6 SemiAuto-6 SemiAuto-6 SemiAuto-6 SemiAuto-6 SemiAuto-6 Drive Fuel Sales Area 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD 2WD Diesel Diesel CNG CNG Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Gasoline Diesel Diesel Diesel Diesel Diesel Diesel CA FA CA FA CA FA CA FA CA FA CA CA FA CA FA CA FA CA FA CA FA CA FA CA FA CA FA Stnd Veh Class U2 B5 S2 B2 U2 B5 U2 B5 U2 B5 S2 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 U2 B5 station wagon station wagon small car small car small car small car station wagon station wagon small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car small car station wagon station wagon 7 6 9.5 9 7 6 7 6 7 6 9.5 7 6 7 6 7 6 7 6 7 6 7 6 7 6 7 6 City Hwy Cmb MPG MPG MPG 30 30 24 24 26 26 28 28 27 27 27 27 27 27 27 33 33 33 33 29 29 30 30 30 30 30 30 42 42 36 36 36 36 35 35 36 36 36 36 36 36 36 41 41 41 41 35 35 42 42 42 42 42 42 34 34 28 28 29 29 31 31 30 30 30 30 30 30 30 36 36 36 36 31 31 34 34 34 34 34 34 8 8 9 9 8 8 8 8 8 8 8 8 8 8 8 9 9 9 9 8 8 8 8 8 8 8 8 yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes MPG PivotTable A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 B C D E Row Labels Average of Air Pollution Score Average of City MPG Average of Hwy MPG Average of Cmb MPG large car 7.37 20.73 30.73 24.33 midsize car 7.03 24.41 31.99 27.18 minivan 6.00 22.00 28.00 24.00 pickup 6.50 22.00 27.00 24.00 small car 6.97 23.63 32.24 26.89 special purpose 7.00 22.00 25.00 23.00 station wagon 6.69 23.26 30.43 25.91 SUV 7.23 23.08 27.84 24.81 Grand Total 6.98 23.53 31.18 26.38 SOLUTION SAMPLE SmartWay no yes Bakery Business 1 2 3 4 5 A What is my profit? Available capital Working hours required Total cost of ingredients B $ $ C 182.75 200.00 12 must be less than or equal to 77.25 must be less than or equal to D E F 12 200.00 SOLUTION SAMPLE 6 7 8 9 10 11 12 13 14 Birthday Cake Cupcakes (dozen) Cookies, Shortbread (dozen) Cookies, Lemon Bars (dozen) Optimal number to make each day Price (retail) $ 60.00 1 $ 25.00 4 $ 18.00 0 $ 20.00 5 Total labor hours must be less than or equal to working hours per day Total cost must be less than or equal to capital Number to make of each item cannot be more than the maximum per day possible $ Cost (ingredients) Labor (hours) $ 14.00 $ 8.00 $ 6.00 $ 6.25 Max per day 3 1 1 1 1 4 6 6