Lab Report: Decision Modelling using Excel
Spreadsheets as Decision Support System (DSS)
At a very simple level, a DSS might just be a spreadsheet that allows the user to vary some of the parameters and see the impact this has on outcomes.
For example:
-we may build a spreadsheet to support various investment decisions;
-at a personal level, we could create a spreadsheet to model a home loan and see the impact on repayments of changing parameters like interest rates, principal size and the term of the loan;
-we can look at different mobile phone plan options and see how much it may cost;
-businesses often use spreadsheets in modelling outcomes as part of business case development for a range of proposals such as:
- the University enters into agreements with offshore organisations to make its degree programs available in these locations - spreadsheets are used to look at the set of costs (fixed and variable) and to compare this to projected income in a range of circumstances (mainly student numbers per cohort, but also considering the impact of issues such as exchange rates, labour costs, wastage rates etc...)
For this lab report, you will create a spreadsheet to model a solution to solve a problem for choosing a mobile phone plan for your granny. This is a small task, but will demonstrate to you the strengths of a spreadsheet being used to assist in decision support.
Excel exercise
You are thinking of buying a new mobile phone plan for your dear old granny. You have collected call records for the last two years (see the MS Excel spreadsheet 'Lab report 1 Granny Phone Data') and are going to use this data as the basis for your analysis and decision.
You have narrowed the search to the following four options as outlined below:
|
Option 1
- Cost: $25 per month (expires at the end of the month)
- Call cap: $225 calls/month
- Data cap: 200 MB connection is cut off once monthly limit is reached
- 0.89c per minute or part of minute
- 0.25c connect fee (per call
|
Option 2
- Cost:$59 per month (expires at the end of the month)
- Call cap: $450 calls/month
- Data cap: 50 MB + 0.35 c/MB
- 0.65c per minute or part of minute
- 0.30c connect fee (per call
|
Option 3
- Cost: $75 per month (expires at the end of the month)
- Call cap: unlimited - Data cap: 250 MB + 0.35 c/MB
- 0.50c per minute or part of minute
- 0.35c connect fee (per call)
|
Option 4
- Cost: $99 per month (expires at the end of the month)
- Call cap: unlimited
- Data cap: 200 MB + 0.45 c/MB
- 0.50c per minute or part of minute
- 0.35c connect fee (per call)
|
Additional Information and Assumptions:
1) At the end of each month each of the plans expire and you are required to purchase another month worth of access.
2) If you reach the call cap limit you must pay the difference between the call cap and the actual dollar amount used. E.g. In the first January month (option 1) granny has used 560 minutes of talk time (at 0.89c per minute) and has made 174 calls (at 0.25c connection fee for each call). Thus, under option 1 (January) the total cost of calls, including connect fees, would be $541.90. The cap limit is $225 and is not reached. This means that granny has had to pay an extra $316.90 for that month, plus the $25.00 plan fee, for a total of $341.90 for the first January option 1.
Please note this is a simple exercise to assist in understanding how MS Excel can be used as a decision support tool. In reality mobile phone plans can be more complicated with other possible variables.
Lab report
Your granny uses the phone to chat to her kids, grandkids and friends. She is on Facebook and regularly checks her profile and posts updates when she is travelling. She is on a pension and needs to ensure she gets the best deal for her mobile phone plan.
Using MS Excel, develop a model to help your granny make a decision on which is the lowest priced option for her situation based on the 24 months of data provided. A spreadsheet downloaded from the unit Moodle site has been provided with two years of data that has been collect. You need to use that dataset in doing your analysis and showing the analysis in the spreadsheet. You must use formulas, link between and within worksheets, and use at least one IF statement in each option.
Please state clearly any assumptions you make and identify any other information you might need to help her make this decision. Your first worksheet must be your coversheet filled-in and the second worksheet must be the original dataset. You must explain which choice is best for granny and why: i.e. build an argument. List this information in your MS Excel spreadsheet. Remember granny is going to read the spreadsheet analysis and this perspective needs to be considered in the spreadsheet you do. It should be noted that there are numerous ways of presenting and formatting the analysis.
Attachment:- Lab Data.rar