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

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.

Manufacturer Model Projected Sales GBI GBI GBM Whitman Petersoin Talbot DXT PXT Tiger Tailwind PBY Lago 2 28 48 30 60 18 Manufacturer Model Cost Each Total cost GBl GBl GBM DXT$ PXT$ Tiger 1,124.00 1,085.35 987.60 1,350.00 886.50 1,495.00 Of Projected Sales: Whitman Tailwind $ Petersorn Talbot PBY$ Lago 2 $ Total Cost Markup: 25%