CSS 305 Interdisciplinary Information Technology

Workgroup Assignment #1 – May6, 2003

Work as groups of either 2 or 3 students. Attach spreadsheet printouts and graphs to this when handing it in.

Names in this group:

1) Create a spreadsheet for a budget. This budget sheet should have the following categories and subcategories:

a) Personnel: Professional, Support Staff, and Hourly Workers. In each category, estimate the number of months or hours to be worked, the rate of pay, and the total for this subcategory. Make some assumptions about the number of workers in this small manufacturing company.

b) Operations budget: Office supplies, Travel, Equipment, Service Contracts, office lease, and advertizing. These figures may depend on the number of workers above. For each, estimate an amount per month and multiply by the number of months to get subcategory totals.

c) Debt Service and investment: It has been decided to spend an amount equal to 25% of the Personnel budget plus 30% of the operations budget to retire debt and make investments.

Total up the categories and finally get a grand total for the entire budget. Lay these out so that the various levels of category totals fall in different columns, making the spreadsheet easy to read.

Include a pie chart and a bar chart showing the totals for the three major categories.

2) Find the data set crashdat from the data set directory. Open it in a browser, cut and paste the entire data set to the clipboard, and paste it into Excel

a) Sort the table based on the number of doors. Use the sort function in the data menu.

b) Use the subtotal function in the Data menu. Subtotal based on changes in doors. Select average for the subtotal function. Select the following to be subtotaled: Head IC, chest decel, L Leg, R Leg, Wt. This should insert subtotal lines within the table at each change in the number of doors.

c) note the outline that has appeared on the left hand side of the table. Experiment with it until only the subtotal and grand total rows appear. Print those lines.

d) Return to the subtotal menu and replace the average function with the count function and print out a table giving the number of cars in each category.

e) Use the plot wizzard to make xy-scatter plots of each of the four measurements (Head IC, chest decel, L Leg, R Leg) vs Wt. Try to determine from these four plots if there is a correlation between any of the measurements and vehicle weight.