2023-XA1
xlsx
keyboard_arrow_up
School
California State University, Northridge *
*We aren’t endorsed by this school
Course
312
Subject
Information Systems
Date
Apr 3, 2024
Type
xlsx
Pages
21
Uploaded by LieutenantSnow6770
IS 312 Excel assignment 1 (XA1): 12 points; each question is worth 3 points
Q2: Mixed reference - Discounted prices for DezinedT
Q3: Mixed reference - Gradebook for IS 312 SP 2019
Q4: PMT function - Ron's auto loan payment schedule
Please follow the "Submission format" given on each spreadsheet.
Q1: References - "Thought exercise" with formulas being copied. NO COMPUTER USE
: pure "
brain exercise
"
"
The arrows and "*" indicate the DESTINATION of the copy: TYPE the new formula in the cell indicated with "*".
Need to provide concise explanation, in the format that are given in Week 2 class.
Students are assigned different questions according to his/her last name:
LName in A-D:
Copied to: cell with "*" (type formula there)
LName in M-Q:
Copied to: cell with "*" (type formula there)
=B1+C4*E$3/$D4
=E1+F4*H$3/$D4
=C4+B3*D$2/$E5
*
(Given above)
(Given above)
=B5+C8*E$3/$D8
=E5+F8*H$3/$D8
*
*
LName in E-L:
Copied to: cell with "*" (type formula there)
LName in R-Z:
Copied to: cell with "*" (type formula there)
=C2+D3*B$4/$C3
*
=B5+C6*E$4/$D3
*
(Given above)
(Given above)
*
*
*
*
Please do NOT use spreadsheet but perform a "thought analysis" to determine the new formulas at the destination cells (
marked "*"
).
* PLEASE do NOT move the cell of the given formula: the problem is supposed to be solved based on the given formulas in given cells.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Write concise reasoning in the following:
B1 is E1 and C4 is F4 because same row, 3 columns right.
E$3 is H$3 because same row, 3 columns right.
$D4 is $D4 because same row and $ means D stays same.
B1 is B5 and C4 is C8 because same column, 4 rows down. E$3 is E$3 because same column and $ means 3 stays same.
$D4 is $D8 because same column, 4 rows down.
B1 is E5 and C4 is F8 because 3 columns right, 4 rows down.
E$3 is H$3 because 3 columns right and $ means 3 stays same.
$D4 is $D8 because 4 rows down and $ means D stays same.
That is: write ONE formula in D10/K12/O4/R15 (for respective last name), that can be copied to the whole area within the double-line borders, that can obtain the numbers as shown.
LName in A-D:
Current pricing
Service
Plain
Standard
Designed
Submission format:
1. Display formulas (<Ctrl>-<~>)
2. Shrink the columns that do not contain yhour work (example: for LName N-Z, can shrink columns A-N)
DezinedT
is a small shop that designs and prints T shirts for customers. The folloinwg on the left are the original prices, and the right are the discount
ed
prices with 10%, 15%, and 20% discounts, respectively. Write the formulas to calculate the discount
ed
prices.
* NOTE: ONE
formula
ONLY
, then copy the ONE formnula to all needed cells
.
3. Print the spreadsheet with formulas (I do not need spreadsheet with values - ONLY formulas
).
Discounted options
Price
10.00%
15.00%
20.00%
LName in E-L:
9
8.1
7.65
7.2
Current pricing
11
9.9
9.35
8.8
Service
Price
15
13.5
12.75
12
Plain
9
Rows 8-10:
Standard
11
Format to show "$"
Designed
15
Rows 12-14:
Format to show "$"
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
LName in M-Q:
Current pricing
Discounted options
Service
Price
10%
15% 20%
Plain
9
8.1
7.65
7.2
Standard
11
9.9
9.35
8.8
Designed
15
13.5
12.75
12
Rows 4-6:
Format to show "$"
Discounted options
10%
15%
20%
8.1
7.65
7.2
LName R-Z:
9.9
9.35
8.8
Current pricing
13.5
12.75
12
Service
Price
Plain
9
Standard
11
Designed
15
Rows 15-17:
Format to show "$"
Discounted options
10%
15%
20%
8.1
7.65
7.2
9.9
9.35
8.8
13.5
12.75
12
Student #
001
002
003
004
005
006
007
008
Possible
Submission format:
1. Display formulas (<Ctrl>-<~>)
Type the data given in the graph beginning from A2: keep all data in their designated ranges
. Formulas in columns C, E, G, I, J, K. C/E/G/I/K has only ONE formula: copi
2. Print the spreadsheet with values - FORMATTED (such as %)
2. Print the spreadsheet with formulas
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Proj 1
Pj1%
Proj 2
Pj2%
Exam 1
E1%
Exam 2
E2%
Total Total%
20
66.67%
33
82.50%
65
65.00%
110
84.62%
228
76.00%
25
83.33%
34
85.00%
85
85.00%
120
92.31%
264
88.00%
28
93.33%
29
72.50%
90
90.00%
130
100.00%
277
92.33%
21
70.00%
36
90.00%
77
77.00%
100
76.92%
234
78.00%
26
86.67%
38
95.00%
88
88.00%
90
69.23%
242
80.67%
29
96.67%
31
77.50%
99
99.00%
90
69.23%
249
83.00%
24
80.00%
37
92.50%
55
55.00%
100
76.92%
216
72.00%
30
100.00%
31
77.50%
66
66.00%
130
100.00%
257
85.67%
30
100.00%
40
100.00%
100
100.00%
130
100.00%
300
100.00%
Ron's Auto Loan Payment Schedule Price
$23,450.00
Loan
$22,150.00
Term
6 years
Rate
2.69% annual rate
Payment schedule
Month
Beginning-Principle
Payment
Interest-Paid
1
$22,150.00
$333.48 $49.65
2
$21,866.18
$333.48 $49.02
3
$21,581.72
$333.48 $48.38
4
$21,296.62
$333.48 $47.74
5
$21,010.88
$333.48 $47.10
6
$20,724.50
$333.48 $46.46
7
$20,437.48
$333.48 $45.81
8
$20,149.82
$333.48 $45.17
9
$19,861.51
$333.48 $44.52
10
$19,572.56
$333.48 $43.88
11
$19,282.96
$333.48 $43.23
12
$18,992.71
$333.48 $42.58
13
$18,701.81
$333.48 $41.92
14
$18,410.25
$333.48 $41.27
15
$18,118.05
$333.48 $40.61
16
$17,825.18
$333.48 $39.96
17
$17,531.66
$333.48 $39.30
18
$17,237.49
$333.48 $38.64
19
$16,942.65
$333.48 $37.98
20
$16,647.15
$333.48 $37.32
21
$16,350.99
$333.48 $36.65
22
$16,054.17
$333.48 $35.99
23
$15,756.68
$333.48 $35.32
24
$15,458.53
$333.48 $34.65
25
$15,159.70
$333.48 $33.98
26
$14,860.21
$333.48 $33.31
27
$14,560.04
$333.48 $32.64
28
$14,259.21
$333.48 $31.96
29
$13,957.69
$333.48 $31.29
30
$13,655.51
$333.48 $30.61
31
$13,352.64
$333.48 $29.93
32
$13,049.10
$333.48 $29.25
33
$12,744.87
$333.48 $28.57
34
$12,439.96
$333.48 $27.89
35
$12,134.37
$333.48 $27.20
36
$11,828.10
$333.48 $26.51
37
$11,521.13
$333.48 $25.83
38
$11,213.48
$333.48 $25.14
39
$10,905.14
$333.48 $24.45
40
$10,596.11
$333.48 $23.75
41
$10,286.39
$333.48 $23.06
42
$9,975.97
$333.48 $22.36
43
$9,664.86
$333.48 $21.67
44
$9,353.05
$333.48 $20.97
45
$9,040.54
$333.48 $20.27
46
$8,727.32
$333.48 $19.56
47
$8,413.41
$333.48 $18.86
48
$8,098.79
$333.48 $18.15
49
$7,783.47
$333.48 $17.45
50
$7,467.44
$333.48 $16.74
51
$7,150.71
$333.48 $16.03
52
$6,833.26
$333.48 $15.32
53
$6,515.10
$333.48 $14.60
54
$6,196.23
$333.48 $13.89
55
$5,876.64
$333.48 $13.17
56
$5,556.34
$333.48 $12.46
57
$5,235.32
$333.48 $11.74
58
$4,913.58
$333.48 $11.01
59
$4,591.11
$333.48 $10.29
60
$4,267.93
$333.48 $9.57
61
$3,944.02
$333.48 $8.84
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
62
$3,619.38
$333.48 $8.11
63
$3,294.02
$333.48 $7.38
64
$2,967.93
$333.48 $6.65
65
$2,641.10
$333.48 $5.92
66
$2,313.55
$333.48 $5.19
67
$1,985.26
$333.48 $4.45
68
$1,656.23
$333.48 $3.71
69
$1,326.47
$333.48 $2.97
70
$995.96
$333.48 $2.23
71
$664.72
$333.48 $1.49
72
$332.73
$333.48 $0.75
Monthly payment:
$333.48 [(1) Create formula in E4 to calcualte monthly payment; with that payment, complete the payment schedule till the end of 72nd month]
Principle-Paid
$283.82
$284.46
$285.10
$285.74
$286.38
$287.02
$287.66
$288.31
$288.95
$289.60
$290.25
$290.90
$291.55
$292.21
$292.86
$293.52
$294.18
$294.84
$295.50
$296.16
$296.82
$297.49
$298.16
$298.82
$299.49
$300.17
[(2) Copy the spreadsheet to next sheet "GoalSeek"
, conduct a goal seek with target monthly payment of $250
, by changing loan amount
]
$300.84
$301.51
$302.19
$302.87
$303.54
$304.23
$304.91
$305.59
$306.28
$306.96
$307.65
$308.34
$309.03
$309.72
$310.42
$311.11
$311.81
$312.51
$313.21
$313.91
$314.62
$315.32
$316.03
$316.74
$317.45
$318.16
$318.87
$319.59
$320.30
$321.02
$321.74
$322.46
$323.19
$323.91
$324.64
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
$325.36
$326.09
$326.82
$327.56
$328.29
$329.03
$329.76
$330.50
$331.24
$331.99
$332.73
Submission format:
1. Display formulas (<Ctrl>-<~>)
2. Print the spreadsheet with formulas (I do not need spreadsheet with values - ONLY formulas).
<== formula
3. For Goal Seek result, see next sheet.
Ending-Principle
$21,866.18
$21,581.72
$21,296.62
$21,010.88
$20,724.50
$20,437.48
$20,149.82
$19,861.51
$19,572.56
$19,282.96
$18,992.71
$18,701.81
$18,410.25
$18,118.05
$17,825.18
$17,531.66
$17,237.49
$16,942.65
$16,647.15
$16,350.99
$16,054.17
$15,756.68
$15,458.53
$15,159.70
$14,860.21
$14,560.04
$14,259.21
$13,957.69
$13,655.51
$13,352.64
$13,049.10
$12,744.87
$12,439.96
$12,134.37
$11,828.10
$11,521.13
$11,213.48
$10,905.14
$10,596.11
$10,286.39
$9,975.97
$9,664.86
$9,353.05
$9,040.54
$8,727.32
$8,413.41
$8,098.79
$7,783.47
$7,467.44
$7,150.71
$6,833.26
$6,515.10
$6,196.23
$5,876.64
$5,556.34
$5,235.32
$4,913.58
$4,591.11
$4,267.93
$3,944.02
$3,619.38
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
$3,294.02
$2,967.93
$2,641.10
$2,313.55
$1,985.26
$1,656.23
$1,326.47
$995.96
$664.72
$332.73
$0.00
Ron's Auto Loan Payment Schedule
Submission format:
Screen shot the goal seek dialog box and the resulted data:
Price
$23,450.00
Monthly payment:
Loan
$22,150.00
$333.48 <== formula
Term
6 years
Rate
2.69% annual rate
Payment schedule
Month Beginning-Principle Payment
Interest-Paid
Principle-Paid Ending-Principle
1
$22,150.00
$333.48 $49.65
$283.82
$21,866.18
2
$21,866.18
$333.48 $49.02
$284.46
$21,581.72
3
$21,581.72
$333.48 $48.38
$285.10
$21,296.62
4
$21,296.62
$333.48 $47.74
$285.74
$21,010.88
5
$21,010.88
$333.48 $47.10
$286.38
$20,724.50
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help