Spreadsheet Modeling and Decision Analysis
Sensitivity Report A sensitivity report of the analysis described above was created and can be found in Appendix E. The solution is not degenerate because there are no zero values in the allowable increase or allowable decrease columns of the constraints in the Sensitivity Report. All of the constraints affect the optimal solution. The solution is unique because there are no zero values in the allowable increase or allowable decrease columns in the decision variables section of the Sensitivity Report. This shows that there are no other combinations of production levels that will optimize this solution.

Decrease the Price of Whole If MNC were to decrease the price of the Whole product by $0.25, the optimal solution would change. This decrease in price would cause Whole’s contribution margin to fall to $0.79 and the optimal profit to fall to $439.76, a $100.00 difference. These calculations can be found in Appendix G. The production levels, however, would not change. This is because, as can be seen in the Sensitivity Report, there is an allowable increase of $0.36 and an allowable decrease of $0.31 in the price of Whole. Because $0.25 falls within this range, this change in price would not affect the production levels of the optimal solution.

...review of spreadsheet modelling The usage of spreadsheet applications, while incredulously recent-sounding, has been perhaps one of the most archetypal examples of computerization of business processes. Conceived by Dr. Richard Mattessich in 1961, an implausible half a century prior, electronic spreadsheets have outphased conventional paper forms of bookkeeping by promising accountants the privilege of partial accounting automation and in turn a revolutionary reduction in time consumption and human error. In spite of grandiose ideals of its further developers, the spreadsheet successfully commanded explosive attention only 20 years later. Pioneered by IBM’s marketing strategy to bundle a spreadsheet derivation (the obsolete Lotus 1-2-3) into its commercial computer, the IBM PC, it enabled the application to reach segments beyond the niche industrial end users (Power, 2002). The decade between 1980 and 1990 bore witness to various media exalting the novelty spreadsheet application as the bane of many common accounting and auditing problems. Spreadsheets soon became indispensable in most firms and its functions have since then extended to encompass financial analyses and modeling.
However, as usage increased and took hold of the market share ubiquitously, it inevitably became what reviewers (Hendry et al, 1994) described as the main component of the tangled mass...

...Introduction
An electronic, or computerized spreadsheet, can be defined as a software application which displays multiple cells that together make up a grid consisting of rows and columns, each cell containing alphanumeric text, numeric values or formulas which performs the calculation processes (Bessant, 2003; Stroman, Wilson, and Wauson 2004 and Power n. d.). It has been extensively employed ranging from the field of business and financial community to engineering, educational, scientific and medical areas (Ballantine, 1991; Bessant, 2003 and Randles, 2005). This essay firstly, will give a brief outline of development of electronic spreadsheet application and particularly indicate its widespread utilization in accounting sector; emphasis will be largely placed on the critical analysis of advantages and pitfalls of spreadsheetmodeling in financial accounting and reporting. Finally, remedies which can reduce the probability of dangers of spreadsheets will be introduced to improve the practicability of spreadsheets.
Ballantine (1991), Walkenbach (2007) and Power (n. d.) acknowledged that VisiValc, which was pioneered by Dan Bircklin and written for the Apple II computer in 1978, should be the first electronic spreadsheet. Admittedly, it essentially laid the foundation for future spreadsheets. Release in 1983, Lotus Development Corporation’s...

...university |
Problem Solving & SpreadsheetModeling |
CIS331 |
12/16/2012 |
This paper talks about the problem solving process along with examples, as well as one of more commonly used modeling and analytical techniques, i.e. spreadsheetmodeling. It also talks about its dependency, its uses, disadvantages and well as explores ways to better this very new, but powerful technique. |Modeling is the process of creating a simpliﬁed representation of real life problems and representing them in an organized fashion in order to understand a particular problem. These models can take many forms such as mental, visual, mathematical or electronic spreadsheets to name a few. Their uses can be seen in almost every field of our daily lives such as businesses, governments, human resources, science and engineering, and medicine etc. Their primary function is to gain insight into a specific problem and after getting a clear understanding of the topic, one can easily approach it from different directions with possible solutions. This paper discusses this process in detail, starting with gaining insight into a specific problem by using a problem solving model, and then moves on to how it applies to spreadsheetmodeling. It also discusses different aspects of electronic spreadsheet engineering, its uses and caveats, as well as its...

...DecisionAnalysis Page 1 of 4
DecisionAnalysis
Donna L. Christian, Strayer University Winter Quarter (MAT 540) Instructor: Mune Lokesh March 11, 2012
DecisionAnalysis Page 2 of 4
In business today, many decision-making situations occur under conditions of uncertainty. The demand for a product can be one number this week and double that number next week or vice versa. There are several decision-making techniques to aid the decision maker in dealing with these types of uncertainties. There are two classes of decision situations, situations where probabilities can be assigned to future occurrences and probabilities that cannot be assigned. A decision-making situation includes several components, the decision itself and the actual events that can occur in the future, we refer to those as states of nature. The states of nature can be good and bad economic conditions, cold or warm weather, and an accident or no accident. The state of nature that does occur will determine the outcome of the decision, but the decision maker has no control over which state occurs. Payoff tables are organized so that the decision situations can be analyzed. Using a payoff table is a means of organizing a decision situation, including the payoffs from...

...that activity times be shortened to provide an 80 percent chance of meeting the 40 weeks completion time. If the variance in the project completion time is the same as you found in part (1), how much should the expected project completion time be shortened to achieve the goal of an 80 percent chance of completion within 40 weeks?
Z = (T – TE) / ((v)^2)^0.5
= (40 – TE) / (5.64)^0.5, where 5.64 is the variance same as in part (1),
= (40 – TE) / 2.37
Z (x) = 0.8, from Normal Distribution Chart, we found that x = 0.84
Therefore,
0.84 = (40 – TE) / 2.37
1.994 = 40 - TE
TE = 38 weeks
3) Using the expected activity time as the normal times and the following crashing information, determine activity crashing decisions and revised activity schedule for the warehouse expansion project.
Activity Crashed Activity Time Cost ($)
(weeks) Normal Crashed
A 4 1,000 1,900
B 7 1,000 1,800
C 2 1,500 2,700
D 8 2,000 3,200
E 7 5,000 8,000
F 4 3,000 4,100
G 5 8,000 10,250
H 4 5,000 6,400
I 4 10,000 12,400
J 3 4,000 4,400
K 3 5,000 5,500
Normal Time Crash Time Maximum Crash Cost per day
Crash Time ($)
tA = 6 4 2 900
tB = 9 7 2 800
tC = 4 2 2 1,200
tD = 12 8 4 1,200
tE = 10 7 3 3,000
tF = 6 4 2 1,100
tG = 8 5 3 2,250
tH = 6 4 2 1,400
tI = 7 4 3 2,400
tJ = 4 3 1 1,400
tK = 4 3 1 1,500...

...Q.5 Solution
Bill Denomination (X)
Number of Bills (f)
X*f
X2*f
$1
520
520
520
$5
260
1,300
6500
$10
120
1,200
12000
$20
70
1,400
28000
$50
29
1,450
72500
$100
1
100
10000
Total
1000
5,970
129520
a.
b.
c. The probability that a bar containing $50 or $100 bill is purchased is 30 / 1000 = 0.03
Hence customer have to buy 100 bars of soap, so that he or she has purchased three bars containing a $50 or $100 bill.
d. Given n=1000, E(X) = 5.97, SD(x) = 9.68912
The probability that a soap contains a bill of $20 or above is 100 / 1000 = 0.0.1
Binomial with n =8, p = 0.1
Hence the probability that at least one of these bar contains a bill of $20 or larger is 0.5595
Q.6 Answer
Number of Children
Frequency
1996
2006
1
3671455
3971276
2
100750
137085
3
5298
6118
4
560
355
5
81
67
a.
X
1
2
3
4
5
P (x)
0.97125
0.02665
0.00140
0.00015
0.00002
b.
x
1
2
3
4
5
Total
P(x)
0.97125
0.02665
0.00140
0.00015
0.00002
1.00
x*P (x)
0.97125
0.0533
0.0042
0.00059
0.00011
1.03
x2*P (x)
0.97125
0.10661
0.01261
0.00237
0.00054
1.09
Expected value = E(x) = ∑ x*P (x) = 1.03
Variance = E(x2) – [E(x)]2 = 1.09-1.03*1.03 = 0.0291
c.
y
1
2
3
4
5
Total
P(y)
0.96463
0.03330
0.00149
0.00009
0.00002
1.00
d.
y
1
2
3
4
5
Total
P(y)
0.96463
0.03330
0.00149
0.00009
0.00002
1.00
y*P (y)
0.96463
0.06660
0.00446
0.00034
0.00008
1.04...

...DECISIONANALYSIS ASSIGNMENT 1
Case Problem 1: Planning Advertising Campaign
EXCEL sensitivity report for the Flamingo Grill case problem:
Managerial Report
Question 1:
Data given for Flamingo Grill case problem:
Total budget = $279000
Advertising media | Exposure rating per ad | New customers per ad | Cost per ad ($) | HJ consultant limit application to |
Television | 90 | 4000 | 10000 | 10 |
Radio | 25 | 2000 | 3000 | 15 |
Newspaper | 10 | 1000 | 1000 | 20 |
After consideration of limit for each advertising media:
Advertising media | Exposure rating per ad | New customers per ad | Cost per ad ($) |
Television | 55 | 1500 | 10000 |
Radio | 20 | 1200 | 3000 |
Newspaper | 5 | 800 | 1000 |
Model:
Decision variables:
Advertising media | Num of campaigns for max effectiveness | Num of campaigns for reduced effectiveness | TOTAL |
Television | 10 | 5 | 15 |
Radio | 15 | 18 | 33 |
Newspaper | 20 | 10 | 30 |
TOTAL | 45 | 33 | |
TOTAL EXPOSURE = 2160
TOTAL NUMBER OF POTENTIAL NEW CUSTOMERS = 127100
Constraints:
* Must reach at least 100000 new customers
127100 ≥ 100000
* Total campaign slots for maximum effectiveness smaller or same with limit
Television: 10 ≤ 10
Radio: 15 ≤ 15
Newspaper: 20 ≤ 20
* At least twice as many radio ads as television ads
33 ≥ 30
* Use not more than 20 television ads
Television: 15 ≤ 20
* Television budget should be at least $140000...

...DECISIONANALYSIS
Definition: A systematic procedure based on thinking patterns used to make choices in the hopes that they are good ones. It involves considering the Elements of a good choice and weighing them against your MUSTS/WANTS and any risks before making a final decision: The book is full of mumbo jumbo on this, but it isn't rocket science. We do some decisionanalysis almost every time we take a course of action.
1.Develop a Goal or Decision Statement. ACTION Oriented statement. Will imply a decision is necessary. This is simply realizing that a decision/choice must be made and stating the need for a decision. - Provide focus -Identify Level (who? Commander, Resource Advisor, etc.). Be sure that this is concrete. "Fix the air conditioner" is not a good decision statement, it must be more specific.
2. Determine the Criteria OBJECTIVES (Musts and wants): Considering and defining the requirements/factors necessary to make a successful choice. These are the "requirements" and the "options". The "needs" and "wants". The "must have" and the "nice to have". MUSTS are required. WANTS are added extras. MUSTS are mandatory, measurable and specific. WANTS are subjective, not mandatory, desirable traits. Brainstorming. Make sure objectives are not LOADED; this will eliminate possible alternatives by narrowing the focus too...