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 froma 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 Amazn 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 fromyou without having to travel to your store. You want to see itf offering free delivery on orders over $35 will be a good decision for yourbusiness. 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 fouryears 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 s 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 theprevious year starting in 2020, ifthe economy is weak, competition is intense, and you do not implement free delivery, you expect sales to grow byjust 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 ata 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 ofgoods 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 ofgoods sold will increase by 1.5% over the previousyear. 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 outl weak or inflation is high, the government will incentivize businesses to continue spending money by settingthe business tax rate at 30%. Otherwise, the rate willbe 35%. ook and the inflation rate. When the economy is 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 woulduse "IF this ANDthat" 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. =IF(AND(A1="this",B1="that''),''x","") 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 loan amount will be $120,000 and the loan interest rate will be 3%. The default values for the inputs should be w, 196, 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-Fwill 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 2. Scenario Manager Once you have completed the template, you will need torun your DSS model through the Scenario Manager to generate all the possiblecombination of inputs and produce a tabular summary of the results. You will followthe 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 columin N. Scsew 1% 1% 2% 1% 1% Cempetition M-mild, eintense Net irceme ahe Taxes fwith delivery Ende, yo. .sh on Hardewit delivery) Net income after Taes fwithout delivery siS 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 froma 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 Amazn 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 fromyou without having to travel to your store. You want to see itf offering free delivery on orders over $35 will be a good decision for yourbusiness. 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 fouryears 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 s 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 theprevious year starting in 2020, ifthe economy is weak, competition is intense, and you do not implement free delivery, you expect sales to grow byjust 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 ata 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 ofgoods 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 ofgoods sold will increase by 1.5% over the previousyear. 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 outl weak or inflation is high, the government will incentivize businesses to continue spending money by settingthe business tax rate at 30%. Otherwise, the rate willbe 35%. ook and the inflation rate. When the economy is 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 woulduse "IF this ANDthat" 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. =IF(AND(A1="this",B1="that''),''x","") 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 loan amount will be $120,000 and the loan interest rate will be 3%. The default values for the inputs should be w, 196, 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-Fwill 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 2. Scenario Manager Once you have completed the template, you will need torun your DSS model through the Scenario Manager to generate all the possiblecombination of inputs and produce a tabular summary of the results. You will followthe 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 columin N. Scsew 1% 1% 2% 1% 1% Cempetition M-mild, eintense Net irceme ahe Taxes fwith delivery Ende, yo. .sh on Hardewit delivery) Net income after Taes fwithout delivery siS