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.

...
Assignment brief
Qualification
BTEC National Diploma for IT Practitioners
Unit number and title
Unit 42: Spreadsheet Modelling
Start date
w/c 3 May 2011
Deadline
w/c 6 June 2011 – (late assignments capped at a pass)
Assessor name
Sheila Cassidy-Allan
Assignment title
Assignment 2 - Enhance, test and evaluate a complex spreadsheet model
The purpose of this assignment is to:
Enhance, test and evaluate the functions of a complex spreadsheet model to meet the advanced needs of a specified user/organisation.
Scenario
Ridgeway is a large college offering a full range of academic courses, together with vocational courses such as plumbing, carpentry and so on. It also offers evening and weekend courses as part of its adult education programmes.
The administration team at Ridgeway want to use spreadsheet software to keep track of student enrolments and course funding for the Year 2011.
Requirements
As per assignment 1 with additional features.
Task 1
The Staff at Ridgeway would benefit from a menu/switchboard function that will enable them to navigate to each sheet from one main menu. Using the following automation methods: (P6)
Create another worksheet called Welcome Sheet. This should be user friendly, with four buttons named Students, Courses, Enrolment and Funding. Record the event of opening each sheet individually using a macro and assign the macro to its appropriate button on your Welcome Sheet.
Add...

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

...Krannert Graduate School of Management
Purdue University
MGMT 57000
SPREADSHEETMODELING AND SIMULATION
Spring 2014
Instructor: Yanjun Li, office: KRAN 422, phone: 494-4525, e-mail: li14@purdue.edu
Office Hours: Tuesday 1:00 – 4:00 pm, or by appointment.
Course Packet: The course packet contains cases and readings from various sources.
Textbook: David Hartvigsen, SimQuick: Process Simulation with Excel, 2nd Edition,
Prentice Hall, 2004. (ISBN: 0-13-107880-1)
References: Law, A. M. and W. D. Kelton, Simulation Modeling and Analysis, 3rd Edition, McGraw Hill, 2000.
Luenberger, D. G., Investment Science, Oxford University Press, 1998.
Winston, W. L., Simulation Modeling Using @RISK, Duxbury, 2001.
Computer Software: Palisade Decision Tools, including @RISK, RISKOptimizer, and StatTools, are available in the computer labs in the Krannert building and Rawls Hall. A student version of the software and the installation guide can be downloaded for free at
https://intra.krannert.purdue.edu/admin/kcc/Pages/Resources.aspx.
SimQuick is included in the companion CD of our textbook.
Course Prerequisites: MGMT 67000
COURSE DESCRIPTION
In the past twenty years, Excel spreadsheets have become the standard tool that business people use to model and analyze quantitative problems. The latest versions of these...

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