Overview: Rebooksand More, a second- hand bookstore thatresels uke the tutorial, you are the owner of textbooks and an assortment of other goods that appeai to college students. In the tutorial, you created an Excel-based benefit from a loan to implement an app that inventory and make purchases for pickup at the store. This would allow you to carry and sell mone inventory without having to increa decision support (DSS) tool to demonstrate to a local bank that your business would your customers could use to search your existing se shelf space and would also allow you to better compete with line 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 delvery services so customers will be able to purchase from you without having totravel to your store. You want to seeif fering freedelivery on ordersover $35 will be a good decision for your business. You decide to construct a decision support (DSS) tool based on the analysisyou did last year forthe bank, but forthis 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 by22% 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 b118%. 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 donotimplement free delvery, you expect sales to grow by 18% over the previousyear starting in 2020. "the economy is weak.competiton is intense. dolphin18 nd you do not implement freedel ery you expect sales to grow by just 6% each year if the economy is strong, competition is mild, and you donot implement free delivery, you expect sales to grow by 14%. if the economyis strong competitionis intense, andyou donot implement free delivery, you expect sales to actually decline by 2%. dolphin's 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 ofgoods 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 ot 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 basedon economicoutlook and the inflation rate. When the economy is weak or inflation is high, the government will incentivize businesses to continuespending money setting the business tax rate at 30%. Otherwise, the rate will be 35%. DSS Structure and Template Information 1. Spreadsheet The case study template containsthe samesections as the tutorialtemplate. 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 fouryears instead of three Hint: During the tutorial, we used aniF statement to calculate the Sales. For this assessment, we have multiple conditions and would use "IF this AND that". . ifyou want to do something specificwhen twoor more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions witha test, then taxe one action if the result is TRUE, and (optionally) do something elseifthe result of the testis FLSE o Start by entering your name in cellA2 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% dolphin . The default values f or 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 S350,906, and cost of goods sold (as a % of sales) is 70%. End-of-year cash on hand in 2019 is S11 5,184. The rest of the cells in columns C. F will require formulas. Many will be similar to the formulas in the tutorial soyou 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 to run 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 stepsyou 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 3% 3. Interpreting the Results Your last task is to provide your interpretation of the results. Iwould like you to do this by composing answersto the following questionsin a Word document . What combination of inputs is best for you as the owner of ReBooks and More and why is that combination best should or should notimplement freedelvery competition on your business Exolain those assumptions. .Based on the results of your analysis. hould you implement free delivery? Explain why you Your analysis makes some assumptions about the impact of the economy, inflation, and 3 Constants 4 Loan Amount 5 Loan Interest Rate 2019 2021 NA 2022 NA 2023 NA NA 7 Inputs 2019 2020 2021 2022 2023 NA 8 Economic Outlook (W-weak, S-strong) NA NA NA NA Inflation Rate (1%-low, 2%-moderate, 3%-high) 10 Competition (M-mild, I-intense NA 2019 2020 2021 2022 2023 Summary of Key results 14 End-of-year Cash on Hand (with delivery) 16 End-of-year Cash on Hand (without delivery) 12 NA NA NA 13 Net Income after Taxes (with delivery) 15 Net Income after Taxes (without delivery) 17 2019 2020 2021 2022 2023 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 28 2019 2020. 2021 2022 2023 Cost of Goods Sold (as a % of Sales) Business Tax Rate 28 Business Tax Rate Income and Cash Flows (with delivery) 2019 2020 2021 2022 2023 30 31 Beginning-of-year Cash on Hand 32 Sales 33 Cost of Goods Sold NA NA NA NA NA 34 Business Loan Payment 35 Income before Taxes 36 Income Tax Expense 37 Net Income after Taxes NA 38 End-of-year Cash on Hand 39 20212022 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 46 Income Tax Expense 47 Net Income after Taxes 2019 2020 2023 NA NA NA NA NA NA Income before Taxes 48 End-of-year Cash on Hand Overview: Rebooksand More, a second- hand bookstore thatresels uke the tutorial, you are the owner of textbooks and an assortment of other goods that appeai to college students. In the tutorial, you created an Excel-based benefit from a loan to implement an app that inventory and make purchases for pickup at the store. This would allow you to carry and sell mone inventory without having to increa decision support (DSS) tool to demonstrate to a local bank that your business would your customers could use to search your existing se shelf space and would also allow you to better compete with line 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 delvery services so customers will be able to purchase from you without having totravel to your store. You want to seeif fering freedelivery on ordersover $35 will be a good decision for your business. You decide to construct a decision support (DSS) tool based on the analysisyou did last year forthe bank, but forthis 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 by22% 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 b118%. 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 donotimplement free delvery, you expect sales to grow by 18% over the previousyear starting in 2020. "the economy is weak.competiton is intense. dolphin18 nd you do not implement freedel ery you expect sales to grow by just 6% each year if the economy is strong, competition is mild, and you donot implement free delivery, you expect sales to grow by 14%. if the economyis strong competitionis intense, andyou donot implement free delivery, you expect sales to actually decline by 2%. dolphin's 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 ofgoods 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 ot 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 basedon economicoutlook and the inflation rate. When the economy is weak or inflation is high, the government will incentivize businesses to continuespending money setting the business tax rate at 30%. Otherwise, the rate will be 35%. DSS Structure and Template Information 1. Spreadsheet The case study template containsthe samesections as the tutorialtemplate. 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 fouryears instead of three Hint: During the tutorial, we used aniF statement to calculate the Sales. For this assessment, we have multiple conditions and would use "IF this AND that". . ifyou want to do something specificwhen twoor more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions witha test, then taxe one action if the result is TRUE, and (optionally) do something elseifthe result of the testis FLSE o Start by entering your name in cellA2 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% dolphin . The default values f or 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 S350,906, and cost of goods sold (as a % of sales) is 70%. End-of-year cash on hand in 2019 is S11 5,184. The rest of the cells in columns C. F will require formulas. Many will be similar to the formulas in the tutorial soyou 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 to run 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 stepsyou 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 3% 3. Interpreting the Results Your last task is to provide your interpretation of the results. Iwould like you to do this by composing answersto the following questionsin a Word document . What combination of inputs is best for you as the owner of ReBooks and More and why is that combination best should or should notimplement freedelvery competition on your business Exolain those assumptions. .Based on the results of your analysis. hould you implement free delivery? Explain why you Your analysis makes some assumptions about the impact of the economy, inflation, and 3 Constants 4 Loan Amount 5 Loan Interest Rate 2019 2021 NA 2022 NA 2023 NA NA 7 Inputs 2019 2020 2021 2022 2023 NA 8 Economic Outlook (W-weak, S-strong) NA NA NA NA Inflation Rate (1%-low, 2%-moderate, 3%-high) 10 Competition (M-mild, I-intense NA 2019 2020 2021 2022 2023 Summary of Key results 14 End-of-year Cash on Hand (with delivery) 16 End-of-year Cash on Hand (without delivery) 12 NA NA NA 13 Net Income after Taxes (with delivery) 15 Net Income after Taxes (without delivery) 17 2019 2020 2021 2022 2023 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 28 2019 2020. 2021 2022 2023 Cost of Goods Sold (as a % of Sales) Business Tax Rate 28 Business Tax Rate Income and Cash Flows (with delivery) 2019 2020 2021 2022 2023 30 31 Beginning-of-year Cash on Hand 32 Sales 33 Cost of Goods Sold NA NA NA NA NA 34 Business Loan Payment 35 Income before Taxes 36 Income Tax Expense 37 Net Income after Taxes NA 38 End-of-year Cash on Hand 39 20212022 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 46 Income Tax Expense 47 Net Income after Taxes 2019 2020 2023 NA NA NA NA NA NA Income before Taxes 48 End-of-year Cash on Hand