PMBA 8040

Assignment 1

Excel Exercises

1. Use the Sales Data for Pivot. [Alternately, find a dataset from your work that is appropriate for Pivot Table Analysis (at least 2 categorical variables, at least 1 numeric variable, at least 100 observations – then create pivot tables as appropriate to summarize the data).

a. Create a pivot table to analyze the Total Amount by State and by Product simultaneously.

b. Create another pivot table to analyze the Total Amount by Date and by Product simultaneously.

2. In a blank worksheet, enter 10 names of fictitious students. Create column headings for the following: Assignments 1 through 4, Quizzes 1 through 5, and Project. Quizzes are worth 10% each, Assignments 5% each, and the project is worth 30%. For each student, enter grades arbitrarily for each of the activities between 60 and 100 points (you are simulating the grades for 10 students on the various activities).

a) Compute the course average for each student. Use appropriate formula in Excel to do so.

b) Compute the letter grade for each student, assuming 90+ = A, 80+=B, 70+=C, 60+=D and under 60 = F. Again, make sure to use appropriate formula in Excel that will do so automatically for each student.

c) You wish to remove the lowest quiz grade and recompute the course averages, distributing the quiz weight equally among the 4 best quizzes rather than all 5. Show revised averages and letter grades in new columns.

3. A product sells for $100 per unit. Fixed Costs are $25, 000 and variable costs are $40 per unit.

a. Create a spreadsheet to compute the Breakeven point and show the profit numbers for a range of possible unit sales.

b. An alternative manufacturing method would change fixed costs to $40,000, but reduce variable costs to $10 per unit, with sales price remaining at $100. Do Crossover Analysis in Excel. Show a graph of the two profit lines.