Biz Cafe Excel Capstone Assignemnt (2) AutoSave Home Insert Comments $ 5 Draw Page Layout Calibri (Body) 11 V B 1 A Formulas A Data = Review = = View > Wraa Text Merge & Center Custom S % 2nsert Delete Format EAY- 0 z Sort Find & X Filter Select Share 4 Ideas Peste > Conditional Format Formatting es Teble cell Styles Sensitivity Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates GR5 x fx 0% MICUIUIT COME 84 Large Coffee Tatal Weekly Revenue using six week average sales B5 86 87 88 Task 2 89 Total Cost of Goods Sold 90 What is your average weekly cost of goods sold? 91 Cups Size Average Weekly Sales freCOGS per Cup 92 Small Coffee 93 Medium Coffee 94 Large Coffee Total Weekly COGS using six week average 96 97 Task 3 98 What is your average weekly gross profit? 99 100 Total Revenue from Total Cast of Goads Sald Average Weekly Grass Profit 101 102 103 Task 4 104 What is your weekly gross profit for a large cup of coffee after raising the price? 105 New COGS Sold N ew Gross Profit 107 106 Price Increase old Price per Cup New Price Per Cup Averace Weekly fro New Average Weekly Sales app New Revenue COGS per Cup 5% 108 10% 109 15% 110 Task 5 111 Your coffee supplier has increased their coffee price by 9% pe pound what is new coffee cost for a large cup of coffee? 112 113 Caffry Cost Per Poi Price increase New Coffee Cast Per Amount of coffee pi Nrw Cost of Coffee for a large Cup 114 115 116 Sheet1 + # E 2 - + 117% Comments AutoSave $ 5 Biz Cafe Excel Capstone Assignemnt (2) Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 11 A M = = = Wraa Text Custom Paste BIU D A Merge & Center S -% Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates GRS X fx UT 2nsert I Delete Format Share 4 Ideas EAY- 0 -- X Sorte Find & A Filter Select Conditional Formet Formatting es Table cell Styles Sensitivity Check for Updates 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 9? 80 B1 Cups Size Average Weekly Sales Price Per Cup Weekly Revenue 82 Small Coffee B3 Medium Coffee B4 Large Coffee 85 Total Weekly Revenue using six week average sales 86 87 BB Task 2 89 Total Cost of Goods Sold 90 What is your average werkly cost of gaads sald? 91 Cups Size Average Weekly Sales freCOGS per Cup 92 Small Coffee 93 Medium Coffee 94 Large Coffee 95 Total Weekly COGS using six week average 46 97 Task 3 98 What is your average weekly grass profit? 99 100 Total Revenue fron Total cost of Goods Sold Average Weekly Gross Profit 101 102 103 Task 4 104 What is your weekly gross profit for a large cup of coffee after raising the price? 105 106 Price Increase Old Price per Cup New Price Per Cup Average Weekly fra New Average Weekly Sales app New Revenue 107 106 109 COGS per Cup New COGS Sold New Gross Profit 15% Sheet1 E - + 117% Biz Cafe Excel Capstone Assignemnt (2) View Comments AutoSave $ 5 Home Insert Draw Page Layout Calibri (Body 11 BIV A Formulas A M Data = E Review = = UT Wraa Text Merge & Center Custom S % 2nsert I Delete Farmat Share 4 Ideas EAY- 0 -- Sort Find & X u Select Fille Paste > Conditional Format Formatting es Table cell Styles Sensitivity Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates GR5 x fx 5% increase 10% Increase 15% increase O Price by Cup Size (use Week 9 Prices) Small $ Medium $ Large S New Cup Prices by Size Small Medium Large 3.20 3.78 4.14 T 1556 0 847 735 343 1437 1258 Cost of Goods Sold (GOGS) Cost of each Cup Small Cup Medium Cup Large Cup 54 Coffee Sales by Cup for Slx Weeks 55 Week 4 Small Coffee T Medium Coffee Large Coffee 58 Weeks Small Coffee Medium Coffee Large Coffee 61 Week 6 Small Coffee Medium Coffee Large Coffee 64 Week 7 Small Coffee Mediurn Coffee Large Coffee Week 8 Small Coffee Medium Coffee Large Coffee Week 9 Small Coffee Medium Coffee Large Coffee $ $ $ Customer Response to Coffee Price C5% increase 10% increase 15% increase No change R791 79% 54 Reduce purchases by 30% per week 12% 18% Go to another cafe 3%) 10% 6,380.80 6,463.80 3,949.56 1% 514 Cost of Coffee per pound Most Recent Decision (9) $ 6.00 New Cost of Coffee per pound after Supplier Increase New price per pound (4% increase per pound New price per pound (4% increase per pound) 1543 1414 629 1929 1647 931 1994 17101 954) Coffee cost for each Cup Size 1/64 lb, or .016 lbs. of Coffee (Small) 2/64 lb. or.032 lbs. of Coffee (Medium) 3/64 lb. or.048 lbs. of Coffee (Large) $ $ $ 0.10 0.19 0.29 New Coffee cost for each Oup Size 1/64 lb, or .016 lbs. of Coffee (Small) 2/64 lb. or.032 lbs. of Coffee (Medium) 3/64 lb. or.048 lbs. of Coffee (Large) 74 To Complete the tasks you need to use functions/create formuals and reference the cells above when completing the tasks. 75 Getting you started: In Cell B82 you should use the Average Function and reference the cellls withn Coffee Sales by Cup for Six Weeks" 76 Getting you started: In Cell C82 you should reference cell F55 (will not lose points if you type in the value of FS5). 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 9? 80 81 Cups Size Average Weekly Sales Price Per Cup Weekly Revenue B2 Small Coffee 83 Medium Coffee 84 Large Coffcc Sheet1 + # E 2 - + 117% Biz Cafe Excel Capstone Assignemnt (2) AutoSave Home Insert Comments $ 5 Draw Page Layout Calibri (Body) 11 V B 1 A Formulas A Data = Review = = View > Wraa Text Merge & Center Custom S % 2nsert Delete Format EAY- 0 z Sort Find & X Filter Select Share 4 Ideas Peste > Conditional Format Formatting es Teble cell Styles Sensitivity Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates GR5 x fx 0% MICUIUIT COME 84 Large Coffee Tatal Weekly Revenue using six week average sales B5 86 87 88 Task 2 89 Total Cost of Goods Sold 90 What is your average weekly cost of goods sold? 91 Cups Size Average Weekly Sales freCOGS per Cup 92 Small Coffee 93 Medium Coffee 94 Large Coffee Total Weekly COGS using six week average 96 97 Task 3 98 What is your average weekly gross profit? 99 100 Total Revenue from Total Cast of Goads Sald Average Weekly Grass Profit 101 102 103 Task 4 104 What is your weekly gross profit for a large cup of coffee after raising the price? 105 New COGS Sold N ew Gross Profit 107 106 Price Increase old Price per Cup New Price Per Cup Averace Weekly fro New Average Weekly Sales app New Revenue COGS per Cup 5% 108 10% 109 15% 110 Task 5 111 Your coffee supplier has increased their coffee price by 9% pe pound what is new coffee cost for a large cup of coffee? 112 113 Caffry Cost Per Poi Price increase New Coffee Cast Per Amount of coffee pi Nrw Cost of Coffee for a large Cup 114 115 116 Sheet1 + # E 2 - + 117% Comments AutoSave $ 5 Biz Cafe Excel Capstone Assignemnt (2) Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 11 A M = = = Wraa Text Custom Paste BIU D A Merge & Center S -% Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates GRS X fx UT 2nsert I Delete Format Share 4 Ideas EAY- 0 -- X Sorte Find & A Filter Select Conditional Formet Formatting es Table cell Styles Sensitivity Check for Updates 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 9? 80 B1 Cups Size Average Weekly Sales Price Per Cup Weekly Revenue 82 Small Coffee B3 Medium Coffee B4 Large Coffee 85 Total Weekly Revenue using six week average sales 86 87 BB Task 2 89 Total Cost of Goods Sold 90 What is your average werkly cost of gaads sald? 91 Cups Size Average Weekly Sales freCOGS per Cup 92 Small Coffee 93 Medium Coffee 94 Large Coffee 95 Total Weekly COGS using six week average 46 97 Task 3 98 What is your average weekly grass profit? 99 100 Total Revenue fron Total cost of Goods Sold Average Weekly Gross Profit 101 102 103 Task 4 104 What is your weekly gross profit for a large cup of coffee after raising the price? 105 106 Price Increase Old Price per Cup New Price Per Cup Average Weekly fra New Average Weekly Sales app New Revenue 107 106 109 COGS per Cup New COGS Sold New Gross Profit 15% Sheet1 E - + 117% Biz Cafe Excel Capstone Assignemnt (2) View Comments AutoSave $ 5 Home Insert Draw Page Layout Calibri (Body 11 BIV A Formulas A M Data = E Review = = UT Wraa Text Merge & Center Custom S % 2nsert I Delete Farmat Share 4 Ideas EAY- 0 -- Sort Find & X u Select Fille Paste > Conditional Format Formatting es Table cell Styles Sensitivity Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates GR5 x fx 5% increase 10% Increase 15% increase O Price by Cup Size (use Week 9 Prices) Small $ Medium $ Large S New Cup Prices by Size Small Medium Large 3.20 3.78 4.14 T 1556 0 847 735 343 1437 1258 Cost of Goods Sold (GOGS) Cost of each Cup Small Cup Medium Cup Large Cup 54 Coffee Sales by Cup for Slx Weeks 55 Week 4 Small Coffee T Medium Coffee Large Coffee 58 Weeks Small Coffee Medium Coffee Large Coffee 61 Week 6 Small Coffee Medium Coffee Large Coffee 64 Week 7 Small Coffee Mediurn Coffee Large Coffee Week 8 Small Coffee Medium Coffee Large Coffee Week 9 Small Coffee Medium Coffee Large Coffee $ $ $ Customer Response to Coffee Price C5% increase 10% increase 15% increase No change R791 79% 54 Reduce purchases by 30% per week 12% 18% Go to another cafe 3%) 10% 6,380.80 6,463.80 3,949.56 1% 514 Cost of Coffee per pound Most Recent Decision (9) $ 6.00 New Cost of Coffee per pound after Supplier Increase New price per pound (4% increase per pound New price per pound (4% increase per pound) 1543 1414 629 1929 1647 931 1994 17101 954) Coffee cost for each Cup Size 1/64 lb, or .016 lbs. of Coffee (Small) 2/64 lb. or.032 lbs. of Coffee (Medium) 3/64 lb. or.048 lbs. of Coffee (Large) $ $ $ 0.10 0.19 0.29 New Coffee cost for each Oup Size 1/64 lb, or .016 lbs. of Coffee (Small) 2/64 lb. or.032 lbs. of Coffee (Medium) 3/64 lb. or.048 lbs. of Coffee (Large) 74 To Complete the tasks you need to use functions/create formuals and reference the cells above when completing the tasks. 75 Getting you started: In Cell B82 you should use the Average Function and reference the cellls withn Coffee Sales by Cup for Six Weeks" 76 Getting you started: In Cell C82 you should reference cell F55 (will not lose points if you type in the value of FS5). 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 9? 80 81 Cups Size Average Weekly Sales Price Per Cup Weekly Revenue B2 Small Coffee 83 Medium Coffee 84 Large Coffcc Sheet1 + # E 2 - + 117%