Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask Financial Accounting Expert

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

Financial Accounting, Accounting

  • Category:- Financial Accounting
  • Reference No.:- M93129736
  • Price:- $85

Priced at Now at $85, Verified Solution

Have any Question? 


Related Questions in Financial Accounting

Comprehensive problem - lou barlow a divisional manager for

Comprehensive Problem - Lou Barlow, a divisional manager for Sage Company, has an opportunity to manufacture and sell one of two new products for a five-year period. His annual pay raises are determined by his division's ...

Exercise 1 copying formatting and calculating sums and

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 f ...

In its first year of operations cullumber company

In its first year of operations, Cullumber Company recognized $31,800 in service revenue, $6,600 of which was on account and still outstanding at year-end. The remaining $25,200 was received in cash from customers. The c ...

What has been strides position on dividend payouts in the

What has been Strides' position on dividend payouts in the past (pattern, relationship with earnings, etc.)? What factors affected its dividend policy?

The ipl just signed sachin to a contract consisting of

The IPL just signed Sachin to a contract consisting of eight, end-of-year payments worth $9 million each, with the first payment precisely one year from today. On the other hand, Dhoni recent deal calls for six annual pa ...

Corporate accounting assignment -assessment task -select

Corporate Accounting Assignment - Assessment task - Select two public limited companies listed on the Australian Securities Exchange (ASX) that are in the same industry. Go to the website of your selected companies. Then ...

Asset retirement obligation changes in estimate versus

Asset Retirement Obligation, Changes in Estimate versus Errors, Writing an Issues Memo Facts: Mega¬Corp's corporate headquarters, built in 1970, has asbestos in its insulation. The Company's financial statements reflect ...

Advanced financial accounting assignment -assessment task

Advanced Financial Accounting Assignment - Assessment Task Part A - In an article entitled 'Unwieldy rules useless for investors' that appeared in the Australian Financial Review on 6 February 2012 (by Agnes King), the f ...

At the start of 2013 shasta corporation has 15000

At the start of 2013, Shasta Corporation has 15,000 outstanding shares of preferred stock, each with a $60 par value and a cumulative 7% annual dividend. The company also has 28,000 shares of common stock outstanding wit ...

Excel quiz1 start excel 2016 and download and open the file

Excel Quiz 1. Start Excel 2016 and download and open the file Excel Quiz1F18. 2. Save the workbook as FirstName_LastName_Excel_Quiz1 where FirstName is your own First Name and LastName is your Surname (for example Roger_ ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As