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

BAOS 300 Practice: Using Spreadsheets

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.

Download the practice activity’s solutions at the bottom of this page.

Task 1

Create a spreadsheet with the sample data shown below:

Region Store Number Date Item Number Sale Price Quantity
Northeast 200 1/2/2015 10 21.95 10
Southeast 300 1/2/2015 10 22.95 14
Southwest 400 1/3/2015 10 20.95 15
Northwest 500 1/4/2015 10 24.95 16
Midwest 600 1/5/2015 10 26.95 20
Northeast 200 3/2/2015 12 124.95 4
Southeast 300 3/3/2015 12 129 3
Southwest 400 3/4/2015 12 140 6
Northwest 500 3/5/2015 12 160 8
Midwest 600 3/6/2015 12 100 2

Task 2

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 Number Total 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.
  16. Add a line graph. Right-click and choose “Add Data Labels” or “Add Data Callouts.”
  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 Value Commission
$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:

Formula Description Result
=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:
Category Food Sales
Vegetables Tomatoes $2,300.00
Vegetables Celery $5,500.00
Fruits Oranges $800.00
Butter $400.00
Vegetables Carrots $4,200.00
Fruits Apples $1,200.00

Experiment with the following formula:

Formula Description Result
=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.

Looking for a similar assignment. Place your order for a similar paper now and have excellent work written by our team of professionals to ensure you acquire the best grades.

If you find it difficult to balance your work and your studies because you are overwhelmed by other responsibilities, you have come to the right place . With the help of our dependable and simple-to-use essay and research writing service, students are able to focus on work or non-academic-related duties that they are required to carry out and complete all their school work on time .

We assist students who are struggling with their academic endeavors by allowing them to obtain essay assistance from  professional essay writers. We are never late for a deadline. When you ask our writer to write your paper, the assignment can be completed in as little as 12 hours or as long as 2 weeks. The order’s deadline is entirely up to you. 

Place an order of your assignment if you are in urgent need of assistance because your essay is due in a few hours/days or you do not know how to complete your professor’s academic assignment. We are here to assist you. You are guaranteed of high grades through our services. 

How to Place an Order 

When you want Premiered Tutorials to do your homework for you, you 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. 

Premiered Tutorials has competent writers to handle any of your assignments. 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 Premiered Tutorials?

If you need professional online homework help at a cheap price from professional and thoroughly vetted online homework helpers, reach out to the best homework help website, Premiered Tutorials.

Whether you are in high school, undergraduate, masters or PhD level, we have the best academic writers to assist you with your homework. We thoroughly vet all our online homework helpers to ensure they are competent to handle our clients’ tasks. Some of the requirements we have is ensuring that they are English Native speakers, have all attended colleges and had high grades in their undergraduate, Masters and PhD classes and have worked as academic writers and delivered high results.

We are a reliable and best essay writing website in the market. Our assignment help services or rather what our clients ask us to do for them include take my online class for me, order an essay cheap, pay someone to write my essay, help with my assignments, history homework help, college homework help, essay writing help online, answers for your homework, pay someone to do my homework for me, homework answers math, pay someone to take my proctored exams and nursing homework assignment help online. As the best website for homework answers, our homework doers also offer cheap essay writing services, online assignment help for all classes

  • Law/ Criminal Law
  • Nursing
  • Information Technology
  • Psychology
  • Business
  • Statistics
  • Sociology
  • History
  • Communication
  • Culture and Society
  • Biology
  • Astronomy
  • Engineering

The courses indicated above are just but a small group of the types of classes Premiered Tutorials handles.

We have competent homework writers that can handle all academic disciplines regardless of the level of education of our clients.

  When we do your homework for you, we write it from scratch to ensure you receive a unique paper. It also ensures that every homework we do for you is free from plagiarism and will help the client to get the highest grades in his or her classes. Any client who manages to consistently use our homework help services is guaranteed of always having the best grades and being one of the top students in the class because we always deliver the best quality for any homework we do for you.

When we complete working on your homework, we deliver the paper to you on time as we are well aware that the work we handle, is time sensitive. Timely deliveries of assignments help our clients get the highest grades because missed deadlines on assignments can result to deductions of marks for late submissions. Therefore, you will always be assured of quality online homework help through Premiered Tutorials whenever you ask us to do your homework for you.

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

Statistics about Us

130 New Projects
235 Projects in Progress
315 Inquiries
420 Repeat clients

© 2021 Premiered Tutorials
All rights reserved. We provide online custom written papers, such as term papers, research papers, thesis papers, essays, dissertations and other custom writing services.

All papers inclusive of research material are strictly intended to be used for research and study purposes only. Premiered Tutorials does not support or condone plagiarism in any form. These custom papers should be used with proper reference.

Place an Order
error: Content is protected !!