Comments AutoSave $ Home Insert Draw Page Layout Calibri (Body) 11 BIUL Formulas A A A Data = E Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center So% Share Ey O 4 Delete- Sort & iller Find & Select Ideas Sensitivity Formatting as Table Styles Farmat X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x fx PCTGILTOTT LACCTW TICTS THE ICECRL ww w as y you I want OTC W C i var Waarom WCCU TOC TIC WC. Again, we are keeping it simple in that we are not including your baked goods, discounts and price changes over the six weeks. You will use your decision period 9 caffee prices and type of coffee (if you changed. We are all consumers and have at one time or another paid an excessive amount for a product and have ignored price increases because you prefer their product over others. This is normal behavior. When you purchase for convenience, the price is usually higher than with your normal source. Loyalty only goes so far, there is a price point where you will consider other stores or locations for your next purchase. This is the same for your customers some will prefer your caf even after a nominal price increase, others may change their habits, and a lew may choose another caf to buy their coffee. Your caf has brand awareness and generally satisfied customers. You want to earn more revenue which will result in a higher gross profit. Don't confuse gross profit (margin) with net profit. When increasing net profit, you have many options since it involves money you spend on servers, rent, advertising to name a few. Ta increase your gross profit, you anly have two options. Either increase revenue or lawer cost of gaads sold. We are going to attempt to increase aur revenue by raising our prices for each cup size. For this exercise we are not worried about why you want more revenue. It could be for capital Improvements, better benefits, salary increases and many more reasons. Before we can forecast our expected grass profit after increasing our coffee prices, we must have a handle on our current sales and understand how they will change when we increase our coffee prices. We are fortunate to have a working relationship with the local College of Business. A team of students have spent the last six weeks surveyine your customers on how they would react to an increase in the price of a cup of coffee. They have provided you with the reaction to your potential increase in coffee prices. They asked the customers how they would react if you increased the price by 5%, 10% and 15%. 20 They were altered three responses: No change Reduce purchases by 30% per week Go to another caf 24 Once you have determined your gross profit based on the historical coffee sales data you will determine the impact of increasing your coffee prices focusing on a large cup of coffee) using the preference changes that the students calculated based on the survey responses. Finally, your coffee supplier increases their coffee prices and you will have to the cost of the coffee for a large cup of coffee. You need to add the values to the green filled cells. That data comes from your Biz Cafe data. Under results I have added your sales for each cup size and like in Excel Operations # 5 you can find cost and pricing information in Decision Period 9. 32 You need to determine the resutls in the blue fillled cells using the information on the spreadsheet. This assignment is designed to demonstrate the power of referencing cells that already have a value you need for the calculations. We are expecting to see function and formulas. Don't just type in an answer. Grading Rubric for each question: This assignment is worth 8% of your grade. The total points for this assignment is 15 points with tasks (each task is worth 3 points) 29 40 Points for each question Sheet1 + # 3 - 0 + 122% Comments AutoSave $ Home Insert Draw Page Layout Calibri (Body) 11 BIUL Formulas A A A Data = E Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center So% Share Ey O 4 2 Peste Delete Format 2 Sort & File Find & Select Ideas Sensitivity Formatting Table Styles X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x v fx 5% incre Price by Cup Size (use Week 9 Prices) Small Medium $ Large $ 3.20 3.78 4.14 New Cup Prices by Size Small Medium Large 54 Coffee Sales by Cup for Six Weeks 55 Week 4 Small Coffee Medium Coffee Large Coffee Week 5 Small Coffee Medium Coffee Large Coffee Small Coffee Medium Coffee Large Coffee Small Coffee Medium Coffee Large Coffee 67 Week 8 Small Coffee Medium Coffee T Large Coffee 70 Week 9 Small Coffee Medium Coffee Large Coffee 1 Cost of Goods Sold (GOGS) Cost of each Cup Small Cup Medium Cup Large Cup Customer Response to Coffee Price C1 5% incre No change Reduce purchases by 30% per week Go to another caf $ $ $ 6,380.80 6,463.80 3,949.56 0 1556 0 847 735 343 1437 1258 514 1543 1414 629 1929 1 647 931 1994 1710 9 54 Week Cost of Coffee per pound Most Recent (Decision (9) $ 6.00 New Cost of Coffee per pound after Supplier New price per pound (4% increase per pound) New price per pound (4% increase per pound) 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 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) 74 To Compltete 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 F55). 77 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 97 80 Sheet1 + # E 2 - + 137% AutoSave Home Insert Comments $ Draw Page Layout Calibri (Body) 11 B I V . E Formulas A A A Data = Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General 2 Merge & Center S % Share Ey O 4 ULT 2 Peste 8 Delete Format 2 Sort & Filter Find & Select Ideas Sensitivity Formatting as Table Styles X V Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x fx M N O P Q 52 54 up Size (use Week 9 Prices) 5% increase 10% increase 15% increase New Cup Prices by Size Small Medium 56 $ 4.14 59 oods Sold (GOGS) 60 ach Cup 61) 62 Cup 63) Customer Response to Coffee Price C1 5% increase No change Reduce purchases by 30% per week 12% Go to another caf 1% 10% increase 15% increase 79% 68% 18% 22% 3% $ $ $ 6,380.80 6,463.80 3,949.56 1096 65 offee per pound 66 ent (Decision (9) 67 $ 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) 0.10 0.19 0.29 New 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) 69 st for each Cup Size 70 r .016 lbs. of Coffee (Small) $ 71 r.032 lbs. of Coffee (Medium) $ 72 r.048 lbs. of Coffee (Large) $ 73 74 ? when completing the tasks. 75 ithn" Coffee Sales by Cup for Six Weeks" 76 the value of F55). 77 78 Sheet1 + # 3 - - - + 137% AutoSave Home Insert $ Draw Page Layout Calibri (Body) 11 Comments Formulas A A Data = Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center $ % Share Ey O 4 2 Peste Delete Farmat 2 Sort & Filter Find & Select Ideas Sensitivity Formatting as Table Styles X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x fx 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 97 80 81 Cups Size Average Weekly Sales Price Per Cup Weekly Revenue 82 Small Coffee $ 1291.67 3.2 $ 4,133.33 83 Medium Coffee $ 1,386.67 3.78 $ 5,241.60 84 Large Coffee $ 561.83 4.14 $ 2,325.99 Total Weekly Revenue using six week average sales 86 87 88 Task 2 89 90 What is your average weekly cost of goods sold? 91 Cups Size Average Weekly Sales froi COGS per Cup Total Cost of Goods Sold 92 Small Coffee $ 1,291.67 0.82 $ 1,059.17 93 Medium Coffee $ 1,386.67 0.78 $ 1,081.60 94 Large Coffee 561.83 1.17 $ 657.34 Total Weekly COGS using six week average $ 2,798.11 97 Task 3 98 What is your average weekly gross profit? 99 100 Total Revenue from Total Cost of Goods Sold 1 Average Weekly Gross Profit 101 $ 11,700.92 $ 2,798.11 $ 8,902.81 102 103 Task 4 104 What is your weekly gross profit for a large cup of coffee after raising the price? 105 Sheet1 + # 3 - 0 + 137% Share Comments AutoSave $ Home Insert Draw Page Layout Calibri (Body) 11 BIUL Formulas A A A Data = E Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center So% Delete- Sort & File Find & Select Ideas Sensitivity Formatting Table Styles Format X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 + x fx 1,033.17 22 Sila Lonee 4.291.071 0.02 93 Medium Coffee 1,386.67 0.78 $ 94 Large Coffee 561.83 1.17 $ Total Weekly COGS using six week average $ 96 97 Task 3 98 What is your average weekly gross profit? 1,081.60 657.34 2,798.11 99 100 Total Revenue from Total Cost of Goods Sold 1 Average Weekly Gross Profit 101 $ 11,700.92 $ 2,798.11 $ 8,902.81 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 from New Average Weekly Sales appl New Revenue 107 5% 108 10% 109 15% 110 Task 5 111 Your coffee supplier has increased their coffee price by 4% per pound what is new coffee cost for a large cup of coffee? COGS per Cup New COGS Sold New Gross Profit 112 New Coffee Cost Per Amount of coffee pe New Cost of Coffee for a large Cup 113 Coffee Cost Per Pou Price increase 114 115 116 117 118 119 120 Sheet1 + # 3 - 0 + 137% Comments AutoSave $ Home Insert Draw Page Layout Calibri (Body) 11 BIUL Formulas A A A Data = E Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center So% Share Ey O 4 Delete- Sort & iller Find & Select Ideas Sensitivity Formatting as Table Styles Farmat X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x fx PCTGILTOTT LACCTW TICTS THE ICECRL ww w as y you I want OTC W C i var Waarom WCCU TOC TIC WC. Again, we are keeping it simple in that we are not including your baked goods, discounts and price changes over the six weeks. You will use your decision period 9 caffee prices and type of coffee (if you changed. We are all consumers and have at one time or another paid an excessive amount for a product and have ignored price increases because you prefer their product over others. This is normal behavior. When you purchase for convenience, the price is usually higher than with your normal source. Loyalty only goes so far, there is a price point where you will consider other stores or locations for your next purchase. This is the same for your customers some will prefer your caf even after a nominal price increase, others may change their habits, and a lew may choose another caf to buy their coffee. Your caf has brand awareness and generally satisfied customers. You want to earn more revenue which will result in a higher gross profit. Don't confuse gross profit (margin) with net profit. When increasing net profit, you have many options since it involves money you spend on servers, rent, advertising to name a few. Ta increase your gross profit, you anly have two options. Either increase revenue or lawer cost of gaads sold. We are going to attempt to increase aur revenue by raising our prices for each cup size. For this exercise we are not worried about why you want more revenue. It could be for capital Improvements, better benefits, salary increases and many more reasons. Before we can forecast our expected grass profit after increasing our coffee prices, we must have a handle on our current sales and understand how they will change when we increase our coffee prices. We are fortunate to have a working relationship with the local College of Business. A team of students have spent the last six weeks surveyine your customers on how they would react to an increase in the price of a cup of coffee. They have provided you with the reaction to your potential increase in coffee prices. They asked the customers how they would react if you increased the price by 5%, 10% and 15%. 20 They were altered three responses: No change Reduce purchases by 30% per week Go to another caf 24 Once you have determined your gross profit based on the historical coffee sales data you will determine the impact of increasing your coffee prices focusing on a large cup of coffee) using the preference changes that the students calculated based on the survey responses. Finally, your coffee supplier increases their coffee prices and you will have to the cost of the coffee for a large cup of coffee. You need to add the values to the green filled cells. That data comes from your Biz Cafe data. Under results I have added your sales for each cup size and like in Excel Operations # 5 you can find cost and pricing information in Decision Period 9. 32 You need to determine the resutls in the blue fillled cells using the information on the spreadsheet. This assignment is designed to demonstrate the power of referencing cells that already have a value you need for the calculations. We are expecting to see function and formulas. Don't just type in an answer. Grading Rubric for each question: This assignment is worth 8% of your grade. The total points for this assignment is 15 points with tasks (each task is worth 3 points) 29 40 Points for each question Sheet1 + # 3 - 0 + 122% Comments AutoSave $ Home Insert Draw Page Layout Calibri (Body) 11 BIUL Formulas A A A Data = E Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center So% Share Ey O 4 2 Peste Delete Format 2 Sort & File Find & Select Ideas Sensitivity Formatting Table Styles X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x v fx 5% incre Price by Cup Size (use Week 9 Prices) Small Medium $ Large $ 3.20 3.78 4.14 New Cup Prices by Size Small Medium Large 54 Coffee Sales by Cup for Six Weeks 55 Week 4 Small Coffee Medium Coffee Large Coffee Week 5 Small Coffee Medium Coffee Large Coffee Small Coffee Medium Coffee Large Coffee Small Coffee Medium Coffee Large Coffee 67 Week 8 Small Coffee Medium Coffee T Large Coffee 70 Week 9 Small Coffee Medium Coffee Large Coffee 1 Cost of Goods Sold (GOGS) Cost of each Cup Small Cup Medium Cup Large Cup Customer Response to Coffee Price C1 5% incre No change Reduce purchases by 30% per week Go to another caf $ $ $ 6,380.80 6,463.80 3,949.56 0 1556 0 847 735 343 1437 1258 514 1543 1414 629 1929 1 647 931 1994 1710 9 54 Week Cost of Coffee per pound Most Recent (Decision (9) $ 6.00 New Cost of Coffee per pound after Supplier New price per pound (4% increase per pound) New price per pound (4% increase per pound) 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 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) 74 To Compltete 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 F55). 77 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 97 80 Sheet1 + # E 2 - + 137% AutoSave Home Insert Comments $ Draw Page Layout Calibri (Body) 11 B I V . E Formulas A A A Data = Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General 2 Merge & Center S % Share Ey O 4 ULT 2 Peste 8 Delete Format 2 Sort & Filter Find & Select Ideas Sensitivity Formatting as Table Styles X V Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x fx M N O P Q 52 54 up Size (use Week 9 Prices) 5% increase 10% increase 15% increase New Cup Prices by Size Small Medium 56 $ 4.14 59 oods Sold (GOGS) 60 ach Cup 61) 62 Cup 63) Customer Response to Coffee Price C1 5% increase No change Reduce purchases by 30% per week 12% Go to another caf 1% 10% increase 15% increase 79% 68% 18% 22% 3% $ $ $ 6,380.80 6,463.80 3,949.56 1096 65 offee per pound 66 ent (Decision (9) 67 $ 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) 0.10 0.19 0.29 New 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) 69 st for each Cup Size 70 r .016 lbs. of Coffee (Small) $ 71 r.032 lbs. of Coffee (Medium) $ 72 r.048 lbs. of Coffee (Large) $ 73 74 ? when completing the tasks. 75 ithn" Coffee Sales by Cup for Six Weeks" 76 the value of F55). 77 78 Sheet1 + # 3 - - - + 137% AutoSave Home Insert $ Draw Page Layout Calibri (Body) 11 Comments Formulas A A Data = Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center $ % Share Ey O 4 2 Peste Delete Farmat 2 Sort & Filter Find & Select Ideas Sensitivity Formatting as Table Styles X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 x fx 78 Task 1 79 What is your average weekly revenue from Week 4 to Week 97 80 81 Cups Size Average Weekly Sales Price Per Cup Weekly Revenue 82 Small Coffee $ 1291.67 3.2 $ 4,133.33 83 Medium Coffee $ 1,386.67 3.78 $ 5,241.60 84 Large Coffee $ 561.83 4.14 $ 2,325.99 Total Weekly Revenue using six week average sales 86 87 88 Task 2 89 90 What is your average weekly cost of goods sold? 91 Cups Size Average Weekly Sales froi COGS per Cup Total Cost of Goods Sold 92 Small Coffee $ 1,291.67 0.82 $ 1,059.17 93 Medium Coffee $ 1,386.67 0.78 $ 1,081.60 94 Large Coffee 561.83 1.17 $ 657.34 Total Weekly COGS using six week average $ 2,798.11 97 Task 3 98 What is your average weekly gross profit? 99 100 Total Revenue from Total Cost of Goods Sold 1 Average Weekly Gross Profit 101 $ 11,700.92 $ 2,798.11 $ 8,902.81 102 103 Task 4 104 What is your weekly gross profit for a large cup of coffee after raising the price? 105 Sheet1 + # 3 - 0 + 137% Share Comments AutoSave $ Home Insert Draw Page Layout Calibri (Body) 11 BIUL Formulas A A A Data = E Review = = View D Biz Cafe Excel Capstone Assignemnt (2) Shape Format 2Wraa Text General Merge & Center So% Delete- Sort & File Find & Select Ideas Sensitivity Formatting Table Styles Format X Check for Updates Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates TextBox 2 + x fx 1,033.17 22 Sila Lonee 4.291.071 0.02 93 Medium Coffee 1,386.67 0.78 $ 94 Large Coffee 561.83 1.17 $ Total Weekly COGS using six week average $ 96 97 Task 3 98 What is your average weekly gross profit? 1,081.60 657.34 2,798.11 99 100 Total Revenue from Total Cost of Goods Sold 1 Average Weekly Gross Profit 101 $ 11,700.92 $ 2,798.11 $ 8,902.81 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 from New Average Weekly Sales appl New Revenue 107 5% 108 10% 109 15% 110 Task 5 111 Your coffee supplier has increased their coffee price by 4% per pound what is new coffee cost for a large cup of coffee? COGS per Cup New COGS Sold New Gross Profit 112 New Coffee Cost Per Amount of coffee pe New Cost of Coffee for a large Cup 113 Coffee Cost Per Pou Price increase 114 115 116 117 118 119 120 Sheet1 + # 3 - 0 + 137%