Business Analytics: Step-by-Step Tutorial

By Narcyz Roztocki

Business Analytics refers to utilizing existing data to make business decisions. Often, however, the decision-makers lack the knowledge and skill to process the existing documents and retrieve information that is useful for their decision-making.

The objective of this tutorial is to improve basic skills on how to process existing data by providing a collection of simplified, but realistic, hands-on-examples.

In essence, this tutorial is a collection of business mini cases. It starts with relatively simple cases and then progresses step-by-step by adding complexity to each of the following business cases. Its intent is to encourage effective and efficient use of application software to help the decisions-makers.

There are three major changes to the second edition. First, several additional mini case studies are added. Second, the case studies are rearranged to allow a more systematic progress. Third, in addition to a solution key, detailed solutions to 35 exercises by using MS Excel are added.

• Language: English
• Publisher: Lulu.com
• Release date: Nov 13, 2016
• ISBN: 9781365510458

Book preview

Business Analytics: Step-by-Step Tutorial

Business Analytics Step-by-Step Tutorial

By Narcyz Roztocki

Second Edition

Copyright © 2016 by Narcyz Roztocki

All rights reserved. This book or any portion thereof may not be reproduced or used in any manner whatsoever without the express written permission of the publisher except for the use of brief quotations in a book review or scholarly journal.

First Printing: 2016

ISBN 978-1-365-51045-8

Narcyz Roztocki

www.small-big-data.com

Preface

Business Analytics refers to utilizing existing data to make business decisions. Often, however, the decision-makers lack the knowledge and skill to process the existing documents and retrieve information that is useful for their decision-making.

The objective of this tutorial is to improve basic skills on how to process existing data by providing a collection of simplified, but realistic, hands-on-examples.  

In essence, this tutorial is a collection of business mini cases. It starts with relatively simple cases and then progresses step-by-step by adding complexity to each of the following business cases. Its intent is to encourage effective and efficient use of application software to help the decisions-makers. All the cases can be solved using various software packages. 

There are three major changes to the second edition. First, several additional mini case studies are added. Second, the case studies are rearranged to allow a more systematic progress. Third, in addition to a solution key, detailed solutions to 35 exercises by using MS Excel are added.

The intention of this tutorial is to introduce fundamental concepts of business analytics. Although this tutorial shows how to use MS Excel to solve the included problems, all mini case studies could be solved equally well by using other software packages such as R or LibreOffice Calc. Moreover, the intention of this tutorial is not to serve as a substitute for a MS Excel tutorial. In fact, a decent knowledge of MS Excel is required. The following concepts of MS Excel, among others, are supportive: importing files, absolute/relative cell references, handling of data (sorting, filters, dealing with duplicates), financial and statistical functions.

Exercise 1

Carlos Gonzales is a manager at a bank. Currently, Carlos is attending a Business Analytics evening class. He has been asked to create a spreadsheet to calculate a final grade for a random student in the class. The final grade will be based on the following:

Exam 1: 25%

Exam 2:  25%

Final Exam: 25%

Homework:  20%

Class Participation: 5%

Carlos is expected to design a spreadsheet for the options specified above, but the model should be able to be (re)used for future calculations.  If, for example, the student specifies a new data of variables, your spreadsheet should be able to accommodate those changes and automatically provide a recalculated number. (It should assume that the model would be able to accommodate changes in all numerical parameters specified above.) In addition, it is expected to avoid any redundancy in the model. 

Exam 1: 50 points 

Exam 2: 80 points

Final Exam: 100 points

Homework: 100 points  

Class participation: 70 points

1. What is the student’s final score?

(a) 80

(b) 81

(c) 82

(d) 83

(e) None of the above

2. Assuming the following grades: Exam 1: 100 points, Exam 2: 100 points, Final Exam: 100 points, Homework: 0 points, Class participation: 100 points, what is the student’s final score?

(a) 60

(b) 70

(c) 75

(d) 80

(e) None of the above

Exercise 2

Carlos Gonzales is a manager at a bank. Currently, Carlos is attending a Business Analytics evening class taught by Professor John Koryto. He has been asked to create a spreadsheet to calculate a final grade for a random student in the class. The final grade in Professor’s Koryto class will be based on the following:

Exam 1: 25%

Exam 2:  25%

Final Exam: 25%

Homework:  20%

Class Participation: 5%

There will also be an Extra Credit assignment accounting for a maximum of 2 percent.

The following letter grades will be assigned based on absolute percentage score:

94% and above A

90%-93.99% A-

87%-89.99% B+

83%-86.99% B

80%-82.99% B-

77%-79.99% C+

73%-76.99% C

70%-72.99% C-

67%-69.99% D+

60%-66.99% D

55.99 and belowF

1. Assuming the following grades: Exam 1: 90 points, Exam 2: 90 points, Final Exam: 85 points, Homework: 85 points, Class participation: 100 points, Extra Credit: 80 points, what is the student’s final letter grade?

(a) A

(b) A-

(c) B+

(d) B

(e) None of the above

Exercise 3

Imagine you are the owner of a small construction company, which specializes in masonry and brick laying. You have been asked to create a spreadsheet to help you create a bid for a private customer who would like a wall to enclose part of their estate. You offer them two options – lava rock or brick.

Currently, you are working on the following bid:

Three workers will work three eight-hour days to build either type of wall. The wall will be 30 feet long, 5 feet tall, and 2 foot thick. Wages will be $10 per hour per person. You will have to add 25% to wages to cover fringe benefits. Lava rock will cost $4 per cubic foot. Brick will cost $2.50 per cubic foot. Your bid must add a profit margin of 40% to your total cost.

You are expected to design a spreadsheet for the two options specified above, but your model should be able to be (re)used for futures bids.  If, for example, the customer specifies a new size of wall, your spreadsheet should be able to accommodate those changes and automatically provide a recalculated bid. (You should assume that your model will be able to accommodate changes in all numerical parameters specified above.) In addition, you are expected to avoid any redundancy in your model.  For example, all parameters, which are applicable for both options, should be used only once.

Please Note:

A bid is a price quote, estimate, or offer. You are to create a spreadsheet model for each option (lava rock or brick) indicating a bid, which covers all costs and takes into account an overall 40 % profit margin.

1. The bid for the lava rock wall is

(a) $1,684.80

(b) $2,940.00

(c) $1,720.00

(d) $1,372.80

(e) None