IS130 Building a Decision Support System with MS Excel:
A Spreadsheet Modeling Assignment
Your small retail shop, Bike Shop, specializes in high-end mountain bikes. At this time your store does not
have any other products. You buy them from the distributors and stock them in your shop. You have one
employee beside yourself to help assemble and sell these bicycles. You need to have a way of monitoring
the operations of this business and calculate your projected profit (or loss) for the coming year. You would
like to know which bikes are the most profitable, and you need to be able to see how other costs such as
rent and utilities might affect your overall net profit or loss within that time frame.
You have decided to develop a Decision Support System (DSS) using an MS Excel spreadsheet. The system
should provide an instant answer as to the profitability of a given product at a given cost or selling price. It
must also allow you to examine different scenarios when manipulating the set of key variables such as
markup, rent, and utilities. The selling price is based on a percentage markup over the cost and is derived
by a formula.
Your task is to enter all of the required data and create the required processing formulas that make up the
DSS (aka DSS Model).
Manufacturer Model Projected Sales
GBI DXT 28
GBI PXT 55
GBM Tiger 48
Whitman Tailwind 30
Peterson PBY 60
Talbot Lago 2 18
Manufacturer Model Cost Each Total cost
GBI DXT $ 1,124.00 $????
GBI PXT $ 1,085.35 $????
GBM Tiger $ 987.60 $????
Whitman Tailwind $ 1,350.00 $????
Peterson PBY $ 886.50 $????
Talbot Lago 2 $ 1,495.00 $????
Total Cost Of Projected Sales: $????
Manufacturer Model Selling Price Sales
GBI DXT $??? $???
GBI PXT $??? $???
GBM Tiger $??? $???
Whitman Tailwind $??? $???
Peterson PBY $??? $???
Talbot Lago 2 $??? $???
Total Projected Sales: $???
Fixed Costs per Year
Rent $ 21,000.00
Utilities $ 2,900.00
Payroll $ 62,500.00
Total Fixed Costs $???
Balance Sheet Calculations
Total Projected Sales $???
Total Cost of Projected Sales $???
Gross Profit $???
Total Fixed Costs $???
Net Profit (Loss) $???
Your Tasks and the Expected Outcome:
Develop a spreadsheet model that will determine the results indicated based on the data provided above.
This must be done by providing cell references (fix location and absolute) and formulas in the Excel
spreadsheet. All of the items shown as $??? must be calculated using a formula or obtained by inserting a
cell reference that provides a link to another cell.
The model must recalculate the results when any of the input values that were provided are changed. This
is required in order to illustrate the principal of “What-If” analysis.
Sales Income Chart:
Develop a chart to graphically illustrate the total Sales Income for each model. A change in the input
parameters must impact the chart.
Use the DSS model you created to help the Bike Shop decide on a strategy that would make them
profitable. In coming up with possible actions to take, use the figure at the end of this document as a
guideline to help you come up with three options that the Bike Shop could take and use the DSS model to
test each options.
Recommendation Text Box:
When done with the analysis of all of the possible options to increase profit, write a paragraph in a text
box (insert textbox shape) with the options the Bike Shop has to increase profit. Be specific on the
changes that each option will bring to the Net Profit (loss) of the business. Discuss the pros and cons of
each option. Select the best option (or combination) and state it as a recommendation for the Bike Shop
Note: In selecting and stating your recommendations, be critical and ensure the recommendation is
feasible and will bring about the desired results.
1. You must upload a working Excel file (.xlsx) through the Assignments Dropbox link on Blackboard. Be creative and use the capabilities of Excel to develop an informative and user friendly DSS model
that can really help a decision maker.
2. The model will be tested by changing the values of the parameters and observing the results. It must be apparent that your model can be used for “What-If” analysis.
The Decision Making Process: