Excel DSS Case Study Instructions Overview: Like the tutorial, you are the owner of Rebooks and More, a second-hand bookstore that resells textbooks and an assortment of other goods that appeal to college students. In the tutorial, you created an Excel-based decision support (DSS) tool to demonstrate to a local bank that your business would benefit from a loan to implement an app that your customers could use to search your existing inventory and make purchases for pickup at the store. This would allow you to carry and sell more inventory without having to increase shelf space and would also allow you to better compete with online resellers like Amazon and Chegg in your local market. You were asking the bank for $120,000 to develop and deploy the app and the bank had offered you a rate of 3% for a five year term. In this case study, it is now one year later in 2019 and you have successfully developed and implemented the app. The app has been more successful than you predicted and the economy has weakened. The combination of these two factors has led to a significant growth in your business over the last 12 months and you are hoping to capitalize on that momentum by initiating delivery services so customers will be able to purchase from you without having to travel to your store. You want to see if offering free delivery on orders over $35 will be a good decision for your business. You decide to construct a decision support (DSS) tool based on the analysis you did last year for the bank, but for this analysis, you want to look at the next four years of data. You will use the following assumptions for sales, cost of goods sold, and the business tax rate for potential states of the economy, inflation, and competition. Sales (with delivery) If the economy is weak, competition is mild, and you implement free delivery, you expect sales to grow by 22% over the previous year starting in 2020. If the economy is weak, competition is intense, and you implement free delivery, you expect sales to grow by just 10% each year. If the economy is strong, competition is mild, and you implement free delivery, you expect sales to grow by 18%. If the economy is strong, competition is intense, and you implement free delivery, you expect sales to grow by just 4%. Sales (without delivery) If the economy is weak, competition is mild, and you do not implement free delivery, you expect sales to grow by 18% over the previous year starting in 2020. If the economy is weak, competition is intense, and you do not implement free delivery, you expect sales to grow by just 6% each year. If the economy is strong, competition is mild, and you do not implement free delivery, you expect sales to grow by 14% If the economy is strong, competition is intense, and you do not implement free delivery, you expect sales to actually decline by 2%. Cost of Goods Sold (with delivery) If inflation is high at a rate of 3% and you implement free delivery, then cost of goods sold will increase by 3.5% over the previous year. If inflation is moderate at a rate of 2% and you implement free delivery, then cost of goods sold will increase by 2.5% over the previous year. If inflation is low at a rate of 1% and you implement free delivery, then cost of goods sold will increase by 1.5% over the previous year. Cost of Goods Sold (without delivery) If inflation is high at a rate of 3% and you do not implement free delivery, then cost of goods sold will increase by 2.5% over the previous year. If inflation is moderate at a rate of 2% and you do not implement free delivery, then cost of goods sold will increase by 1.5% over the previous year. If inflation is low at a rate of 1% and you do not implement free delivery, then cost of goods sold will increase by .5% over the previous year. The business tax rate will vary based on economic outlook and the inflation rate. When the economy is weak or inflation is high, the government will incentivize businesses to continue spending money by setting the business tax rate at 30%. Otherwise, the rate will be 35%. DSS Structure and Template Information 1. Spreadsheet The case study template contains the same sections as the tutorial template. You can refer back to the DSS tutorial walkthrough for information on those sections. The main differences in template structure are the addition of competition to the inputs, the business loan payment now applies with or without delivery, and the scenario covers four years instead of three. Hint: During the tutorial, we used an IF statement to calculate the Sales. For this assessment, we have multiple conditions and would use "If this AND that". If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result is TRUE, and (optionally) do something else if the result of the test is FALSE O=IF(AND(A1="this" B1="that"),"x","") I Start by entering your name in cell A2 You will need to format all monetary values in Accounting format with no decimal places, Percentage values should be in the Percentage format with no decimal places. Input values should be centered The loan amount will be $120,000 and the loan interest rate will be 3%. The default values for the inputs should be W, 1%, and M. The calculations sections contain some starting values in column B for 2019. Sales in 2019 is $498,800, cost of goods sold is $350,906, and cost of goods sold (as a % of sales) is 70%. End-of-year cash on hand in 2019 is $115,184. The rest of the cells in columns C-F will require formulas. Many will be similar to the formulas in the tutorial so you can refer back to the tutorial walkthrough for guidance. The values produced by the formulas you create with the default inputs should match those in the DSS Case Study Check Sheet that is included with these instructions. BIS 300 - Excel DSS Case Study 2 2. Scenario Manager Once you have completed the template, you will need to run your DSS model through the Scenario Manager to generate all the possible combination of inputs and produce a tabular summary of the results. You will follow the same steps you learned in the DSS tutorial to set up the Scenario Manager, so you can refer back to the DSS tutorial walkthrough for guidance. For the case study, there are 12 possible combinations of inputs and you are interested in the same four outputs as the tutorial. Once you have constructed the scenario summary you will need to edit it in the same way you did in the tutorial. The figure below shows you what the first several columns of the scenario summary should look like. The summary should extend to column N. Scenario Summary weak fondy Wealty Wesh Economy wesem Wemy Weal nomy strong con my strong com with Low with with Moderate with Moderate with with High with low with Law antion and on and station and inflation and Wation and inflation and inflation and inflation and Mid M Intense Mid Inse MI Intense Competition Competition Competition Competition Competition Competition Competition Competition nene w SCS $25 $C$10 w IN M 5 w 13 1 w 2 w 2x M w 3% Changing Calle Economic Outlook Wweak, Strong Inflation Rate (1-low, moderate high Competition (Memild, britanse Result Cells Net Income after Teres with delivery End-of-year Cash on Hand (with delivery) Net Income wher Teres (without delivery End-of-year Cash on Hand without delivery 5 15 1 M M 10.464 $F$135 $F$145 SF5155 SE5165 180,4055 678.0 5 175.193 $ 666 0025 113,0615 5267765 107,6825 5128895 157.500 5 631.5685 155,664 $ 624 4255 97,8715 492,7115 94.966 5 483 1255 133,8335 583,300 $ 135.5485 $81.9405 82.2305 457.9355 81.8675 152,7485 144.5455 5880505 139,5235 575,6865 437,702 68,465 406,318 End-of-year cash on Hand we delivery Net Income after Taxes (without delivery) End-of-year Cash on Hand (without delivery) 3514 $F$15$ $F$165 TS 3287705 175.1935 107,6825 666,0025 512.889 $ 6356 155,664 5 624 425 5 94,9665 483,125 5 5833300457355 500S 135,548 $ 81,867 $ 139,52 581.949 $ 452,748 $ 575.680 3. Interpreting the Results Your last task is to provide your interpretation of the results. I would like you to do this by composing answers to the following questions in a Word document. What combination of inputs is best for you as the owner of ReBooks and More and why is that combination best? Based on the results of your analysis, should you implement free delivery? Explain why you should or should not implement free delivery. Your analysis makes some assumptions about the impact of the economy, inflation, and competition on your business. Explain those assumptions. . Grading Rubric Please see this assignment on Canvas for the grading rubric. You are required to use the template that I will include with these instructions. If you do not use the provided template, you will receive a zero (O) for the assessment. The template is labeled, Excel_DSS_CS_Template.xlsx, and available on our canvas site. B C D 2022 2023 2019 $120,000.00 3% 2020 NA NA 2021 NA NA NA NA NA NA 5 3 Constants 4 Loan Amount Loan Interest Rate 6 7 Inputs 8 Economic Outlook (W-weak, Sestrong) Inflation Rate (1%-low, 2%=moderate, 3%=high) 10 Competition (M-mild, l-intense) 2020 2019 NA NA NA 2021 NA NA NA w 1% M 2022 NA NA NA 2023 NA NA NA 9 11 2020 2021 2022 2023 13 2019 NA NA NA NA $ $ $ $ $ $ $ $ $ $ $ $ 2020 2021 2022 2023 2019 $ 498,800 $ 350,906 70% NA 2020 2021 2022 2023 12 Summary of Key results Net Income after Taxes (with delivery) 14 End-of-year Cash on Hand (with delivery) 15 Net Income after Taxes (without delivery) 16 End-of-year Cash on Hand (without delivery) 17 18 Calculations (with delivery) 19 Sales 20 Cost of Goods Sold 21 Cost of Goods Sold (as a % of Sales) 22 Business Tax Rate 23 24 Calculations (without delivery) 25 Sales 26 Cost of Goods Sold 27 Cost of Goods Sold (as a % of Sales) 28 Business Tax Rate 29 30 Income and Cash Flows (with delivery) 31 Beginning-of-year Cash on Hand 32 Sales Cost of Goods Sold 34 Business Loan Payment 35 Income before Taxes 36 Income Tax Expense 37 Net Income after Taxes Sheet1 + 2019 $ 498,800 $ 350,906 70% NA 2020 2021 2022 2023 33 2019 NA NA NA NA NA NA NA 29 2020 2021 2022 2023 2019 NA NA NA NA NA NA NA 2020 2021 2022 2023 30 Income and Cash Flows (with delivery) 31 Beginning-of-year Cash on Hand 32 Sales 33 Cost of Goods Sold 34 Business Loan Payment 35 Income before Taxes 36 Income Tax Expense 37 Net Income after Taxes 38 End-of-year Cash on Hand 39 40 Income and Cash Flows (without delivery) 41 Beginning-of-year Cash on Hand 42 Sales 43 Cost of Goods Sold 44 Business Loan Payment 45 Income before Taxes 46 Income Tax Expense 47 Net Income after Taxes 48 End-of-year Cash on Hand 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 2019 NA NA NA NA NA NA NA Sheet1 + Excel DSS Case Study Instructions Overview: Like the tutorial, you are the owner of Rebooks and More, a second-hand bookstore that resells textbooks and an assortment of other goods that appeal to college students. In the tutorial, you created an Excel-based decision support (DSS) tool to demonstrate to a local bank that your business would benefit from a loan to implement an app that your customers could use to search your existing inventory and make purchases for pickup at the store. This would allow you to carry and sell more inventory without having to increase shelf space and would also allow you to better compete with online resellers like Amazon and Chegg in your local market. You were asking the bank for $120,000 to develop and deploy the app and the bank had offered you a rate of 3% for a five year term. In this case study, it is now one year later in 2019 and you have successfully developed and implemented the app. The app has been more successful than you predicted and the economy has weakened. The combination of these two factors has led to a significant growth in your business over the last 12 months and you are hoping to capitalize on that momentum by initiating delivery services so customers will be able to purchase from you without having to travel to your store. You want to see if offering free delivery on orders over $35 will be a good decision for your business. You decide to construct a decision support (DSS) tool based on the analysis you did last year for the bank, but for this analysis, you want to look at the next four years of data. You will use the following assumptions for sales, cost of goods sold, and the business tax rate for potential states of the economy, inflation, and competition. Sales (with delivery) If the economy is weak, competition is mild, and you implement free delivery, you expect sales to grow by 22% over the previous year starting in 2020. If the economy is weak, competition is intense, and you implement free delivery, you expect sales to grow by just 10% each year. If the economy is strong, competition is mild, and you implement free delivery, you expect sales to grow by 18%. If the economy is strong, competition is intense, and you implement free delivery, you expect sales to grow by just 4%. Sales (without delivery) If the economy is weak, competition is mild, and you do not implement free delivery, you expect sales to grow by 18% over the previous year starting in 2020. If the economy is weak, competition is intense, and you do not implement free delivery, you expect sales to grow by just 6% each year. If the economy is strong, competition is mild, and you do not implement free delivery, you expect sales to grow by 14% If the economy is strong, competition is intense, and you do not implement free delivery, you expect sales to actually decline by 2%. Cost of Goods Sold (with delivery) If inflation is high at a rate of 3% and you implement free delivery, then cost of goods sold will increase by 3.5% over the previous year. If inflation is moderate at a rate of 2% and you implement free delivery, then cost of goods sold will increase by 2.5% over the previous year. If inflation is low at a rate of 1% and you implement free delivery, then cost of goods sold will increase by 1.5% over the previous year. Cost of Goods Sold (without delivery) If inflation is high at a rate of 3% and you do not implement free delivery, then cost of goods sold will increase by 2.5% over the previous year. If inflation is moderate at a rate of 2% and you do not implement free delivery, then cost of goods sold will increase by 1.5% over the previous year. If inflation is low at a rate of 1% and you do not implement free delivery, then cost of goods sold will increase by .5% over the previous year. The business tax rate will vary based on economic outlook and the inflation rate. When the economy is weak or inflation is high, the government will incentivize businesses to continue spending money by setting the business tax rate at 30%. Otherwise, the rate will be 35%. DSS Structure and Template Information 1. Spreadsheet The case study template contains the same sections as the tutorial template. You can refer back to the DSS tutorial walkthrough for information on those sections. The main differences in template structure are the addition of competition to the inputs, the business loan payment now applies with or without delivery, and the scenario covers four years instead of three. Hint: During the tutorial, we used an IF statement to calculate the Sales. For this assessment, we have multiple conditions and would use "If this AND that". If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result is TRUE, and (optionally) do something else if the result of the test is FALSE O=IF(AND(A1="this" B1="that"),"x","") I Start by entering your name in cell A2 You will need to format all monetary values in Accounting format with no decimal places, Percentage values should be in the Percentage format with no decimal places. Input values should be centered The loan amount will be $120,000 and the loan interest rate will be 3%. The default values for the inputs should be W, 1%, and M. The calculations sections contain some starting values in column B for 2019. Sales in 2019 is $498,800, cost of goods sold is $350,906, and cost of goods sold (as a % of sales) is 70%. End-of-year cash on hand in 2019 is $115,184. The rest of the cells in columns C-F will require formulas. Many will be similar to the formulas in the tutorial so you can refer back to the tutorial walkthrough for guidance. The values produced by the formulas you create with the default inputs should match those in the DSS Case Study Check Sheet that is included with these instructions. BIS 300 - Excel DSS Case Study 2 2. Scenario Manager Once you have completed the template, you will need to run your DSS model through the Scenario Manager to generate all the possible combination of inputs and produce a tabular summary of the results. You will follow the same steps you learned in the DSS tutorial to set up the Scenario Manager, so you can refer back to the DSS tutorial walkthrough for guidance. For the case study, there are 12 possible combinations of inputs and you are interested in the same four outputs as the tutorial. Once you have constructed the scenario summary you will need to edit it in the same way you did in the tutorial. The figure below shows you what the first several columns of the scenario summary should look like. The summary should extend to column N. Scenario Summary weak fondy Wealty Wesh Economy wesem Wemy Weal nomy strong con my strong com with Low with with Moderate with Moderate with with High with low with Law antion and on and station and inflation and Wation and inflation and inflation and inflation and Mid M Intense Mid Inse MI Intense Competition Competition Competition Competition Competition Competition Competition Competition nene w SCS $25 $C$10 w IN M 5 w 13 1 w 2 w 2x M w 3% Changing Calle Economic Outlook Wweak, Strong Inflation Rate (1-low, moderate high Competition (Memild, britanse Result Cells Net Income after Teres with delivery End-of-year Cash on Hand (with delivery) Net Income wher Teres (without delivery End-of-year Cash on Hand without delivery 5 15 1 M M 10.464 $F$135 $F$145 SF5155 SE5165 180,4055 678.0 5 175.193 $ 666 0025 113,0615 5267765 107,6825 5128895 157.500 5 631.5685 155,664 $ 624 4255 97,8715 492,7115 94.966 5 483 1255 133,8335 583,300 $ 135.5485 $81.9405 82.2305 457.9355 81.8675 152,7485 144.5455 5880505 139,5235 575,6865 437,702 68,465 406,318 End-of-year cash on Hand we delivery Net Income after Taxes (without delivery) End-of-year Cash on Hand (without delivery) 3514 $F$15$ $F$165 TS 3287705 175.1935 107,6825 666,0025 512.889 $ 6356 155,664 5 624 425 5 94,9665 483,125 5 5833300457355 500S 135,548 $ 81,867 $ 139,52 581.949 $ 452,748 $ 575.680 3. Interpreting the Results Your last task is to provide your interpretation of the results. I would like you to do this by composing answers to the following questions in a Word document. What combination of inputs is best for you as the owner of ReBooks and More and why is that combination best? Based on the results of your analysis, should you implement free delivery? Explain why you should or should not implement free delivery. Your analysis makes some assumptions about the impact of the economy, inflation, and competition on your business. Explain those assumptions. . Grading Rubric Please see this assignment on Canvas for the grading rubric. You are required to use the template that I will include with these instructions. If you do not use the provided template, you will receive a zero (O) for the assessment. The template is labeled, Excel_DSS_CS_Template.xlsx, and available on our canvas site. B C D 2022 2023 2019 $120,000.00 3% 2020 NA NA 2021 NA NA NA NA NA NA 5 3 Constants 4 Loan Amount Loan Interest Rate 6 7 Inputs 8 Economic Outlook (W-weak, Sestrong) Inflation Rate (1%-low, 2%=moderate, 3%=high) 10 Competition (M-mild, l-intense) 2020 2019 NA NA NA 2021 NA NA NA w 1% M 2022 NA NA NA 2023 NA NA NA 9 11 2020 2021 2022 2023 13 2019 NA NA NA NA $ $ $ $ $ $ $ $ $ $ $ $ 2020 2021 2022 2023 2019 $ 498,800 $ 350,906 70% NA 2020 2021 2022 2023 12 Summary of Key results Net Income after Taxes (with delivery) 14 End-of-year Cash on Hand (with delivery) 15 Net Income after Taxes (without delivery) 16 End-of-year Cash on Hand (without delivery) 17 18 Calculations (with delivery) 19 Sales 20 Cost of Goods Sold 21 Cost of Goods Sold (as a % of Sales) 22 Business Tax Rate 23 24 Calculations (without delivery) 25 Sales 26 Cost of Goods Sold 27 Cost of Goods Sold (as a % of Sales) 28 Business Tax Rate 29 30 Income and Cash Flows (with delivery) 31 Beginning-of-year Cash on Hand 32 Sales Cost of Goods Sold 34 Business Loan Payment 35 Income before Taxes 36 Income Tax Expense 37 Net Income after Taxes Sheet1 + 2019 $ 498,800 $ 350,906 70% NA 2020 2021 2022 2023 33 2019 NA NA NA NA NA NA NA 29 2020 2021 2022 2023 2019 NA NA NA NA NA NA NA 2020 2021 2022 2023 30 Income and Cash Flows (with delivery) 31 Beginning-of-year Cash on Hand 32 Sales 33 Cost of Goods Sold 34 Business Loan Payment 35 Income before Taxes 36 Income Tax Expense 37 Net Income after Taxes 38 End-of-year Cash on Hand 39 40 Income and Cash Flows (without delivery) 41 Beginning-of-year Cash on Hand 42 Sales 43 Cost of Goods Sold 44 Business Loan Payment 45 Income before Taxes 46 Income Tax Expense 47 Net Income after Taxes 48 End-of-year Cash on Hand 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 2019 NA NA NA NA NA NA NA Sheet1 +