Reference no: EM131873991
Assignment : Decision Support System Capstone Exercise
You are budgeting for the purchase of a new automobile, and therefore you are evaluating your situation to determine the monetary resources available for a monthly payment. In addition, you are analyzing some sales data for your local grocery store.
1) Create a new Excel spreadsheet using the DSS Capstone Input file in Moodle.
2) In cell B6 in the Auto Loan worksheet, enter the function to look up the correct tax rate.
3) In cell B17, calculate the amount of capital you have available to use for a car payment.
4) Calculate Miscellaneous Expenses as follows: If your combined Dining Out and Entertainment costs are greater than $400, then you have $150 for miscellaneous. Otherwise you only have $50.
5) In cell E11, enter the function to calculate your monthly payment given the information shown. Make sure the result is a positive value.
6) Create named ranges for Monthly Payment (E11), PercentCapital (E14), and InsPremium (E16).
7) Use Goal Seek to set the monthly payment of the auto loan to $304.69 (15% of available capital) by changing the purchase price.
8) Beginning in cell G4, complete the series of loan amount values ranging from $16,000 to $30,000 in $500 increments.
9) Complete the one-variable data table to determine different monthly payments, capital percentages, and insurance premiums given variable loan amount.
10) Copy the loan amount values from the one-variable table into the two-variable table starting in cell L3. Complete the series from 3.00% to 4.25% using .25% increments from cell M2 through R2.Set up and complete the two-variable data table. Format cell L3 to hide the result of the formula.
11) Format the data in the two-variable table so that any payment under $375 has a light green background, data that is between $375 and $500 will have a light yellow background, and anything about $500 will have a light red background.
12) Create a scenario named Current loan option, using amount of loan and interest rate as variable cells. Enter 25000 and 4.25% as the values for the scenario. Create a second scenario named Best loan option, using the same changing cells. Enter 16000 and 3.00% the values for the scenario. Create a third scenario named Worst loan option, using the same changing cells. Enter 30000 and 4.25% the values for the scenario. Uncheck Prevent changes to change cell values in all scenarios.
13) Generate a scenario summary report using the amount of loan, monthly payment, and insurance premium as the results.
14) Display the Auto Loan worksheet. Open Solver and set the objective cell for the lowest monthly payment possible. Use the cells containing purchase price and down payment as the variable cells.
15) Set constraints for the purchase price and down payment. The purchase price must be at least $16000 but no more than $18000. The down payment must be at least $500 but no more than $5000. Set a constraint for the interest rate, which must be at least .03.
16) Solve the problem, generate an Answer Report, but keep the original values in the Auto Loan worksheet.
17) Create a PivotTable based on the sales data in the Quarter3 worksheet. Place the PivotTable on a new worksheet. Rename the worksheet as PivotTable.
18) Add the Location field to the Row Labels area. Add the Month field to the Report Filter area. Add the Produce, Meat/Seafood, Dairy, and Total fields to the Values area. Modify the Sum of Total field to calculate the average instead of the sum.
19) Sort the row labels in descending order. Create a slicer for the Location field.
20) Create a clustered column PivotChart. Use the slicer to filter the PivotTable so that only the Brooklyn Drive data is displayed.
NOTE: You do NOT need to turn this exercise in. Feel free to e-mail it to yourself if you would like to do so.
Attachment:- DSS Capstone Input .rar