CS&E 1113 assignment 3

Download from Carmen: Lake West University.xlsx and Klein Technology Seminars

ALL BEGINNING FILES MUST BE DOWNLOADED FROM Carmen OR THEY WILL NOT BE ACCEPTED.

(Unless otherwise noted)

Part 1: Lake West University

Read from the book, Spreadsheet Programming for Business:

Case 5: Lake West University, Pages 397-400

Read the entire case problem set before you continue!

Step 1: Analyze the problem and create the spreadsheet skeleton

In subsequent labs you may be required to create your own spreadsheet design as well as “solve” the problem set. Spreadsheet design is the most important factor in creating functional spreadsheets. It will take you an enormous amount of time to set up your worksheet, but if you set it up correctly, you will also save an enormous amount of time, frustration, and spreadsheet errors in the future. In real estate it is location, location, location… Well, in spreadsheet creation, it is design, design, design!

After reading the case, you surmise that Dr. Haddock wants a spreadsheet to analyze statistics for all of the ECON 2103 classes that he coordinates. You have noted the following from reading Dr. Haddock’s requirements.

He has created a spreadsheet that has the correct and incorrect responses for each question by section, classification, and Major. You will need to modify this workbook to create summary data Dr. Haddock would like to analyze.

  • Look at the workbook he has provided to ascertain how you can use this information.
  • There is a spreadsheet for each section number that includes the lecturer’s name, the student’s major, classification, and the answer provided by question.

Your investigation (i.e. reading the case) has led you to the following conclusions about the worksheets.

Follow the steps below to complete this part of the Assignment.

  • You need to create one worksheet (named, “Response”) that will contain all the detailed response information.
  • You will need to create a worksheet (named, “Analysis By Major”) that will summarize by major and question, the number of correct responses, incorrect responses and the percentage of correct answers. There is already a sample “Analysis by Major” worksheet set up in the workbook you have downloaded.
  • You will need to create a worksheet (named, “Analysis By Section”) that will summarize by section and question, the number of correct responses, incorrect responses and the percentage of correct answers.
  • You will need to create a worksheet (named, “Analysis By Classification”) that will summarize by classification and question, the number of correct responses, incorrect responses and the percentage of correct answers.
  • You will need to add some conditional formatting to identify any percentages of correct responses that are less than 70%.

Step 2: Create the spreadsheet skeletonand fill in the formulas

I have created the beginning skeleton based on my perception of Dr. Haddock’s requirements. When you are in a business environment, your perception of the spreadsheet details may not be the same as your managers’; therefore you will probably need to make many modifications to the design and the contents as you are working on the project, but when you are finished you will have a masterpiece and your manager will love you and call you the “Excel Master of the Universe”. 

Because I am your manager in this class, you will follow my design perceptions as described below.

  • Worksheet Response will contain information from the Section 1, Section 2, Section 3, Section 4, and Section 5 spreadsheets. You will copy and paste these worksheets into your Response worksheet.
  • You will add aStud # to each record by starting with a 1 in cell A4, and then in cell A5 you will type in the formula, =A4+1 and copy this down
  • A student will receive 1 point if they have answered the question correctly and these points will be added together to obtain the student’s final score.
  • Write an IF function in cell O4 that can be copied across and down to cell X103 that displays a 1 in the cell if the student has the correct answer and a 0 if the student does not. (The correct answers for each question are denoted in cells E2:N2)
  • Column Y will total each student’s score.
  • Worksheet AnalysisBy Majorwill summarize by major and question, the number of correct responses, incorrect responses and the percentage of correct answers.
  • This worksheet has already been set up for you in the workbook you downloaded.
  • Observe the spreadsheet design. It is easy to read and is set up so you can type in one formula in cell C3 and copy it to Cell L3, one formula in cell C4 and copy it to L4, and so forth.
  • Worksheets Analysis By Section and Analysis By Classification.
  • Copy and paste Worksheet Analysis By Major, into two new worksheets named, Analysis By Section and Analysis By Classification,change some column headers and change a few cell references to obtain your results

That is good spreadsheet design my friend. There are several other ways you could design this spreadsheet, but remember there is good design and poor design, and if you have an initial poor design, then GIGO!!! (Garbage In, Garbage Out)

Part 2: Klein technology seminars

Readfrom the book, Spreadsheet Programming for Business:

Klein Technology Seminars, Pages 385-395

  • Read the entire case problem set and the notes below before you continue.
  • Note: The project talks about pivot tables and pivot charts. We have not yet covered these concepts so ignore the references to these excel functions.

You had a meeting with Dr. Klein to discuss the actual scope of the project as the details he gave you were sketchy at best. From this conversation, you decided to divide this project into several different phases. The first phase, which you will complete now, will be to generate statistics by instructor.

You still have the initial worksheet that contains the survey information for each class. This worksheet is named; Initial Data and you will use it to create your instructor statistics. This will be used for input only; therefore, you don’t need to format it.

FOLLOW THE STEPS BELOW TO COMPLETE THIS PART OF THE ASSIGNMENT.

Your first worksheet will contain responses by Question number, i.e. the number of students per each category. (Strongly Disagree, Disagree, etc.) You don’t have to include questions 1 or 2 because they do not contain any relevant data for this sheet.

  • The skeleton has been created for you in the spreadsheet named, Response by Question. You should be able to write one formula in cell C3 and copy it over and down to obtain the correct answers.

The second sheet named, Instructor Stats will contain individual instructor statistics. Again, you don’t have to include questions 1 or 2 because they do not contain any relevant data for this sheet.

  • You will want to display the average score by instructor and by Question number. For example, the average score of Question 3 for Instructor 1 is 3.67.
  • Set this up so you only have to type in one formula and copy it over and down.
  • You will also want a cumulative average of all questions by instructor and you should rank the average by instructor. In addition, the average, minimum, and maximum of each question should be calculated.
  • Make sure you design this so the formulas can be easily copied if needed.

The third sheet named, Average Class Size should display the average class size by instructor and by location.

  • This spreadsheet should be set up so that you only have to type in one formula and copy it over and down.
  • You should also use the IFERROR() function so that if no classes were given in that specific location a 0 will display.

Remember, there are various correct ways to design this spreadsheet and various incorrect ways as well. You should use the information you have learned thus far to create a useful worksheet. Try to create formulas that can be easily copied and try not to hard-code values in your formulas. Use formulas and mixed, absolute, and relative cell references whenever possible.

Make sure to design a spreadsheet that is easy to read and easy to modify. Dr. Klein should be able to quickly look at the worksheet to glean the information he is seeking.

Part 4: maxi’s grocery mart

Complete from the book, Spreadsheet Programming for Business:

Case 3 (Maxi’s Grocery Mart, pages 379-382)

You will be creating your file, therefore you will not have a starting file on Carmen.

Please Read the information below before you create the Pro Forma worksheet. Do not complete the Information Specifications section at this time. You will be instructed how to complete this part after you have created the Pro Forma worksheet.

In this case you are asked to create a pro forma statement. Because you do not know the format of a pro forma sheet, and you may not even know what a pro forma statement is, you should do some investigation on the web. Excel has a few pro forma templates that you can use, but you are not required to use them however; you can use any sample you find. If you decide to use an Excel template, do some more investigation to learn how you can download the template and use it.

  • Although your book states that you should have an input section and an output section that is placed on the same spreadsheet, this is an inordinate amount of information for one worksheet therefore; for this case, please do the following
  • Create a worksheet named, Assumptions
  • Put all the inputs on this sheet, i.e. all of the Figure 1 values in your book should be put on this worksheet and CLEARLY labeled
  • Create a worksheet named, Pro Forma
  • This is your output
  • Use all the concepts you have learned in this class thus far, including:
  • Break all the inputs down to their lowest element
  • DO NOT “hard code” any values.
  • Use any other information you have learned in the class to complete an effective and well-designed spreadsheet.

You do not need to protect the cells in the worksheet.

complete the pro-forma statement before continuing.

Complete the Implementation Specifications section as follows:

Change the appropriate cells as noted in questions 1, 2, 4, and 5. For each of the scenarios you will copy the net income from the pro forma worksheet and paste the values (1st column 3rd row) into a new worksheet. You will use this worksheet to create a chart showing the different scenarios.

WHEN YOU HAVE FINISHED THIS WORKSHEET, PLEASE CHANGE YOUR VALUES BACK TO THE ORIGINAL ONES USED FOR THE INITIAL SPREADSHEET DESIGN.

Your new worksheet should look as follows with a chart underneath as shown below.


CSE 1113 Assignment 31