4 DVP Assignments

These are 4 DVP assignments for the Web class , each assignment should be independent the instructors for each assignment are attached, please see the attachments.
dvp2_1.docx

dvp3_loan_payment_1.docx

Don't use plagiarized sources. Get Your Custom Essay on
4 DVP Assignments
Just from $13/Page
Order Essay

dvp5_dog_breeds_2.docx

start_file_dvp5_1.xlsx

dvp_7_instructions__1_.docx

Unformatted Attachment Preview

DVP2 UNIVERSITY COSTS
This assignment entails comparison of the cost of attending university at several schools. You may use Microsoft Excel
(either 2013 on a pc or 2011 on a mac) or Google spreadsheet to complete this assignment. In order to successfully
complete this assignment you will need to be able to do the following:
•
•
•
•
•
•
Apply order of mathematics
Use absolute cell references
Add worksheet and label it
Apply conditional formatting
Use relative cell references
Use cell references from different worksheet
Recreate the following table in a new worksheet:
A
1
B
C
D
E
F
Cost of Univeristy
2
3
Weber State University
4
2015-2016 2016-2017 2017-2018 2018-2019 Total
5
Tuition
4326
6
Fees
858
7
Books/Supplies
800
8
Total
9
10 Another University
11 Tuition
12 Fees
13 Book/Supplies
14 Total
15
16 Another University
17 Tuition
18 Fees
19 Books/Supplies
20 Total
21
22
23 Average annual percentage increase a year
4.2%
RESEARCH TUITION DATA FOR THREE ADDITONAL SCHOOLS
You will need to find data for 2 additional colleges/universities for 2015-16. Find data that lists the tuition, fees
and books/supplies separately.
Calculate the average annual increase per year. That increase is found in cell F23. To calculate the increase
take the previous year amount and multiply by the percentage rate and then add in the amount. Be sure to
use an absolute cell reference for the annual percentage increase. Copy this formula over for the years shown.
So in Cell C5 it would read =B5*$F$23+B5.
FORMATTING
Format cell A1 so the data is merged and centered through column F. Turn on title style and center the data.
Make sure the cells showing the University Name are merged and centered; turn a cell style on those cells that
contain the University Name.
Change the column width for Column A to 15.
Turn on currency or accounting style for all numbers except the percentage which should be displayed as a
percentage with 2 decimal places.
The total rows should be formatted with the Total Cell Style.
Merge and center the data in row 23 through column D.
Rename the sheet University Costs
ADDING AN ADDITIONAL SHEET
Access a new sheet and name that sheet Weber State University
In Cell A1 type Weber State University. Format cell A1 using any three different features (bold, font color, size,
italics, font, etc.)
In Cell A2 type Cost to attend 2015-2016
In Cell A3 you are going to access the amount shown on Sheet 1 so it shows in Sheet 2. To do this you will need
to type an = sign in cell A3, then you will click on Sheet 1 and click on cell C8.
CREATING GRAPHS
Create a bar graph for Weber State University that shows Tuition, Fees and Book/Supplies for the 2015-16
school year. Move the graph to a new sheet. Name that sheet WSU Graph.
SUBMIT DVP2
DVP3 LOAN PAYMENT CALCULATOR USING
NAMED CELLS
This assignment requires the use of a financial function (PMT) which is used to calculate the payment on a loan. We will
also calculate the total interest paid on a loan.
In order to successfully complete this assignment, in addition to the concepts in previous assignments, you also need to
know how to complete the following tasks in a spreadsheet software:
•
•
PMT function
Define names for cells
IF you completed the previous assignment using Excel, switch to a Google spreadsheet. IF you used Google previously,
switch to a Microsoft product.
CREATE NEW WORKBOOK. RECREATE THE LABELS AS SHOWN IN THE EXAMPLE
This is what the worksheet looks like to begin with. In this example, I’m buying a car that cost $15,000 and am putting
$5,000 down.
To figure out how much the loan amount is (how much
money you are borrowing), we know to use a formula
=B4-B5 – right? BUT if the cells have names, the formula
will be =Price-Down Payment, which is easier to read and
remember. There are 9 cells that will have values in them
(B3, B4, B5, B6, D3, D4, D5, D7, and D9). The labels that
identify these values are just to the left of these cells (A3,
A4, A5, A6, C3, C4, C5, C7, and C9).
Look up the current interest rate for an auto loan and enter the number of years you wish to have the loan (typically 3 or
5). Enter these numbers now.
NAMING CELLS
Insert Menu, Name menu, Create (on a mac)
Formulas TAB, Defined Names GROUP (on a pc)
Highlight (select) cells A3:B6, which includes both the labels for the cells and the cells that values will go it, then go to
CREATE FROM SELECTION (pc)
I want to create names that are in the left column FROM VALUES so I need to click Top row to take that check mark off.
THEN the names are going to be defined from the left column.
For BOTH the mac and the PC, you can select two cells, the one with the
value (i.e., B4, the cell that contains 15000). Then go to the name box and
type in the text you will use to name this cell. In this example, I would type
Price.
WHY BOTHER?
NAMING CELLS is used for a three reasons:
1. To make formulas easier to read
2. To locate cells (if you go to the Name Box and choose the cell named, the cursor will jump to that cell).
3. In place of absolute cell references. Once you name a cell, you can use the name and don’t need to worry about
absolute. Naming a cell makes it absolute.
Repeat the process for all of the cells that contain values or will contain values in columns B and D so when you create
your formulas, you will have the words, not cell references.
INSERT FUNCTIONS AND FORMULAS
1. To determine the loan amount, subtract the down payment from the price.
2. Use the PMT function to determine the monthly payment.
There are three parts to the payment function: the interest rate, the number of periods for the loan, and the
present value (PV) which is the amount to be borrowed.
Since the given rate is an annual rate and you are making monthly payments, you need to divide the interest
rate by 12. Since the number of periods given is in years and you are making monthly payments, you need to
multiply the years by 12. Lastly, the payment is money you are paying out so the number is negative.
To convert the payment to a positive number, put a – (minus) in front of the PMT.
The formula will be: =-PMT(Annual_Interest_Rate/12,Years*12,Loan_Amount)
3. To determine the total interest, multiply the years by the Payment by 12 and subtract from that the loan
amount.
4. To determine the total cost, add the price and total interest.
FORMAT WORKSHEET
Format the Loan Payment Calculator to emphasize the important items of information and to portray all numbers with
the correct format (percentages and money). Adjust column widths so that all information displays.
ADJUST VALUES
Enter data for a home loan. To complete this exercise you need to locate the following:
•
•
•
Cost of home (choose one)
Value of down payment (at least 10%)
Current interest rates on mortgages
Enter the values in the appropriate cells. Determine the number of years for the loan. Typical mortgages are 30-year, 20year, or 15-year loans. Adjust the number of years until you are comfortable with the monthly payment.
INSERT IMAGE
Insert an image of the house you wish to purchase.
READY TO SUBMIT?
Save the spreadsheet. Check the grading rubric to be sure you have completed all of the steps required, make changes
as needed, and resave. When you are finished, submit the completed file through the Canvas assignment submission
window.
DVP5. DOG BREED STATISTICS
In order to successfully complete this assignment, in addition to the concepts in previous assignments, you also need to
know how to complete the following tasks in a spreadsheet software:
•
Convert text to
columns
•
Filter
Use Microsoft Excel for this assignment. There is a Word file attached for you
to fill out and submit as well.
GET DATA
Open up the Start File.
PREPARE DATA FOR ANALYSIS
Save workbook as DVP5 Dog Breeds Last name.
Name the worksheet Original Data. This worksheet will not be used again. It
is saved here in case there is a mistake and we need to go back to it.
Copy the entire worksheet and paste to another worksheet. Name this
worksheet Parsed Data. You will begin working in the parsed data sheet.
PARSE DATA INTO COLUMNS
Notice there is one word for some dogs, and some have two. We need to
separate these words into separate columns so we can analyze the data
correctly. To prepare the worksheet to accommodate this new structure,
move the yearly data to the right so you have one blank columns.
In Excel, there is a data command that will parse the data: Text to Columns
(pc: DATA: Data Tools: Text to Columns) (mac: DATA:text to columns).
In both the mac and the pc, there is a wizard that will assist you in converting the data from one column to multiple
columns. Follow the wizard steps and make the following decisions:
•
•
•
•
•
Select the data in column A
Choose Delimited (as file type that best describes data)
The Delimiter that separates the data is a Space. Change the checkmark from Comma to Space.
Notice when you change the delimiter, the data is displayed by columns.
The last step of the Wizard allows you to set the data format. The data is Text. The destination by default is the
upper left corner of the data, don’t change it.
EDIT DATA AS NEEDED
Remove the data for 2010 by deleting the entire column.
Change the column headings for the other years to text field types (HOME: Number: Text). These fields imported as
numbers and it matters later that they aren’t considered numeric data.
DISPLAYING DATA VISUALLY
Copy the first 11 rows of data and paste to another sheet. Name the sheet Top 10.
FILTER
Go back to Parsed Data. Turn on the filter option for Excel.
1. Filter the number of hounds. Take a screenshot and paste into a new sheet. Name sheet filter 1.
2. Filter the number of spaniels. Take a screenshot and paste into a new sheet. Name sheet filter 2.
3. Filter the number of Terriers. Take a screenshot and paste into a new sheet. Name sheet filter 3.
READY TO SUBMIT?
Resave the spreadsheet. When you are finished, submit the file through the Canvas assignment submission window.
Breed Dog
Retrievers Labrador
Shepherd German
Beagles
Retrievers Golden
Terriers Yorkshire
Bulldogs
Boxers
Poodles
Dachshunds
Rottweilers
Shih Tzu
Schnauzers Miniature
Pinschers Doberman
Chihuahuas
Pointers Shorthaired
Huskies Siberian
Pomeranians
Bulldogs French
Danes Great
Sheepdogs Shetland
Spaniels Cavalier
Terriers Boston
Maltese
Shepherds Austrialian
Corgis Welsh
Pugs
Spaniels Cocker
Mastiffs
Springer Spaniels
Brittanys
Havanese
Weimaraners
Bernese Mountain
Highland Terrier
Collies
Vizslas
Papillons
Frises Bichons
Bullmastiffs
Hounds Basset
Pinschers Miniature
Newfoundlands
Ridgebacks Rhodesian
Collies Border
Chesapeake Retrievers
Akitas
2011
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
34
35
36
37
38
39
40
41
42
43
44
45
46
47
2010
1
2
4
5
3
6
7
9
8
11
10
12
14
13
16
18
15
21
17
19
23
20
22
26
27
24
25
28
29
30
31
32
39
34
38
41
35
37
42
36
40
44
46
47
48
49
2006
1
3
5
4
2
12
7
8
6
17
9
10
21
11
20
25
14
36
24
19
27
15
18
34
22
13
16
32
26
31
38
30
41
33
37
43
35
29
40
28
23
44
54
56
50
53
2001
1
3
5
2
6
19
8
7
4
11
10
13
24
9
22
21
12
64
28
16
44
18
20
35
26
15
14
37
27
31
75
29
54
30
33
45
40
25
49
23
17
50
57
61
41
39
Bloodhounds
Bernards
Shar-Pei Chinese
Terriers Bull
Wheaten Terriers
Inu Shiba
Terriers Scottish
Terriers Airedale
Whippets
Malamutes Alaskan
Terriers Cairn
Cattle Austrialian
Crested Chinese
Apsos Lhasa
Spaniels Cocker
Pekingese
Greyhounds Italian
Chows
Samoyeds
Setters Irish
Pyrenees Great
Staffordshire Terriers
Dalmatians
Malinois Belgian
Pointer Wirehaired
Terriers Bull
Griffons Brussels
Chin Japanese
Wolfhounds Irish
Terriers Border
Corgis Welsh
Mountain Swiss
Flandres
Sheepdogs English
Terriers Silky
Terriers Tibetan
Setters English
Hounds Afghan
Terriers Norwich
Retrievers Flat-Coated
Schnauzers Standard
Griffons
Basenjis
Setters Gordon
Schnauzers Giant
Elkhounds Norwegian
Terriers Russell
48
49
50
51
52
53
54
55
57
58
59
60
61
62
63
64
65
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
43
45
50
53
59
63
52
54
60
58
56
64
57
62
66
61
67
65
72
77
71
70
69
76
73
74
80
75
79
83
84
88
85
81
78
90
101
86
100
103
95
93
89
98
94
106
92
45
39
47
61
62
65
42
55
60
57
48
67
52
46
74
49
59
64
76
68
58
66
85
90
73
80
63
72
77
81
79
97
86
71
70
95
98
87
94
100
99
105
84
88
83
92
75
48
36
38
80
56
55
43
46
67
53
42
72
62
34
76
32
51
52
66
59
47
63
58
94
73
95
77
74
86
87
85
101
81
68
60
91
89
90
99
98
97
115
71
84
82
83
70
Keeshonden
Terriers Welsh
Terriers Fox
Borzois
Tervuren Belgian
Coonhounds Black
Shepherd Anatolian
Collies Bearded
Terriers Fox
Spaniels Tibetan
Pointers
Eskimo American
Salukis
Sheepdogs Belgian
Terriers Manchester
Terriers Norfolk
Terriers Austrailian
Italiani Spinoni
Terriers Blue
Briards
Terriers Bull
Spaniels Toy
Spaniels Springer
Terriers Irish
Spaniels Clumber
Terriers Bedlington
Terriers Lakeland
Affenpinschers
Greyhounds
Spaniels Field
Deerhounds Scottish
Kuvaszok
Hounds Ibizan
Spaniels Irish
Hounds Pharaoh
Lowchen
Retrievers Curly-Coated
Pulik
Spaniels Water
Sheepdogs Lowland
Terriers Dinmont
Spaniels Suzzex
Terriers Sealyham
Terriers Skye
Komondorok
Spitz Finnish
Otterhounds
98
99
100
102
108
109
111
112
113
114
115
116
117
118
119
120
121
123
124
125
127
129
130
132
133
134
135
139
140
141
142
148
149
150
151
152
154
156
157
159
160
161
163
164
166
167
169
87
105
97
96
108
91
109
112
110
104
111
114
115
116
121
117
123
118
120
125
130
126
127
128
131
140
137
136
139
132
141
144
151
148
156
147
146
145
143
149
164
155
152
160
154
158
161
93
91
78
96
107
131
111
104
102
101
103
109
116
121
106
115
113
117
114
118
129
120
124
123
122
128
127
125
136
126
135
130
138
139
141
132
137
140
133
142
144
145
149
146
148
147
152
79
92
69
88
106
125
119
96
93
102
100
103
107
108
105
114
104
122
109
113
132
116
112
110
120
118
126
117
123
133
121
127
140
131
135
134
129
130
124
128
141
145
142
138
139
136
147
Foxhounds English
Harriers
Foxhounds American
171
172
173
167
165
166
155
154
151
144
143
146
DVP 7 Instructions
You will need to open the DVP7 Start File to complete this assignment.
Spreadsheet
Enter Data and Set Up Formulas
Place
AB Furniture
Gustons
Tarrs
Justin and Sons
Three Brothers
Sofa
Love Seat End Tables Lamps Pillows
800
550
100
80
50
1100
600
125
50
40
1525
790
400
110
25
500
400
124
65
35
1422
650
199
125
60
1. Enter the data shown in the table above, into the Start File.
2. Insert a column to the left of column H (Total Cost of Loan). Add the column heading Loan
Payment.
3. Figure the total cost of each furniture set using the sum fuction.
4. Figure the Monthly Payment for each furniture set by using the PMT function.
5. Figure the Total Cost of the Loan by multiplying the payment by the loan period.
6. Based on the Total Cost of the Loan, use the appropriate functions to determine the Statistics
requested in the Statistics Section. The answers should appear in column B.
7. In Column J enter the column title Interest Paid. Calculate the interest paid on the furniture.
8. Resave the file
Format Worksheet
1. Change the theme of the workbook to Ion.
2. Format the title and subtitle as follows.
a. Merge and center the title (Furniture) across columns A through J. Apply the title style.
b. Marge and center the subtitle (your name) across columns A through J.
c. Change the background color of the title and subtitle cells using on the themed colors.
d. Change the font color of these two cells to one of the theme colors. Use color
combinations that have enough contract to be legible.
e. Bold the font in A1 and A2.
3. Format the column headings as follows.
a. Apply the Heading 3 cell style to all column headings in Row 4.
b. Wrap two word column headings to two lines.
c. Center align and middle align column headings.
4. Autofit all columns
5. Format the cells in the worksheet (columns A-G) that contain values that are costs to currency
style with no decimals.
6. Format the cells in the worksheet (columns H-J) that contain values that are costs to currency
style with 2 decimal places.
7. Change the Interest Rate to be formatted as a percentage with 2 decimal places.
8. Sort the spreadsheet by the Total Cost of the Loan in ascending order.
9. Autofit all columns again.
10. Name the sheet tab – FURNITURE – ORIGINAL DATA.
Sheet 2
Copy your entire spreadsheet to sheet two. Name sheet two – COUPON
If your columns are not the correct size, autofit the columns again.
All items are 20% off.
Create changes to your spreadsheet reflecting the discount.
Sheet 3
Copy your entire ORIGINAL spreadsheet to sheet three. (not the one with the discount).
Name Sheet three 6.5% interest
If your columns are not the correct size, autofit the columns again.
Change your interest rate to 6.5% and change the Loan Period in Months to 36.
Sheet 4
Copy your entire ORIGINAL spreadsheet to sheet four. Name the sheet NO PILLOWS
If your columns are not the correct size, autofit the columns again.
Delete the column with the pillow information.
Construct Two Charts
Stacked Column Chart – Be sure your are in your ORIGINAL DATA
1. Create a stacked column chart which contains the costs for each furniture item. So each column
should represent one of the stores. The store name should display on the Y axis. If the data
doesn’t display this way switch row and column. Add the title Furniture to the chart.
2. Remove the grid lines
3. Remove the values on the x – axis
4. Move the position of the legend so it displays on the top.
5. Move the chart so it is embedded (this just means in the spreadsheet) in column D through J and
between rows 10 and 20.
Doughnut Chart
1. Create a doughnut chart for Three Brothers Furniture. Select the row headings and then select
the row that contains the information about the furniture from Three Brothers.
2. Format the chart style to Style 8.
3.
4.
5.
6.
7.
8.
9.
Remove the legend. Display the category name and percentage for each section of your chart.
Change the labels to a color that you can see.
Move the labels so each one is legible.
The chart title should read Three Brothers Furniture.
Add a furniture image to the center of the doughnut.
Move the doughnut chart to a new sheet.
Name the sheet Three Brothers Furniture.
Add Header
Add a header to your Original Data worksheet. Key your name, centered, Key your instructors name
right aligned.
Presentation
Slide 1 – Picture with Caption – layout
1. Title – Furniture
2. Subtitle – Your Name (the placeholder says Click to add text)
3. Insert an image of furniture that you like in the picture placeholder.
Slide 2 – title and 2 content layout
1.
2.
3.
4.
5.
6.
Add a Smart Art graphic on the right side – the Vertical Curved List
Add each of the items in the furniture set to the vertical curved list
In each circle add a graphic of that particular item.
The title of the slides should read Furniture Purchase
On the left side insert any shape.
In the shape write the name – Three Brothers Furniture
Slide 3 – comparison layout
1.
2.
3.
4.
5.
Left side title – Three Brothers Furniture
Right side title – Furniture Choices
Copy the column chart from Excel to the right side. Make it 3.5 inches tall.
Copy the doughnut chart from Excel to the left side. Make it 3.5 inches tall.
Title the slide – My Furniture PUrchase
Slide 4 – Blank layout
1. Insert a new slide
2. Format the background as a picture of furniture. If the theme you have chosen hides the
background image, check the Hide Background Graphic box. Change transparency to 40%.
3. Insert a text box
4. Text – Three Brothers Furniture
5. Change the font
6. Increase the size of your font to between 60 and 70.
7. Add a fill to the background of the textbox that is a color that is in the image on the slide.
8. Change the font color to a contrasting color that is in the image on the slide.
9. Move the textbox to the top of the slide.
10. Resize the textbox so it …
Purchase answer to see full
attachment

GradeAcers
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.

Order your essay today and save 15% with the discount code DISCOUNT15