Other four Assighnments for now

Office 2010 – myitlab:grader – Instructions

Exploring Excel Chapter 01 – IRCD project

Fernwood Art Center Adult Classes

Project Description:
In this project, you will create a spreadsheet detailing the list of adult classes offered by the Fernwood Art Center during the 2011-2012 season. You will calculate the cost of each class for those with a member discount. Additionally, you will create a formula to summarize the total fees collected for each class during the first session of the season. Finally, you will format your spreadsheet to make it visually appealing.

Instructions:
For the purpose of grading of the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the downloaded Excel file named Exploring_e01_Grader_IR.xlsx. 0
2 On the Session1 worksheet, in cell A3, enter Start Date as the text. In cell B3, enter 7/7/11 as the date. 0
3 On the Session1 worksheet, in cell E7, create a formula that will calculate the session fee with member discount. 10
4 On the Session1 worksheet, change the value in cell D8 to 280, and then clear the contents in cell A13. 2
5 On the Session1 worksheet, in cell H7, create a formula that will calculate the total fees collected for the Introduction to Watercolor class. 10
6 On the Session1 worksheet, use the fill handle to copy the formulas in cells E7 and H7 down through row 11. 16
7 Display the formulas on the Session1 worksheet, and then hide the formulas on the spreadsheet. Delete the worksheet labeled Sheet1. 8
8 On the Session1 worksheet tab, insert a new row above row 5. 4
9 On the Session1 worksheet, move the range A3:B3 to A14:B14. 8
10 On the Session1 worksheet, delete row 3. 4
11 On the Session1 worksheet, copy the range A5:E11. Click on the Class Fees worksheet tab, and then paste the copied range into cell A4. 6
12 On the Class Fees worksheet, change the width of column A to 30.00 (215 pixels). Change the width of columns D and E to 14.57 (107 pixels). 6
13 On the Session1 worksheet, copy the range A1:H2. Paste the values (without formatting) onto the Class Fees worksheet in cell A1. 8
14 On the Class Fees worksheet, merge and center the text in cell A1 across the range A1:E1, and then merge and center the text in cell A2 across the range A2:E2. 4
15 On the Class Fees worksheet, select the range A4:E5 and add a bottom border. With the range still selected, change the fill color to Tan, Background 2. 5
16 On the Class Fees worksheet, apply the Accounting Number format with zero decimal points to the range D6:E10. 5
17 On the Class Fees worksheet, change the page setup options so that the data is centered horizontally on the page. 2
18 On the Class Fees worksheet, create a header that will display the text Class Fees in the center section of the header area. Print preview the Class Fees worksheet. Return to Normal view. 2
19 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Class Fees; Session1; Session2; Session3. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed. 0
Total Points 100

Updated on: 15/3/2010 1 E_CH01_EXPV1_IRCD_Instructions x

2

>Class Fees

Session

1

Discount

Enrollment Totals

Member Non-Member Member

15

0.5

5

2 8

1

75 2 4

10 0.5

3 5

1 0.25 75 5 7

																						The Fernwood Art Center
		Adult Classes
Session 1 –			Enrollment Totals
		Class Name			Total Weeks							Member			Session Fee			Total Fees Collected
					Non-Member
		Introduction to Watercolor						10			0.				5						3				4			6
		Basic Drawing						8	2					7
		Tilemaking Workshop		0.25
		Black & White Photography	360
		Glass Bead Workshop
Add Oil Painting Workshop?

Session2

The Fernwood Art Center
Adult Classes

Class Name Total Weeks

Session Fee Enrollment Totals Total Fees Collected
Non-Member Member Non-Member Member

Introduction to Watercolor 10

3 8

Basic Drawing 8 50%

4 2

Tilemaking Workshop 1

1 5

Black & White Photography 10 50%

3 6

Glass Bead Workshop 1 25% $ 75 $ 56 7 2

Session 2 – Enrollment Totals
	Member Discount
					50%		$ 315		$ 158		$ 2,205
	$ 275		$ 138		$ 1,375
			25%				$ 75				$ 56		$ 356
	$ 360		$ 180		$ 2,160
	$ 638
	Start Date:	9/15/11

Session3

The Fernwood Art Center
Adult Classes

Class Name Total Weeks Member Discount Session Fee Enrollment Totals Total Fees Collected
Non-Member Member Non-Member Member
Introduction to Watercolor 10 50% $ 315 $ 158 3 8 $ 2,205
Basic Drawing 8 50% $ 275 $ 138 4 2 $ 1,375
Tilemaking Workshop 1 25% $ 75 $ 56 1 5 $ 356
Black & White Photography 10 50% $ 360 $ 180 3 6 $ 2,160
Glass Bead Workshop 1 25% $ 75 $ 56 7 2 $ 638

Start Date:

Session 3 – Enrollment Totals

1/19/12

Sheet1

Office 2010 – myitlab:grader – Instructions Exploring Series! Excel Ch. 02 – EOC Project

Buff and Tuff Gym

Project Description:
In this project, you will create a worksheet that calculates membership costs for Buff and Tuff Gym. You will create functions that determine costs based on a condition, lookup membership rates and fees, and calculate monthly payments. You will also create functions that summarize your data.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the downloaded Excel workbook named Exploring_e02_Grader_EOC.xlsx. 0
2 In the Circular Referencing Warning message box, click OK, and then close the Help window that opens automatically. Assign the name Membership to the range A18:C20. 5
3 In cell B2, enter a function that will return the value of today’s date. 4
4 In cell C5, create a VLOOKUP function that returns the membership cost that corresponds with the membership type entered in cell B5. The function will look for an exact match in the range named Membership, and return the membership cost from the second column. 10
5 Copy the VLOOKUP function down to cells C6:C13. 3
6 In cell E5, enter an IF function that will calculate the annual total. If the client has a Locker (D5) is “Yes”, then add the Cost (C5) to the Locker Fee (B22). Otherwise, the function will return the Cost only. Ensure that B22 is entered as an absolute reference. 10
7 Copy the IF function down to cells E6:E13. 3
8 In cell G5, enter a formula that will multiply the Annual Total (E5) by Years (F5) to calculate Total Due. Copy the formula down to cells G6:G13. 7
9 In Cell H5, create a VLOOKUP function that returns the down payment that corresponds with the membership type entered in cell B5. The function will look for an exact match in the range named Membership, and return the down payment from the third column. 10
10 Copy the VLOOKUP function down to cells H6:H13. 3
11 The formula in cell I5 is incorrect, and contains a circular reference. Edit the formula so that is calculates the difference between Total Due and Down Payment. Copy the formula down to cells I6:I13. 7
12 In cell J5, enter a function to calculate a monthly payment for the client. The function will refer to the annual interest rate (B23) divided by B24, a loan term (F5) multiplied by B24, and the balance due (I5). Refer to B23 and B24 as mixed cell references (B$23 and B$24). Refer to I5 as a negative value (-I5) so that your result returns a positive number. 10
13 Copy the payment function down to cells J6:J13. 4
14 In cell H18, enter a function that counts the number of entries in the range A5:A13. 4
15 In cell H19, enter a function that finds the lowest value in the range J5:J13. 4
16 In cell H20, enter a function that finds the average value in the range J5:J13. 4
17 In cell H21, enter a function that finds the highest value in the range J5:J13. 4
18 In cell H22, enter a function that finds the median value in the range J5:J13. 4
19 Format the range H19:H22 with the Accounting format. 4
20 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100

Updated on: 3/4/2010 1 E_CH02_EXPV1_EOC _Instructions x

2

>New

Client

s

Client

Yes 2 0

0

Deluxe No 2 0

Deluxe Yes 3 0

Individual No 1 0

Individual No 2 0

Individual No 3 0

Individual Yes 3 0

Membership Cost Down Payment

Deluxe

Family

Individual

Buff and Tuff Gym

Date Prepared:

Membership

Cost

Locker

Annual Total

Years

Total Due

Down Payment

Balance

Monthly Payment

Andrews

Deluxe

Yes

1

0

Baker

Individual

Carter

Family

No

3

Dudley

Evans

Foust

Gardner

Hart

Ivans

Totals

Summary Statistics

$ 575

$ 250

Number of New Members

$ 1,500

$ 700

Lowest Monthly Payment

$ 300

$ 150

Average Monthly Payment

Maxium Monthly Payment

Locker Fee

$ 75

Median Monthly Payment

Interest Rate

5.75%

Months Per Year

12

Office 2010 – myitlab:grader – Instructions Exploring! Excel Ch. 02 – IRCD Project

Crazy Cars Auto Sales

Project Description:
In this project, you will create a worksheet that calculates sales and commissions for a used car company. You will create functions that determine monthly payments, lookup commission rates, and calculate a bonus for the salespeople.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the downloaded Excel workbook named Exploring_e02_Grader_IR.xlsx. 0
2 In cell G5, enter a formula that calculates the state sales tax by multiplying the base cost of the car (in F5) by 7%. Copy the formula down to cells G6:G16. 11
3 In cell H5, enter a formula that adds the sales tax (in G5) to the base price of the car (in F5). Copy the formula down to cells H6:H16. 11
4 In cell I5, enter a function to calculate a monthly payment for buyers. The function will refer to the annual interest rate (in D21) divided by 12, a loan term of 48 months, and the total cost of the car (in H5). Refer to the annual interest rate (D21) as an absolute cell reference. Copy the function down to cells I6: I16. 11
5 In cell J5, create a VLOOKUP function that returns the salesperson commission that corresponds with the base cost of the car (in F5). Use the range $C$24:$D$28 as the Lookup Table, and return the commission rate from Column 2. 10
6 Copy the VLOOKUP function down to cells J6:J16. 5
7 In cell G19, enter a function to sum cells H5:H16. 7
8 In cell G20, enter a function to average the cells in the range H5:H16. 7
9 In cell G21, enter a function to find the lowest value in the range H5:H16. 7
10 In cell G22, enter a function to find the highest value in the range H5:H16. 6
11 In cell G23, enter a function that will return the value of today’s date. 5
12 In cell G24, enter an IF function that will calculate a bonus for the part-time sales agents. If the value in cell G19 (Total for all sales) is greater than 250000, then multiply G19 by .015. Otherwise, the function will return a value of 0 (i.e. no bonus). 10
13 Format cell G24 with the Currency format and two decimal places. 4
14 In cell C17, enter a function that counts the number of entries in the range C5:C16. 6
15 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100

Updated on: 23/2/2010 1 E_CH02_EXPV1_IRCD _Instructions x

Sheet1

Brown

6/10/12

Taylor

25,000 6/17/12

2007 Hybrid Sport Utility

6/17/12

Harris

36,000

30,000 6/10/12

Robinson

6/24/12

6/10/12

Walker

6/24/12

30,000 6/17/12

King

25,000 6/24/12

Brown

Taylor

Harris

Robinson

Walker

King

																																								Crazy Cars
Auto Sales and Commissions (Part-Time Staff)
Salesperson	Car Description	Mileage	Purchase Date	Base Cost of Car	State Sales Tax	Total Cost with Tax	48-month Payment Plan	Salesperson Commission Rate
		Brown	2003 Hybrid Sedan	100,000				6/17/12	$ 9,800.00
2007 Hybrid Sedan			25,000				6/10/12	$ 21,800.00
		Taylor		2007 Hybrid Sport Utility		36,000	$ 21,500.00
2008 Hybrid Sport Utility	$ 40,700.00
		Harris			30,000	$ 29,800.00
2007 Sport Utility (SUV)				6/24/12	$ 22,800.00
		Robinson	2007 Minivan-LX	$ 23,400.00
2007 Minivan-EX	40,000	$ 25,800.00
		Walker	1998 Minivan-GL	133,000	$ 3,000.00
2008 Minivan-LE	24,000	$ 23,900.00
		King	2007 Crossover-SE	$ 21,600.00
2008 Crossover-SE	$ 21,900.00
Total for all sales:
Average price:
Interest rate:	6.25%	Lowest price:
Highest price:
Lookup Table	Today’s date:
3000	0.01	Bonus:
10000	0.02
20000	0.025
30000	0.03
40000	0.035

Office 2010 – myitlab:grader – Instructions Exploring! Excel Ch. 03 – IRCD Project

Campus Events Ticket Sales

Project Description:
In this project, you will create and format charts that display ticket sales for campus events. You will create and format a 2-D column chart, a 3-D pie chart, and insert sparklines to view data trends quickly.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the downloaded Excel workbook named Exploring_e03_Grader_IR.xlsx. 0
2 Select cells A4:E10. Insert a 2-D Clustered Colum chart based on the selected data. 10
3 Move the chart so that its top left corner is in the upper left corner of cell A13. Change the width of the chart to 6″ and the height to 4″. 7
4 Select the nonadjacent ranges A4:A10 and F4:F10. Insert a 2-D Pie chart based on the selected data. 10
5 Move the pie chart to a new worksheet named Pie Chart. Apply the Chart Style 26 and the Chart Layout 6 to the pie chart. 10
6 Edit the data in the pie chart so that it includes the ranges A4:A10 and D4:D10 (ticket sales for November only). Change the chart type to Pie in 3-D. 10
7 Set the Homecoming Dinner-Dance data point explosion to 50%. Format the data labels to show values (in addition to the existing percentages) but not leader lines. 9
8 Modify the chart title of the pie chart to be November Ticket Sales. 3
9 On Sheet 1, add the title Event Tickets by Month above the chart. Insert data labels at the outside end of the November data points. 9
10 In the 2-D Column chart, add a rotated primary vertical axis title as Tickets Sold. Change the fill color of the November data series to Light Blue (under Standard Colors). 10
11 Add a linear trendline to the November data series. 6
12 Insert column sparklines in cells G5:G11. Include the data range B5:E11 (September through December data) in the charts. Show the high point in each of the sparklines. Set the marker color of each high point to Green (under Standard Colors). 10
13 Enter Trends in cell G4. 3
14 Prepare Sheet1 for printing by changing the orientation to Landscape. Ensure that the worksheets are correctly named and placed in the following order in the workbook: Pie Chart; Sheet1. 3
15 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100

Updated on: 11/7/2011 1 E_CH03_EXPV1_IRCD _Instructions x

Sheet1

2,500

2,000

250

– 0

2,500 500

2,000 1,500 750 6,000

	Campus Events Ticket Sales
Fall Semester
September	October	November	December	Total for Semester
Football games-home	4,000		6,000	6,	500		– 0	16,500
Concerts			2,000			2,500	2,	750	2,	250	9,500
On-campus movies	3,000	2,875	10,375
Homecoming Dinner-Dance	350	400	1,000
Theatrical events		1,500	2,250	6,750
Fundraisers	1,750
Total for Month	12,000	16,100	16,525	5,500	50,125