Excel Exam Fall 2015 | Complete Solution

Fall 2015 Excel Exam

Requirements

1.       Using the Inc Stmt worksheet, Create range names for the following cells: B14:D14, B18, B21:B24, B26:B27, B30, B33:B36,  B38:B39, B43:D46, and 50:B53 (Note: you will not be able to use Create from Selection for everything because some of the labels are repeated and the names would not be unique; abbreviations are fine, just make sure that the names are unique and sensible)

2.       Create formulas in cells B4:D12. Do not hard code any numbers.

a.       In the formulas for year 3 sales and variable costs, you will need to re-calculate the year 2 increases; you will not be able to simply copy the formula to column D (i.e. multiply times the percentage change twice)

3.       Use an IF statement to calculate the tax. If you can’t figure out the IF statement, just enter the number. If it is correct, you will only lose points for the IF statement. You are allowed to add data to the assumption area. If you add data to use in your calculation, name the cells. If NOI is less than $10,000, then it will be NOI * 4%, if it is over $10,000 but less than $50,000, you would take the amount over $10,000, multiply it by 6% then add $400 with the same patter for over $50,000

4.       Using the Scenario Manager, answer the following questions (all results should be on one, separate, worksheet)

a.       Santa says that direct materials are currently $10 per unit and will increase 4%, not 3% per year

b.      Fixed manufacturing costs are way overstated, They should start at $90,000 and increase by 8% per year

c.       Santa wants to know what would happen if they got better quality materials (starting cost, $15 per unit sold) and raised the price to $58 to start

d.      Output the Income after Tax for all three years for the three scenarios

1. Switch to the Orders worksheet. Create new spreadsheets for each scenario. You will create two spreadsheets for each scenario; one using grouping and subtotals, the other using pivot tables. For the grouping worksheets, start with the worksheet ordered by Customer Name. Within customer name, sort by date. Name the worksheets “scenario a group”, “scenario b group”, etc. or “scenario b pivot”, “scenario b pivot”, etc. Move sheets as needed to keep them in the correct order (scenario a group, scenario a pivot, scenario b group, scenario be pivot, etc.)
   1. For each customer, sum the sales. (Note: this is a large worksheet so it will not be instantaneous)
   2. For each customer, count the number of orders
   3. Count the number of products ordered for each product
   4. What is the average number of products per category?
   5. Create a new question and display the group and pivot worksheets