Reference no: EM132142271

EXERCISE 1: COPYING, FORMATTING, AND CALCULATING SUMS AND AVERAGES

Let's assume that Groth Donut Company has three stores, only one of which is shown at the top of the sheet titled "p = r-­-e". The revenue and expenses for one month are shown for you at the top of the homework tab.
Copying Rows:

- First, use a function to find the profit. (Hint: Profit = Revenue -­- Expenses).
- Then, copy lines 1 and 2 and paste them in lines 5 and 6
- Select line 6 by clicking in the extreme left hand cell (the "6")
- copy it to line 7, and again to line 8
- In cell B7, type in 160000. In cell C7, type in 110000.
- In cell B8, type in 90000. In cell C8, type in 65000. You should have automatically gotten a new number in your profit column for these two rows.
- Immediately to the right of the "Expenses" cell, type in "Store Location" and label the stores as Queens, Brooklyn, and Bronx. It should look like this:

Next, we are interested in the sum of all the profits, revenues, and expenses, as well as the average of all the profits, revenues, and expenses. Type in Sums and Averages so that it looks like this:

Calculating Sums:

- To calculate "Sums", the sum of the profits, in cell A10 type =SUM and select the data you are interested in. Your formula should look something like this: =SUM(A6:A8).
- Drag to fill in the information for the sum of revenues in cell B10, and the sum of expenses in C10.
Calculating Averages:

- To calculate the "Averages", the averages of the profits, in cell A11 type =AVERAGE and select the date you are interested in. Your formula should look like this: =AVERAGE(A6:A8).
- Drag to fill in the information for the sum of revenues in cell B10, and the sum of expenses in C10.

Questions to answer in your spreadsheet:
1. Which store has the highest profit?
2. Groth Donut Company wants to downsize, and close one of their stores. Which one should they close?
3. There was a plumbing problem in the Bronx store, and a leak caused 2,500 dollars in damages (a new expense that month). What is the Bronx store's new profit amount?

EXERCISE 2: CREATING AN INCOME STATEMENT
Return for a minute to the very first exercise-the one where you created the equation "PROFIT = REVENUE - EXPENSES." That equation forms the basis for one of the most widely-­-used financial statements in accounting called the Income Statement. An income statement shows the details of a company's financial performance. In our case, we are looking at financial performance for one month of the year.
The Income Statement consists of three parts:

- Revenue - Cost-­-of-­-Goods Sold (COGS) = Gross Profit Margin
- Gross Profit Margin - Operating Expenses = Net Income Before Taxes
- Net Income Before Taxes - Taxes = Net Income (The Bottom Line)
Go to the "Bus 1000 Excel Spreadsheet TEMPLATE" and click on the second sheet, which is labelled, "Income Statement."
- In cell A1, type "Income Statement". Change the font size to 18, and make the text bold.
- Change the column width in column A to "30." You can do this by right-­-clicking "A" and selecting "Column Width". Change the column width for columns B, C, and D to 15.
For the next steps, use the image below as your reference:

- Going down column A, type the words: "Revenue" in cell A3, "Cost of Goods Sold" in A5 etc. as shown on the sample spreadsheet above.

- Be sure to skip rows 2, 4, 7, and 14 as shown. Bold the headings in rows 3, 6, 13, 15, and 16 as shown above
- Next, type in the numbers you see above in column B which have already been provided for you (e.g. Cost of Goods Sold is $30,000). In order to get the dollar symbol, you will have to change the number format.
Now it's time to calculate the Revenue, Gross Profit, Operating Expenses, Net Income Before Taxes, Taxes, and Net Income.
- Revenue: Units x Price per unit
- Gross Profit: Revenue -­- Cost (of goods sold)
- Operating Expenses: The sum of advertising, salaries, rent, and utilities
- Net Income Before Taxes: Gross Profit -­- operating expenses
- Taxes: Net Income Before Taxes x cell C16
- Net Income: Net Income Before Taxes -­- Taxes
Inserting Rows:

For one last complication, insert an additional row in the Income Statement between "Salaries" and "Rent." Do this by right-­-clicking cell A11 and selecting "Insert". Call this cell "Transportation Costs" and value it at
$4,000.

Now, using the income statement answer the following questions. DO NOT revert back to the original figures after each question! Keep every change you make and move on to the next question. Write your answers clearly in the spreadsheet.
What would the company's net income (bottom line) be if:

1. Taxes were increased to 25%?

2. The company pressured suppliers for lower prices (COGS) from 30,000 to 26,000?

3. An advertising campaign was created that raised the amount spent on advertising from $5,000 to $8,000 but that campaign resulted in increased sales from 20,000 to 30,000 units?

4. The company moved and restructured-moving to South Carolina where rent is one third of what it currently is?

5. Offered an incentive program to eliminate a number of highly-­-paid middle managers, which would lower salaries from 20,000 to 13,000?

Lastly, you can use an Income Statement to calculate widely-­-used financial ratios. One of the most popular is Basic Earnings Per Share, which is Net Income divided by the number of shares outstanding.

6. Assume that the company has 14,000 shares outstanding. Using the formula Basic Earnings Per Share = Net Income / Number of Share Outstanding calculate EPS. What is the EPS?

EXERCISE 3: USING "LOOKUP" AND "COUNTIF" FUNCTIONS TO RANK EMPLOYEE PERFORMANCE

Managers often like to rank employees performance. One scheme called "20-­-70-­-10," or more disparagingly as "Rank and Yank," categorizes the top 20 percent of employees as "Best," the middle 70 percent of employees "Near Best," and the bottom 10 percent as "Below Best."
Go to the "Bus 1000 Excel Student Spreadsheets" and click on sheet labelled "Lookup". There you will find the performance ratings-on a ten point scale-for 20 employees. Each employee has 3 performance ratings.
- In the 4th column, calculate the average performance rating for each employee (to one decimal place.)
- Then, in the 5th column, use the Lookup formula below to classify the employees.
- =LOOKUP(e2,{0,4,9,10},{"Below Best","Near Best","Best"}
The Lookup formula is structured as follows:

- e2 is the cell for which the calculation should be made.
- 0 to 4 is the classification of below best
- 4 to 9 is the classification of near best, and
- 9 to 10 is the classification of best
- The words "Below Best", "Near Best", "Best" are placed for each of the three classifications.
Using the results, do the following:
1. List the names of the "Best" employees
2. List the names of the "Below Best" employees
Next, redefine the LOOKUP function to classify employees as "Above Average" and "Below Average" based on whether they scored above or below an average of 5 in their three performance scores. Place these results in cells b23 and b24.
3. How many people scored "Above Average?"
4. How many people scored "Below Average?"
To answer these questions use the COUNTIF function. Its form is COUNTIF (g2:g21, "Above Average") where g2:g21 is the range.

Lastly, you will notice that many more people scored "Above Average" than "Below Average" using 5 as the cut point. Redefine the LOOKUP formula so that a roughly equal number of employees are classified as "Above Average" as "Below Average," for example 11 above average and 9 below average.
5. What is the new LOOKUP formula? Copy and paste your answer here:

EXERCISE 4: BREAKEVEN ANALYSIS
Breakeven Analysis allows you to assess how profitable a project will be based on how much you sell each unit for and how many units you sell.
In the following example, assume you own a small company that has developed a new exercise device-called "The Strider"-that tells someone how far they have walked, how many calories they have burned and how many paces per mile they have tallied. Assume the following facts.
- The machine that makes the device was purchased for $11,500.
- The microprocessors, the plastic case materials and the labor needed to assemble it costs $15 for each device.
You have decided to sell the product directly to the consumer through a website promoted through ads.

- The price you intend to charge each buyer for the "Strider" is $42.
Before you market the product, you decide to create a spreadsheet to estimate the revenues, costs, profits and "Breakeven Point." Go to the "Bus 1000 Excel Student Spreadsheets" and click on the sheet labelled "Breakeven Analysis." Enter the following information. When doing calculations, make sure that you type them in the appropriate format, e.g. "=B5-­-B9" to calculate the profit.
- In A3 type in "Units Sold", in B3 type 10000.
- In A4 type "Per Unit Sales Price", in B4 type $42.
- In A5 type "Revenue". Calculate this amount in cell B5 (units x price per unit).
- In A7 type "Fixed Cost", in B7 type $11,500.
- In A8 type "Variable Cost", in B8 type $15.
- In A9 type "Total Cost", in B9 calculate the total cost: Fixed costs + (variable cost per unit x total number of units sold)
- In A11, type "Profit", in B11 calculate the profit (revenue - total cost).
- In A13, type "Breakeven Point". In A14, calculate the BEP: Fixed cost/(Price per unit - variable cost per unit).
You should set up the spreadsheet with the appropriate formulas so that you can change the price, cost and sales volume quickly.
Answer the following questions:
1. What are your revenues, total expenses and profit if you sell 10,000 Striders?
2. What are your revenues, expenses and profit if you sell 15,000 Striders by lowering the price to $35 each?

3. How many Striders do you need to sell $42 to make $500,000 in profits? (You can find the answer by entering different numbers in the appropriate cell until you get the desired result).
4. How many Striders do you need to sell at $42 to break even?

EXERCISE 5: CREATING A "PIVOT TABLE"

Pivot Tables allow you to create reports on a new spreadsheet from data stored on a large spreadsheet.

For this exercise you do not need to type in the data. It is provided to you. Open the "Bus 1000 Excel Student Spreadsheets." Look on the bottom line and click where it says "Pivot Table Data." There you will find a spreadsheet that has two additional columns-"Quarter," which lists sales (revenues) for each of the four quarters in a year, and "Products," which lists the two key products sold by the stores-Apple's iPhone and the Samsung phone.
Manipulating this data will allow you to answer the following questions:
1. Which store-the one in Queens, Brooklyn or the Bronx-is the "most" profitable? Which one is the "least" profitable?
2. Which product-the Apple iPhone or the Samsung phone-is more profitable?
3. Which quarter has the highest sales revenue?
To answer these questions:

- Click on any cell (e.g. a3)
- Click on "Insert," then click "PivotTable" on the far left and the following box appears.

- Simply click "OK," choosing the defaults. A new worksheet will appear.
- On the right hand side of the spreadsheet a box titled "PivotTable Field List" appears.

Let's start by answering the first question: Which store-the one in Queens, Brooklyn or the Bronx-is the "most" profitable?
- Click on "Profits"
- Then, click on 3 Stores."
- Note that "Sum of Profit =" appears under Values.
- "3 Stores" appears under Row Labels.
- Also, a table appears in the upper left hand corner.

Now you can answer the questions:
1. Which store is the most profitable?
2. Which quarter has the highest sales revenue?

As an added step, try clicking on the "2 products" box in the PivotTable Field List at right. This is, you will now have a total of three boxes selected-Profit, 3 Stores and 2 Products.

EXERCISE 6: CREATING A CLUSTERED COLUMN AND LINE CHART

Charts are visual representations of data. Charts are useful because they often make it easier to understand the data. Users can pick out patterns and trends that are illustrated in the chart that are otherwise difficult to see.
For this exercise you will analyze the changes in stock price for five different airlines. The data is provided for you. Go to the "Bus 1000 Excel Student Spreadsheets" and click on sheet labelled "Line Chart."
Open the file and find the spreadsheet with columns for each airline and rows for each month. The data is the percent change in stock price over the course of the month.
In order to compare the stock performance of the five airlines we will create a chart. Follow the instructions to create a chart.

- Select the range B1:F6, or make sure that a cell is selected within your data (any cell).
- On the Insert tab, in the Charts group, choose Column, and select Clustered Column

Accurate labels are necessary so that the user can interpret the data.

- We want the different bar colors to represent the months, which will be labelled in the legend below the graph. We want the bars to be clustered according to airline. If your graph is reversed, right click on the graph and click Select Data. Click on "Switch Row/Column"
- On the Chart Tools Layout tab, choose Chart Title, and select Above Chart. Label the chart "Stock Price Change". If your chart is already titled "Chart Title", double-­-click this text on the graph and change it to "Stock Price Change".
- On the Chart Tools Layout tab, choose Axis Titles, select Primary Horizontal Axis Title and select Title Below Axis. Label the horizontal axis "Airline". On a Mac or different Excel versions, this option will be found under "Chart Design" ?? "Add Chart Element" ?? "Axis Titles".
- In the same menu, select Primary Vertical Axis Title, and label the vertical axis "Percent Change".

You are now done with your clustered column chart, and will make a line chart beside it. Do not delete your clustered column chart.

- Select the range B1:F6 again, or click any cell within your data.
- On the Insert tab, in the Charts group, choose Line, selected Line With Markers.
- This time, we want each line to represent a different airline, and each point to represent a month. Therefore, the legend should show which airline is represented by each color. Switch your rows and columns only if necessary.
- On the Chart Tools Layout tab, choose Chart Title, and select Above Chart. Label the chart "Stock Price Change". If your chart is already titled "Chart Title", double-­-click this text on the graph and change it to "Stock Price Change".

- On the Chart Tools Layout tab, choose Axis Titles, select Primary Horizontal Axis Title and select Title Below Axis. Label the horizontal axis "Airline". On a Mac or different Excel versions, this option will be found under "Chart Design" ?? "Add Chart Element" ?? "Axis Titles".
- In the same menu, select Primary Vertical Axis Title, and label the vertical axis "Percent Change".
- You should now have two charts side by side - a bar chart, and a line chart - showing the same data set.

Use the results to answer the following questions:
1. Which airline did not experience a decline in stock price?
2. What airline had the largest stock price decrease, and in which month?
3. Which airline showed the greatest increase in stock prices, and in which month?
4. In which month did Ryan Air have a negative percent change in stock price?

EXERCISE 7: CREATING CLUSTERED BAR CHART

For this exercise you will analyze the changes in yearly revenue for five different. The data is provided for you. Go to the "Bus 1011 Excel Student Spreadsheets" and click on sheet labelled "Bar Chart." There are columns for each year and rows for each company. The data is the yearly revenue in millions of dollars.

Create a Clustered Bar Chart:

- Select the range A1:E6, or click any cell within your data.
- On the Insert tab, in the Charts group, select Bar, select 3-­-D Clustered Bar.

Adjust the Style:

- Just as we did in exercise 7, we want to change the title. Label the chart "Annual Revenue".
- We want the different bar colors to represent the company, which will be labelled in the legend below the graph. We want the clusters of bars to represent the year. If your graph is reversed, right click on the graph and click Select Data. Click on "Switch Row/Column".
- Change the Vertical Axis Title to "Year" and the Horizontal Axis Title to "Revenue (in millions)". Check the instructions in Exercise 7 above if you do not remember how to do this.
- We now want to rotate the bars. Right click on the chart and select "3-­-D Rotation...". Change the x rotation to 90 degrees and the y rotation to 45 degrees. (This option may be in the Chart Tools Layout tab). .
- Change the chart title to 20 pt font and the axis titles to 15 pt font. Change the font for the title, axes, and legend to Arial.

Now answer the following questions:

1. Which company had the lowest revenue in 2014?
2. Between which two years did Target experience its largest increase in revenue?
3. Which company has experienced a decline in revenue each year?
4. At its current rate of revenue increase, what do you think Costco's 2018 revenue will be, approximately?

Attachment:- excel ebook.rar