MIS111-L, Case Analysis 1 Guidelines
Case Analysis Overview
Case Analysis 1 includes material from the weeks beginning Aug 25, Sep 1, 8.
Please base your answers on material discussed in the following units: Class Introduction, Excel Basics, Data entry & formatting, Charting.
Your answers to the questions will be entered in the Case Analysis Turnitin Template document (separate from this document, but also found on Blackboard).
Total points possible: 25
Business Communication Assessment Points
Since you are writing a business document, 20% (5 points) of your assignment grade will be based on your overall business communications skills. Therefore,your response should be well organized, written in standard English using complete sentences, appropriate grammar, punctuation, and formatting/presentation.
Assignment Overview
Excel allows business professionals to accomplish the key requirements of information management that we discuss in MIS 111, namely: organization, analysis, and communication.
Your task for the Case Analysis will be to fill in answers for each of the following questions. An expected answer size is also provided for each question.
Question 1 [4 points]:
Task 1(a): What is your (planned) major? (please pick one for the purposes of this question).
Task 1(b): Describe (goal: 100-125 words), using an example, how a professional in your major area (e.g., Accounting, Chemistry, MIS, Psychology) can use using Excel to organize and analyze data to produce useful information. In other words, use Excel to make the data more functional or meaningful.
Question 2 [4 points]:
In our class lecture, we discuss a variety of chart types such as Column, Pie, Line and Scatter. Suppose your supervisor at work, John Doe, asks you to distinguish between Scatter-charts and High-low-close charts.
Task 2(a): Explain to John Doe (in 75-100 words) the differences between the two chart types.
To augment the answer in 2(a), please provide John Doe with the following example.
Task 2(b): (in 50-75 words) Scenario (i.e., example you can think of) where a High-low-close chart would be more suitable to display/communicate data than a Scatter chart.
Question 3 [7 points] based on a simplified Case scenario:
A district is interested in tracking ticket sales for sporting events at different member schools. For simplicity, we assume they are interested in ten schools at this time. Please follow the steps below in answering this question.
To begin, save the CA1_Sales.xlsx file from Blackboard to your local computer. Open the file to perform the following tasks.
1. In cell B1, enter your 8-digit studentID (do not use your CatCard number).
2. In Cell B2, enter your full name.
3. In Cell C5, create a formula to calculate the number of Tickets sold for Riverside Public. Take the last 2-digits from your 8-digit studentID. Let us refer to these last 2-digits as XY. You will use this to compute the "Tickets Sold" column. For each school, calculate the number of tickets sold using the formula seen on the "Tickets Sold" column in the figure below. In your formula, replace XY with the last two digits of your student ID.
For example, if your student ID is “12345678”, you should use “78”. So the formula =2000 +XY becomes =2000+78 (i.e., you will end up with a value of 2078 for the number of tickets sold for the first school, Riverside Public).
4. In cells C6:C14 (C6 through C14), create formulas to calculate the number of tickets sold consistent with the figure below.
A / B / C / D4 / School / Price / Tickets Sold / Revenue
5 / Riverside Public / $10.00 / =2000+XY / To be calculated
6 / Valley Palo Verde / $11.50 / =500+XY / To be calculated
7 / Cholla Vista High / $14.50 / =1250+XY / To be calculated
8 / Rincon East Valley / $13.50 / =750+XY / To be calculated
9 / Mt. Lemmon Magnet / $11.50 / =1000+XY / To be calculated
10 / Benson Public / $14.50 / =1500+XY / To be calculated
11 / Colby Medvale / $14.00 / =2500+XY / To be calculated
12 / East-West Magnet / $12.50 / =1000+XY / To be calculated
13 / Arrowdale Public / $15.50 / =1250+XY / To be calculated
14 / Mequite Vista / $13.00 / =2000+XY / To be calculated
15 / Total Revenue / Use formula to calculate total
5. In cells D5:D14 (D5 through D14), enter formulas to calculate the revenue by multiplying the number of tickets sold by the ticket price (do not directly enter the result; you must use an Excel formula). An example to illustrate desired output of formula: if the price of tickets at Riverside Public is $10.00 and 2078 tickets were sold, the Revenue is $20,780.
6. In cell D15, create a formula that calculates the revenue across all schools. For example (simplified) if all 10 schools had a revenue figure of $5,000 (each), then the Total Revenue is: $50,000. Your formula should compute this total.
Task 3(a): To show us your work, please copy and paste your Excel table generated by performing steps in Question 3, into your Word Turnitin Template document in the space provided for Task 3(a). Make sure all parts of your answer are easily readable. The following screenshot shows a sample of what is expected.
Task 3(b): Please write down (copying and pasting is acceptable) the formula from cell D9
(i.e., Revenue from Mt. Lemmon Magnet) into your Word Turnitin Template document in the space provided for Task 3(b). Ensure your formula is correctly written and that it is a valid formula and NOT the dollar value of revenue for that school.
Task 3(c): Please write down (copying and pasting is acceptable) the formula from cell D15
(i.e., Total Revenue) into your Word Turnitin Template document in the space provided for Task 3(c). Ensure your formula is correctly written and that it is a valid formula (and not the dollar value of Total Revenue).
Question 4 [5 points]
Use your solution from Question 3 to perform tasks in Question 4.
Task 4(a): Describe (in 75-100 words) how a spreadsheet like Excel can help you analyze and communicate information about the distribution of your revenue from different schools (i.e., relative revenue from the 10 schools listed in the example in Question 3).
How can an Excel chart help in this process?
Task 4(b): In continuing your answer 4(a) for revenue distribution, copy and paste an Excel chart (i.e., a chart you created for revenue distribution). See the figure below for an example of what is expected.
Page 1 of 5