spreadsheet and influence chart question (Loan Calculator)

use the down payment is $5206, need to write all the inputs in the sheet. use the formulas in the excel. let excel to calculator. use the DS601 style for cell styles. I give 2 examples for that. and also need to answer those questions. thx

Sheet1

									Cell Styles used in DS 601	R. Saltzman
Output	Black font on gray background, with border (= “Output” built-in style.)
Input	Black font on blue background.
Calculated	No formatting (= “Normal” built-in style.)
Decision	Red font on light red background (= “Bad” built-in style.)
Random	Green font on light green background (= “Good” built-in style.)
To create your own cell style:
1) Select a cell and format it as you wish it look
2) Cell Styles > New Cell Style …
Name style, and check boxes for the attributes you want to alter.

2

>Sheet

1

_Hourly

%

Salaried Hourly Salaried Hourly Salaried Hourly

0

70 $8,850 $5,310

1

2

3

4

5

Annual Premium

GR Salaried Hourly Combined

$657 $1,395 $2,052

$657 $1,395 $2,052

5%

$2,052

25%

5%

10%

GR 15%

$2,052

20%

25%

																																																											R. Saltzman	Health Care Premiums Model	2/1/18
Inputs					Salaried					Hourly	Annual_Employee_		GR	=Sheet1!$C$	5
Current Number Employed		3	0		70	Annual_Employee_GR_Salaried	=Sheet1!$B$5
Annual Employee GR	1 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper		5%			25%	Annual_Premium_GR	=Sheet1!$B$8
Average	Annual Premium		$8,850		$5,310	Average_Annual_Premium_Hourly	=Sheet1!$C$6
Average_Annual_Premium_Salaried	=Sheet1!$B$6
Annual Premium GR		4
Projected Premiums	No. of Employees	Ave. Annual Premium	Total Costs ($1,000)
Years from Now		Combined
30	$266	$372	$637
35	88	$9,204	$5,522	$322	$486	$808
40	110	$9,572	$5,743	$383	$632	$1,015
46	138	$9,955	$5,973	$458	$824	$1,282
53	173	$10,353	$6,212	$549	$1,075	$1,623
61	216	$10,767	$6,460			$657			$1,395					$2,052
1-way Data Table
Total Costs in $1000s in Year 5
Ratio to
Year 0 Total
2%	$596	$1,266	$1,862	2.923
3%	$626	$1,330	$1,955	3.069
4%	3.221
$689	$1,464	$2,153	3.379
6%	$722	$1,535	$2,257	3.543
7%	$757	$1,609	$2,366	3.713
8%	$793	$1,685	$2,478	3.890
9%	$831	$1,765	$2,595	4.073
	10%	$869	$1,847	$2,717	4.263
2-way Data Table	Hourly Employee GR
	15%		20%	30%	35%
$1,342	$1,555	$1,826	$2,104	$2,472
Salaried Employee	$1,428	$1,641	$1,912	$2,190	$2,558
$1,568	$1,781	$2,330	$2,698
$1,708	$1,921	$2,192	$2,470	$2,838
$1,923	$2,136	$2,408	$2,685	$3,054

Projected HC Premiums

Salaried 0 1 2 3 4 5 265.5 322.14 382.88640000000004 457.93213440000005 548.72215756800006 656.81006936064 Hourly 0 1 2 3 4 5 371.7 485.97120000000007 631.76256000000012 824.27784192000024 1074.6671689728003 1395.4522129367044 Combined 0 1 2 3 4 5 637.20000000000005 808.11120000000005 1014.6489600000002 1282.2099763200004 1623.3893265408003 2052.2622822973444

Years From Now

Costs in $1,000s

R. Saltzman Assignment 1: Loan Calculator Name .

DS 601 Applied Management Science Due: Next week

An important and common business transaction is getting a loan from a bank or financial services company. Suppose you want to buy a car with a purchase price of $21,000, but you only have $5,000 available now to make as a down payment. Then you’d need to finance (i.e., get a loan for) the balance ($16,000) at a particular annual interest rate, such as 6%.

Typically, we pay back a loan by making a series of equal-sized payments due at the end of each month. Excel’s PMT function can be used to find the amount of money you must pay every month in order to amortize the loan in some number of periods. Its syntax is: =PMT(Rate, Nper, PV, FV, Type), where

· Rate = the interest rate per period;

· Nper = the total number of payments, or the length of the loan (typically, in months);

· PV = the present value, or loan amount;

· FV = the future value, or cash balance, you want to attain after the last payment is made. If omitted, FV is assumed to be 0, i.e., the future value of a loan is 0.

· Type    = 0, if payments are due at the end of each period;

= 1, if payments are due at the beginning of each period.

For example, the cell formula PMT(.06/12, 36, 16000, 0, 0) yields a payment size of $486.75, which must be paid by the end of every month in order to repay the loan in 3 years. The total interest paid (TIP) over the course of a loan is the total amount of all payments minus the amount financed. Here, TIP = 36($486.75) – $16,000 = $1,523. A key decision to make prior to getting a loan is the length of the loan. This assignment explores the relationship between the loan length, payment size, and TIP.

To do:

1. Draw an influence chart for this situation, with TIP as the main output.

2. Create a spreadsheet model in Excel following the format used in class, i.e., have sections for inputs, decision variables, calculated quantities, outputs, and range names.

a. Assume a purchase price of $21,000 and an annual interest rate of 6%.

b. The down payment should equal to the last four digits of your student ID number. For example, if your ID # is 987654321, then the down payment would be $4,321.

3. On the same sheet, make a 1-way data table where the loan length varies from 12 to 60 months in increments of 12 months. Output columns should be monthly payment size and TIP.

4. On the same sheet, make a 2-way data table for TIP where the loan length varies from 12 to 60 months in increments of 12 months and the annual interest rate varies from 4% to 8% in increments of 0.5%. Highlight all cells in the data table with a TIP below $1,500.

Turn in hard copies of:

1. Your influence chart (should fit on 1 page)

2. Your spreadsheet (should fit on 1-2 pages)

3. Brief answers to the following questions (which can be answered on your spreadsheet):

a. What kind of relationship is there between loan length and TIP?

b. How does the loan length affect PMT? Make an XY plot with loan length on the x-axis.

c. How does the interest rate affect TIP?

d. If you can only afford to make monthly payments of $425 or less, and the annual interest rate is 6%, what options do you have regarding the loan length?

Sheet1

Simple

Profit

Model DS 6

0

1 2/1/18 Demand =Sheet1!$B$5 Fixed_Cost =Sheet1!$B$6 Inputs Number_Leftover =Sheet1!$B$15 Unit Price $30 Number_Sold =Sheet1!$B

$14

Demand

1,000 Production =Sheet1!$B$11 Fixed Cost $10

,000 Total_Cost =Sheet1!$B$16 Variable Cost

$14

Total_Revenue =Sheet1!$B$17 Salvage Value

$10

Unit_Price =Sheet1!$B$4 Variable_Cost =Sheet1!$B$7 Decision Variable Production

1,

200 1-way Data Table Production Profit

Total Cost Calculated Quantities $5,200 $26,

800 Number Sold 1000

1000

700 $1,200 $19,800 Number Leftover

200 200 800

$2,800 $21,200 Total Cost

$26,800 900 $4,

400 $22,600 Total Revenue $32,000

1000

$6,000 $24,000 1100 $5,600 $25,400 Output 1200

$5,200 $26,800

Total Profit

$5,200

1300 $4,800 $28,200 1400

$4,400

$29,600 1500 $4,000 $31,000 1600 $3,600 $32,400 1700 $3,200 $33,800 2-way Data Table Demand
$5,200 700 800 900 1000 1100 1200 1300 1400 1500
700 1200 1200 1200 1200 1200 1200 1200 1200 1200
800 800

2800

2800 2800 2800 2800 2800 2800 2800
900 400

2400 4400

4400 4400 4400 4400 4400 4400
Production 1000 0

2000 4000 6000

6000 6000 6000 6000 6000
1100

-400

1600

3600 5600 7600

7600 7600 7600 7600
1200

-800

1200

3200 5200 7200 9200

9200 9200 9200
1300

-1200

800 2800

4800 6800 8800 10800

10800 10800
1400

-1600

400 2400 4400

6400 8400 10400 12400

12400
1500

-2000

0 2000 4000 6000

8000 10000 12000 14000

1-way data table graphed

700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1200 2800 4400 6000 5600 5200 4800 4400 4000 3600 3200

Production

Total Profit