Get a unique, high-quality and non-plagiarized paper from us today at the most affordable price
Email us : premieredtutorials@gmail.com
PREMIERED TUTORIALS

## Directions

Work through the following practice activities using the Learning Resources when you get stuck. You do not have to submit the practice activities for grading.

Create a spreadsheet with the sample data shown below:

RegionStore NumberDateItem NumberSale PriceQuantity
Northeast2001/2/20151021.9510
Southeast3001/2/20151022.9514
Southwest4001/3/20151020.9515
Northwest5001/4/20151024.9516
Midwest6001/5/20151026.9520
Northeast2003/2/201512124.954
Southeast3003/3/2015121293
Southwest4003/4/2015121406
Northwest5003/5/2015121608
Midwest6003/6/2015121002

Experiment with the following formula:

1. COUNT(A2:B11) counts the numbers in the range (ignores blank/empty cells).
2. =COUNTA(E2:G11) counts all character strings in a range (ignores blank/empty cells).
3. =TODAY() inserts today’s date.
4. =AVERAGE(E2:E11) adds the numbers in the range, divides them by the total number of values, then provides the average.
5. =MAX(E2:E11) shows the highest value in the range.
6. =MIN(E2:E11) shows the lowest value in the range.
7. =DAYS(C11,C2) calculates the number of days between the two dates.
8. =NETWORKDAYS(C2,C11) calculates the number of workdays (assuming a five-day work week) between the two dates.
9. Add spaces to the cell A2 in front and at the end; =TRIM(A2) gets rid of these extra spaces.
10. =CONCATENATE(TRIM(B2), “-“, TRIM(A2)) combines/merges different values into a single value and fills it down to see Store-Region.
11. Add a Total Sale column as column G in the spreadsheet. Compute G2 using the formula: =E2*F2.
12. Compute the total sales by item number. Create cells as shown below:
Item NumberTotal Sale
10\$1,793.25
12\$3,206.80

Use the formulas =SUMIF(D2:D11, “10”, G2:G11)     and     =SUMIF(D2:D11, “12”, G2:G11).

1.  Compute total sales by each region. Use the formula =SUMIF(A2:A11, “Northeast”, G2:G11).
2.  Compute total sales by each store. Create your own SUMIF formula.
3.  IF Statement: Add column “Profit (200 at 10%, others at 15%)” as column H. Compute profit as follows: For store 200, profit is at 10% of the total sale; for other stores, profit is at 15% of the total sale.
Formula to use in H2:  =IF(B2=200, G2*10%, G2*15%).
4.  Nested IF Statement: Add column “Profit (200 at 10%, 300 at 15%, others at 20%)” as column I. Compute profit as follows: For store 200, profit is at 10% of the total sale; for store 300, profit is at 15%; for other stores, profit is at 20% of the total sale.
Formula to use in I2: = =IF(B2=200, G2*10%, IF(B2=300, G2*15%, G2*20%)).
5. Add more columns and compute profits in a different way: If the sale price is less than \$25, profit is at 10% of the total sale; if the sale price is between \$25 and less than \$125, profit is at 15% of the total sale; for sale prices at or above \$125, profit is at 20% of the total sale.
6. Rename Sheet1 as “Sales Data.”
7.  Add a new worksheet and name it “Sales Charts.”
8. Add “Region Total Sales” in A1 and A2 of the Sales Data worksheet.
9. Start with using the values in a different worksheet. Work to reference cells from another worksheet. To do this, bring data from Sales Data to Sales Charts. In cell A2 of Sales Charts, type the following formula:    =’Sales Data’!F21.
(Note: In your spreadsheet, the cell may be something other than F21).
10. Drag and drop the formula from A2 to other cells so that you get all the regions and their total sales from “Sales Data” to “Sales Charts.”
11. Add a bar chart with Region and Total Sale. Change title to “Total Sales by Region.” Experiment with different fonts and colors.
12. Add a pie chart on the Same Data; right-click and choose “Add Data Labels.”
13. Select the pie chart, right-click, and choose “Format Data Labels.” Choose Percentage in addition to Values.
14. Add a doughnut chart on the Same Data; right-click and choose “Add Data Labels” or “Add Data Callouts.”
15. Select the doughnut chart, right-click, and choose “Format Data Labels.” Choose Value in addition to Percentage.
17. Select the line graph. Right-click and choose “Add Trend Line.”
18. Repeat Exercises 21 through 30 for “Total Sales by Store” and “Total Sales by Item” (about 10 minutes for each).
19. SUMIF another example: Use a different worksheet and enter the following data:
Property ValueCommission
\$100,000.00\$7,000.00
\$200,000.00\$14,000.00
\$300,000.00\$21,000.00
\$400,000.00\$28,000.00

Experiment with the following formula:

=SUMIF(A2:A5,”>160000″,B2:B5)Sum of the commissions for property values over 160,000\$ 63,000.00
=SUMIF(A2:A5,”>160000″)Sum of the property values over 160,000\$ 900,000.00
=SUMIF(A2:A5,300000,B2:B5)Sum of the commissions for property values equal to 300,000\$ 21,000.00
=SUMIF(A2:A5,”>” & C2,B2:B5)Sum of the commissions for property values greater than the value in C2\$ 49,000.00

1. SUMIF another example: Use a different worksheet and enter the following data:
CategoryFoodSales
VegetablesTomatoes\$2,300.00
VegetablesCelery\$5,500.00
FruitsOranges\$800.00
Butter\$400.00
VegetablesCarrots\$4,200.00
FruitsApples\$1,200.00

Experiment with the following formula:

=SUMIF(A2:A7,”Fruits”,C2:C7)Sum of the sales of all foods in the Fruits category\$ 2,000.00
=SUMIF(A2:A7,”Vegetables”,C2:C7)Sum of the sales of all foods in the Vegetables category\$ 12,000.00
=SUMIF(B2:B7,”*es”,C2:C7)Sum of the sales of all foods that end in es (Tomatoes, Oranges, and Apples)\$ 4,300.00
=SUMIF(A2:A7,””,C2:C7)Sum of the sales of all foods that do not have a category specified\$ 400.00
1. On separate worksheets, construct pivot tables for total sales by item and for total sales by region.

#### How to Place an Order

Send the assignment details such as the instructions, due date/deadline, number of pages and college level to the customer support agent online on live chat,  fill in the assignment details at place an order or send the information to our email address premieredtutorials@gmail.com and a customer support agent will respond to you immediately.

Once you place your order, we choose for you the best and competent writer for your assignment based on each writer’s competence in handling a subject.

When the homework is completed, we have a quality assurance team that proofreads the assignment to ensure it meets the required rubric instructions from your professor.

After thorough review of your assignment, we send the paper to the client. In case you need any changes at this point, you can let us know so that we can handle it for you at no extra charge.

#### Why we should write your Paper

1. Money Return guarantee
2. 0% Plagiarism Rate
3. Guaranteed Privacy
4. Written from scratch by highly qualified writers
5. Communication at Any Time (24/7)
6. Flexible Pricing and Great Discount Programs
7. Timely Deliveries
8. Free Amendments
##### Looking for a similar assignment and in urgent need for help? Place your order and have excellent work written by our team of professionals to ensure you acquire the best grades.

We are here to assist you.