Due Date: December 6
Point Value: 70
This is an individual assessment. Therefore, you are NOT allowed to share files with each other related to this assessment, you are NOT allowed to work in groups on this assessment, and you are NOT allowed to give or receive any other type of help on this assessment except for help from the instructor or the resources explicitly provided to you by the instructor for use on this assessment. Your submission should be 100% your OWN work and by making a submission for this assessment you are implying that the work you have submitted is entirely your own!
For this assessment, you will construct a decision support (DSS) tool using Excel and answer some questions about the assessment scenario in a Word document. You will use the instructions in this document along with the DSS Case Study Check Sheet to complete your deliverables.
You are required to use the template included with these instructions. The template will be labeled, DSS_CaseStudy_Template_Fall2017.xlsx. If you do not use the provided template you will receive a zero (0) for the assessment. Name it “username_DSS_CaseStudy”
Scenario Information
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 2018 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.
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 2019. 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%.
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 2019. 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%.
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.
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
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.
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 2018. Sales in 2018 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 2018 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.
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.
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.
Your document should start with the following information at the top.
Interpreting the Results of the DSS Case Study
Your name
Assignment due date
Class number
