Cashback Offer from 10th to 25th February 2021. Get Flat 20% Cashback credited to your account for a minimum transaction of $80. Post Your Question Today!

Question DetailsNormal
$ 32.00

Excel Exam Fall 2015 | Complete Solution

Question posted by
Online Tutor Profile

Fall 2015 Excel Exam


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

Available Answer
$ 32.00

[Solved] Excel Exam Fall 2015 | Complete Solution

  • This solution is not purchased yet.
  • Submitted On 20 Dec, 2015 01:17:13
Answer posted by
Online Tutor Profile
Santa's Workshop Variable Income Statement Year 1 Year 2 Year 3 Sales 464000 522000 609,000 Variable Expenses: 224000 252000 2...
Buy now to view the complete solution
Other Similar Questions
User Profile

Excel Exam Fall 2015 | Complete Solution

Santa's Workshop Variable Income Statement Year 1 Year 2 Year 3 Sales 464000 522000 609,000 Variable Expenses: 224000 252000 291800 Variable COGS $192,000 $216,000 $251,400 Variable Selling & Admin $32,000 $...

The benefits of buying study notes from CourseMerit

Assurance Of Timely Delivery
We value your patience, and to ensure you always receive your homework help within the promised time, our dedicated team of tutors begins their work as soon as the request arrives.
Best Price In The Market
All the services that are available on our page cost only a nominal amount of money. In fact, the prices are lower than the industry standards. You can always expect value for money from us.
Uninterrupted 24/7 Support
Our customer support wing remains online 24x7 to provide you seamless assistance. Also, when you post a query or a request here, you can expect an immediate response from our side.

$ 629.35