Computer Science

IS130 Building a Decision Support System with MS Excel:

A Spreadsheet Modeling Assignment

Problem Statement:

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).

Projected Data:

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: $????

Markup:

25%

Manufacturer Model Selling Price Sales

Income

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:

Spreadsheet:

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.

What-If Analysis:

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.

Further Instructions:

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:

Order now and get 10% discount on all orders above $50 now!!The professional are ready and willing handle your assignment.

ORDER NOW »»