I need help with what formulas to use for this assignment to make the whole system work with the markup cost. detailed instructions on how to set up the excel sheet and the proper formulas would be great here are the instructions.

Instructions

1. Make three new tabs in the worksheet (in addition to the original) of the model
2. Make three copies of the completed model (your original) and paste it three times, one on each Worksheet Tab
3. Change inputs or constant values in each of the copied models with those values you are suggesting to show the effect in the bottom line of the Profit and Loss table and the chart.
4. In an MS Word file, write a 1-page document stating the three possible improvement actions the Bike Shop can take to become profitable. Include specific input values and output values that resulted from plugging in various numbers. For example, you could say "If you increase the price to $5000, then the expected profit will be $100,000"
5. Evaluate each of the three improvement actions, i.e., is this action feasible? Would it actually make them profitable? Would customers respond positively? Would sales decrease?
6. Select the one action that after evaluation you believe would be the best action to take for the Bike Shop to become profitable and identify it as your recommendation with a justification as to why the recommended action is best.

IS130 Building a Decision Support System with MS Excel: A Spreadsheet Modeling Assignment Objective: This assignment assesses students' ability to use critical thinking and MS Excel to solve business problems. Specifically, students demonstrate effective use of formulas, absolute cell referencing, dynamic charts, and formatting. It is assumed that students have practical experience with MS Excell (i.e., IS52 Lab) and an understanding of basic accounting concepts, including cost vs. price, and markup (i.e., ACCT 4a and b). Anonymous assessment scores are used as part of CSB's Student Learning Outcome Plan: - BA Graduates will demonstrate the ability to make data informed judgments utilizing spreadsheets and other analytical tools and technology. Concept Refresher: Cost: the price a business pays for acquiring, producing, or maintaining something. It includes material, labor, and overhead (i.e., fixed costs like rent) - Cost is not the (selling) Price your customers pay - Businesses look for ways to lower costs but never to raise costs Markup: the \% increase over cost a company chooses to impose on the products it sells. Cost + Markup = Price ; Cost +x% of Cost = Price . - Markup is the difference between a product's selling price and cost as a percentage of the cost. If a product costs $100 to make and you choose a 25% markup the selling price is $125, the price increase is $100+($10025%)=$125 Problem Statement: Your small retail 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 ONLY ONE employee besides yourself to help assemble and sell these bicycles. You need to develop a simple spreadsheet model to help you monitor this business's operations and help you track your projected profit (or loss) for the coming year. You would like to know which bikes are the most profitable, and you need to analyze 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 model using an MS Excel spreadsheet that will become a part of your Decision Support System (DSS). This model should provide an instant answer 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 product line offered and the set of critical variables such as markup, rent, and utilities. The model will help you decide the best course of action for next year. Note: The selling price is based on a percentage markup over the cost and is derived by a formula. Projected Data: Your task is to enter that information into a DSS model on MS Excel. The tables below include the input data you will use to calculate the output data shown as question marks "?????"). I otal Projectea sales: $?!? Your Tasks and the Expected Outcome: Spreadsheet: Develop a model that will determine the results based on the above data. This must be done by using items shown as $ ???? must be calculated using a formula or obtained by inserting a cell reference that will link to another cell. (Note: do NOT use the Table feature in Excel A, File Preview The model must aynamically recalculate the results when the input values (e. g., projected sales, markup, cost per unit) are changed. This is required in order to illustrate the principle of "What-If" analysis in Decision Support Systems (see the course module on Business Analytics- Tab - Decision Support Systems). Note: It is not necessary to use the What-If utility of Excel. Sales Income Chart: Develop a chart to graphically illustrate the total Sales Income for each bicycle model. A change in the input parameters must impact the chart. That is, if you change the input value of for each bike, the change must be automatically reflected on the chart as well. Scenario Analysis (do not use the excel provided what if tool): Use the model you created (the excel spreadsheet with formulas) to help the Bike Shop decide on a strategy that would make them profitable. In coming up with possible actions to take, use the DecisionMaking Model illustrated at the end of this document to develop three options that the Bike Shop could take to increase profits and use the Excel model to test the possibilities. When done with your analysis, write a description in a separate MS Word document of the following: 1 ) Bike's Shop problem, 2) the three possible courses of action to increase profit, and 3) the one option that you believe is the most effective in making the Bike Shop profitable. Be specific on the changes that would result in the Excel model if each option was implemented (i.e., test each one using the model). Remember, in selecting a course of action, think critically and ensure the course of action is feasible, makes business sense, and will bring about the desired results. Note: Increasing price may not always be the best option since you may lose customers to the competition! In 2) above, the three options must be done using Excel calculations. Show those three options in three separate MS Excel worksheets within the same Excel file. Checklist: 1) Did you enter all input data in each of the tables? 2) Did you use the absolute cell reference (e.g. using \$ sign)? 3) Did you offer three scenario solutions in Excel? 4) Did you provide a rationale on why your choice of solution is best suited? In order to receive full credit you had to: - use the correct absolute cell reference formula (eg. $A$20 there must be two $ signs, no exception) - create a total of four Excel sheets (original, option 1, option 2, option 3) and in each sheet, there must be all five tables WITH the correct absolute cell reference formula. - write up a discussion on three alternative options using paragraphs and the final paragraph stating WHY one option is better than the two other options. The discussion must be elaborated in explaining why so, not just stating the facts. - Do NOT combine the tables in to one large table IS130 Building a Decision Support System with MS Excel: A Spreadsheet Modeling Assignment Objective: This assignment assesses students' ability to use critical thinking and MS Excel to solve business problems. Specifically, students demonstrate effective use of formulas, absolute cell referencing, dynamic charts, and formatting. It is assumed that students have practical experience with MS Excell (i.e., IS52 Lab) and an understanding of basic accounting concepts, including cost vs. price, and markup (i.e., ACCT 4a and b). Anonymous assessment scores are used as part of CSB's Student Learning Outcome Plan: - BA Graduates will demonstrate the ability to make data informed judgments utilizing spreadsheets and other analytical tools and technology. Concept Refresher: Cost: the price a business pays for acquiring, producing, or maintaining something. It includes material, labor, and overhead (i.e., fixed costs like rent) - Cost is not the (selling) Price your customers pay - Businesses look for ways to lower costs but never to raise costs Markup: the \% increase over cost a company chooses to impose on the products it sells. Cost + Markup = Price ; Cost +x% of Cost = Price . - Markup is the difference between a product's selling price and cost as a percentage of the cost. If a product costs $100 to make and you choose a 25% markup the selling price is $125, the price increase is $100+($10025%)=$125 Problem Statement: Your small retail 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 ONLY ONE employee besides yourself to help assemble and sell these bicycles. You need to develop a simple spreadsheet model to help you monitor this business's operations and help you track your projected profit (or loss) for the coming year. You would like to know which bikes are the most profitable, and you need to analyze 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 model using an MS Excel spreadsheet that will become a part of your Decision Support System (DSS). This model should provide an instant answer 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 product line offered and the set of critical variables such as markup, rent, and utilities. The model will help you decide the best course of action for next year. Note: The selling price is based on a percentage markup over the cost and is derived by a formula. Projected Data: Your task is to enter that information into a DSS model on MS Excel. The tables below include the input data you will use to calculate the output data shown as question marks "?????"). I otal Projectea sales: $?!? Your Tasks and the Expected Outcome: Spreadsheet: Develop a model that will determine the results based on the above data. This must be done by using items shown as $ ???? must be calculated using a formula or obtained by inserting a cell reference that will link to another cell. (Note: do NOT use the Table feature in Excel A, File Preview The model must aynamically recalculate the results when the input values (e. g., projected sales, markup, cost per unit) are changed. This is required in order to illustrate the principle of "What-If" analysis in Decision Support Systems (see the course module on Business Analytics- Tab - Decision Support Systems). Note: It is not necessary to use the What-If utility of Excel. Sales Income Chart: Develop a chart to graphically illustrate the total Sales Income for each bicycle model. A change in the input parameters must impact the chart. That is, if you change the input value of for each bike, the change must be automatically reflected on the chart as well. Scenario Analysis (do not use the excel provided what if tool): Use the model you created (the excel spreadsheet with formulas) to help the Bike Shop decide on a strategy that would make them profitable. In coming up with possible actions to take, use the DecisionMaking Model illustrated at the end of this document to develop three options that the Bike Shop could take to increase profits and use the Excel model to test the possibilities. When done with your analysis, write a description in a separate MS Word document of the following: 1 ) Bike's Shop problem, 2) the three possible courses of action to increase profit, and 3) the one option that you believe is the most effective in making the Bike Shop profitable. Be specific on the changes that would result in the Excel model if each option was implemented (i.e., test each one using the model). Remember, in selecting a course of action, think critically and ensure the course of action is feasible, makes business sense, and will bring about the desired results. Note: Increasing price may not always be the best option since you may lose customers to the competition! In 2) above, the three options must be done using Excel calculations. Show those three options in three separate MS Excel worksheets within the same Excel file. Checklist: 1) Did you enter all input data in each of the tables? 2) Did you use the absolute cell reference (e.g. using \$ sign)? 3) Did you offer three scenario solutions in Excel? 4) Did you provide a rationale on why your choice of solution is best suited? In order to receive full credit you had to: - use the correct absolute cell reference formula (eg. $A$20 there must be two $ signs, no exception) - create a total of four Excel sheets (original, option 1, option 2, option 3) and in each sheet, there must be all five tables WITH the correct absolute cell reference formula. - write up a discussion on three alternative options using paragraphs and the final paragraph stating WHY one option is better than the two other options. The discussion must be elaborated in explaining why so, not just stating the facts. - Do NOT combine the tables in to one large table