DSS Case Study Instructions
docx
keyboard_arrow_up
School
Northern Kentucky University *
*We aren’t endorsed by this school
Course
438
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
3
Uploaded by Lukem11568
Excel DSS Case Study
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 that I will include with these instructions.
The template will be labeled, DSS_CaseStudy_Template.xlsx. If you do not use the provided template you will receive a zero
(0) for the assessment.
Scenario Information
Like the tutorial, you are the owner of ReTurn the Page, 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 $75,000 to develop and deploy the app and the bank had offered you a rate of 5% for a six-year term.
In this case study, you have successfully developed and implemented the app and it is now the beginning of 2024. The economy has weakened bringing more customers to your store and the app has further increased engagement with your business. The combination of these two factors has led to meaningful growth in your business over the last two years 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 $50 will be a good decision for your business. You decide to construct a decision support (DSS) tool based on the analysis you did for the bank, but for this analysis, you want to look at the impact of implementing free delivery on the next four years from 2024 to 2027. You also realize that your analysis will be more accurate, if you factor in competition as a third input variable.
In addition to the assumptions from your original analysis regarding the economy and inflation, you expect that implementing free delivery will increase sales, but also increase cost of goods sold and that intense competition will have a negative effect on sales. 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 you implement free delivery, the expected effects on sales and cost of goods sold will be as follows:
If the economy is weak and competition is mild, you expect sales to grow by 12% over the previous year starting in 2024.
If the economy is weak and competition is intense, you expect sales to grow by 8%.
If the economy is strong and competition is mild, you expect sales to grow by 7%.
If the economy is strong and competition is intense, you expect sales to grow by 4%.
If inflation is high at a rate of 3%, then cost of goods sold will increase by 3.5% over the previous year starting in 2024.
If inflation is moderate at a rate of 2%, then cost of goods sold will increase by 2.5%.
If inflation is low at a rate of 1%, then cost of goods sold will increase by 1.5%.
If you do not implement free delivery, the expected effects on sales and cost of goods sold will be as follows:
If the economy is weak and competition is mild, you expect sales to grow by 9% over the previous year starting in 2024.
If the economy is weak and competition is intense, you expect sales to grow by 5%.
If the economy is strong and competition is mild, you expect sales to grow by 5%.
If the economy is strong and competition is intense, you expect sales to grow by 2%.
If inflation is high at a rate of 3%, then cost of goods sold will increase by 2.5% over the previous year starting in 2024.
If inflation is moderate at a rate of 2%, then cost of goods sold will increase by 1.5%.
If inflation is low at a rate of 1%, then cost of goods sold will increase by .5%. Implementing or not implementing free delivery will not affect the business tax rate, which is based solely on economic outlook and the inflation rate. Specifically, 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 24%. Otherwise, the rate will be 32%.
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 and the business loan payment now applies with or without delivery.
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 $75,000 and the loan interest rate will be 5%.
The default values for the inputs should be W, 1%, and M.
The calculations sections contain some starting values in column B for 2023. Sales in 2023 is $502,684, cost of goods sold is $343,233, and cost of goods sold (as a % of sales) is 68.28%.
The income and cash flows sections contain some starting values in column B for 2023. Operating expenses in 2023 are $88,294 and end-of-year cash on hand is $68,460. Operating Expenses in rows 34 and 45 are estimated to increase by a fixed amount of 2% each year.
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 11 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?
What are the anticipated effects on your business of offering free delivery?
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 (e.g., John Doe)
Deliverables
Name your Excel file and your Word document using your last name and the name of the assessment (e.g., Smith_DSSCaseStudy.xlsx).
Submit both files through the Excel DSS Case Study submission page on Canvas.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help