DATABASE SYSTEM

Closed

Description

CIS007-2

Decision Support Systems and Data Mining

Assignment 1

1. Consider the following simple present value model:

Present value is a financial model which helps to calculated the time value of money based on the payment amount in the future subject to a particular interest rate and duration of the time. Following is the simple present value model, which calculates P, present value, as the rate of F, the future value (payment amount), over (i+1), interest rate plus one powered by n, the duration of the time.

P = F/(1 + i) n

Suppose F = £10000, i = 8% and n = 10 years, then determine the present value P using Excel, to solve the problem as described below:

1. a. without using any of the built-in financial functions

1. b. with using Excels built-in financial functions

You should produce the formulas in the same worksheet; entitle it Q1.

[10 MARKS IN TOTAL, 5 Marks for each part]

2. Using the Formula produced in 1 (use cell referencing to produce these):

2. a. Construct a table of values for P, on a new worksheet with F varying in steps of £300 from £0 to £15000 and i varying in unit steps from 3% to 15%. (n fixed here at 10 years); entitle the worksheet Q2a.

2. b. Copy the worksheet to 9 other worksheets and hence create a workbook of present values with F varying in steps of £300 from £0 to £15000, i varying in unit steps from 3% to 15% and n varying in yearly steps from 1 to 10 years.

You should now have a workbook consisting of 10 sheets; entitle them Q2b1, Q2b2, Q2b3, … Q2b9.

[20 MARKS IN TOTAL, 2 Marks for each correct worksheet]

3. Consider the following problem introduced:

A factory makes 3 components, A, B and C using the same production process for each. A unit of A take 1 hr, a unit of B takes [url removed, login to view] hrs and a unit of C takes 0.5 hrs. In addition, C has to be hand finished, an activity taking [url removed, login to view] hrs per unit. Each week total production time (excluding hand finishing) must not exceed 300 hrs and hand finishing must not exceed 45 hrs.

The components are finally assembled to make two finished products. One product consists of 1 unit of A and 1 unit of C selling for 30 pounds whilst the other consists of 2 units of B and 1 unit of C and sells for 45 pounds. At most 130 of the first product and 100 of the second product can be sold each week.

3. a. Formulate the problem of planning weekly production to maximise total proceeds as a linear programming problem in 2 variables and obtain the solution graphically.

3. b. Revise the price for the first product from £30 to £35 and resolve the problem graphically to find out and show the new optimum solution. Compare the old and new solutions with respect to the impact of the price.

3. c. Revise the limit for the number of the second product from 100 to 120 and resolve it graphically to find out and show the new optimum solution. Compare the old and new solutions with respect to the impact of the capacity limit.

3. d. Solve the problem with MS Excel Solver in-built functionality for all three cases to verify your solutions found graphically.

Skills: Data Entry, Excel

See more: Assignment, product excel, three d model, steps to solve assignment problem, steps in data mining process, solve the assignment problem, solve assignment problem, solution to assignment problem, solution of assignment problem, programming in excel 2013, programming factory, problem solution assignment, planning problem, optimum it solutions, ms excel programming, money unit, make money with excel, linear programming worksheet, linear programming solver excel, linear programming problem, linear programming model, linear programming in excel, linear assignment problem, impact products, find factory

Project ID: #4516412

23 freelancers are bidding on average $397 for this job

fhasanbd

READY TO START RIGHT NOW

$473 USD in 10 days
(20 Reviews)
3.9
Alcatraz87

Hi, I'm proficient in MS Excel and I'm able to help you with this project. Please check out my profile and previous reviews and if you consider me a suitable candidate, let's chat. Thanks.

$500 USD in 6 days
(9 Reviews)
3.8
ksrslm

Will solve the problem in Handwritten as well as in Excel file

$300 USD in 3 days
(1 Review)
1.6
osnofasl

Good evening. I am computer engineer, so I am ready and able to do the job. Thanks in advance. Best regards,

$300 USD in 5 days
(1 Review)
1.5
vaibhav585

Greetings abbey22 , Team of Excel experts here , this project is pretty simple and i would be able to complete it in Written and VBA coded in another 12 hours ,if you contact back i will be able to provide you a draf More

$300 USD in 3 days
(1 Review)
1.0
mpunn2007

lets do it.

$300 USD in 15 days
(1 Review)
0.0
arnabg01

i can do this...trust me....u will be in profit....

$301 USD in 3 days
(0 Reviews)
0.0
sanowaraiub

I have done my graduation in finance, and now working in Multinational company in supply chain where we have to deal with many excel related report so m good at it.

$388 USD in 5 days
(0 Reviews)
0.0
bantoo1

sir,i can do this job

$333 USD in 2 days
(0 Reviews)
0.0
gautampunj

best in this format and can solve in type.

$300 USD in 3 days
(0 Reviews)
0.0
AureateKT

Hello. I'm ready to work

$300 USD in 2 days
(0 Reviews)
0.0
asimarain21

i am fast in data entry and ms office work.

$444 USD in 3 days
(0 Reviews)
0.0
marypeta

Hello. I'm ready to work

$444 USD in 3 days
(0 Reviews)
0.0
Rajmohan007

Hi Expert in Operations Management and Excel.. I am the right combo for this assignment. PM, so i can start working for you Thanks & Regards, Raj

$444 USD in 3 days
(0 Reviews)
0.0
ReehIns

I can do it perfectly sir.. Please give this project to me and ill be done it in a very very nice manner as you really need Hope to do your project very well even if you give now also. Thank you sir.

$388 USD in 5 days
(0 Reviews)
0.0
starchip

ten gracia de dios

$444 USD in 3 days
(0 Reviews)
0.0
wyliechan

Quantitative Finance. Excel, Optimization, Linear Programming.

$333 USD in 3 days
(0 Reviews)
0.0
alphaedge999

I am interested in working on this project.

$450 USD in 3 days
(0 Reviews)
0.0
pvijayan1

Hi Abbey. I can easily complete this assignment for you.

$333 USD in 3 days
(0 Reviews)
0.0
tjd1

Have an MS in Operations Research and I am a MS Office Specialist (Expert Level). These are pretty straightforward activities.

$500 USD in 4 days
(0 Reviews)
0.0