• October 30th, 2015

Computer

Assignment Requirements

part 1

 

Amanda has another set of orders for you to enter on the Customer Orders worksheet. In

addition to calculating the charge for creating the DVDs, Amanda also wants to include

the cost of shipping in the total charged to each customer.

  1. Open a blank workbook, and then save the workbook as Order Report.
  2. Rename the Sheet1 worksheet as Documentation, and then enter the following data into the specified cells:

Cell A1: RipCity Digital

Cell A3: Author Cell B3: your name

Cell A4: Date Cell B4: =today()

Cell A5: Purpose Cell B5: To track customer orders for RipCity Digital

  1. Insert a new worksheet (plus sign to right of previous worksheet), and rename the new Sheet2 worksheet as Customer Orders.
  2. On the Customer Orders worksheet, enter the following data into the specified cells:

Cell A1: RipCity Digital

Cell A3: Customer Orders Report

Cell A4: March 27 to April 17, 2015

  1. In cells A5 through H10, enter the column titles and data from Figure_One. Wrap text accordingly.

Figure_One Date LastName FirstName Address Phone DVDs Price per DVDFigure_One Date LastName FirstName Address Phone DVDs Price per DVD Shipping Charge

3/27/2015

Flemming

Doris

25 Lee St. Bedford, VA 24523

(540) 555-5681

7

$18.29

$7.49

4/4/2015

Ortiz

Thomas

28 Ridge Ln. Newfane, VT 05345

(802) 555-7710

13

$16.55

$9.89

4/8/2015

Dexter

Kay

150 Main St. Greenbelt, MD 20770

(301) 555-8823

25

$15.79

$7.23

4/9/2015

Sisk

Norman

250 East Maple Ln. Cranston, RI 02910

(401) 555-3350

15

$16.55

$10.55

4/17/2015

Romano

June

207 Jackston Ave. Westport, IN 47283

(812) 555-2681

22

$15.79

$13.956.

6 Set the width of column A to 10 characters, columns B and C to 12 characters, column D to

20 characters, and columns E, G, and H to 16 characters. (Select column, Home tab, Cells

group, Format, Column Width)

  1. Autofit all rows in the worksheet to the cell contents. (Select row, Home, Cells group,

Format, AutoFit Row Height)

  1. In cell I5, enter Total Charge. In cell I6, insert a formula that calculates the total charge

for the first customer (the number of DVDs multiplied by the price per DVD and then added

to the shipping charge). Increase the width of column I to 11 characters.

  1. Copy the formula in cell I6 and paste it into the range I7:I10.
  2. In cell E11, enter Total DVDs. In cell F11, use the SUM function to calculate the total

number of DVDs created for all customers. In cell I11, use the AutoSum feature to insert

the SUM function to calculate the total charges for all of the customer orders.

  1. Use Edit mode to make the following corrections:
  2. In cell D6, change the street address from 25 Lee St. to 2500 Lee St.
  3. In cell F9, change the number of DVDs from 15 to 17.
  4. In cell H8, change the shipping charge from $7.23 to $8.23.
  5. Use the Find and Replace commands to replace all occurrences of St. with Street, Ln.

with Lane, and Ave. with Avenue.

  1. Fill color cell range A5:I5 to a color of your choosing. Surround each cell (all cells) with a

border.

  1. Change the page layout of the Customer Orders worksheet to print in landscape orientation

on a single page.

  1. Return the view of the Customer Orders worksheet to Normal view, save your changes to

the Order Report workbook, and then save the current workbook as Revised Report.

  1. Remove Kay Dexter s order from the Customer Orders worksheet.
  2. Add the following order directly after the order placed by June Romano: date 4/22/2015;

name Patrick Crawford; address 200 Valley View Road, Rome, GA 30161; phone (706)

555-0998; DVDs 14; price per DVD $16.55; shipping charge $12.45.

  1. Use Edit mode to change the ending date of the report in cell A4 from April 17 to April

22.

  1. Save the workbook and preview it for correctness.
  2. Submit all saved workbooks to the instructor s gradebook via Blackboard. No emails of

homework allowed/accepted

 

part 2

Altac Bicycles Deborah York is a financial consultant for Altac Bicycles, an online seller of bicycles and bicycle equipment based in Silver City, New Mexico. She has entered some financial information in an Excel workbook for an income statement she is preparing for the company. You will enter the remaining data and formulas.

  1. Open the Altac workbook, and then save the workbook as Altac Bicycles.
  2. Insert three new rows at the top of the Sheet1 worksheet, and then enter the following text on two lines within cell A1: (In cell A1, enter the first line of text, and then hold down the Alt key and press the enter key, and then enter next line of text.)

Altac Bicycles

Income Statement*

  1. In cell A2, enter For the Years Ended December 31, 2012 through December 31, 2014.
  2. In the range C6:E7, enter the following net sales and cost of sales figures: 2014 2013 2012

Net Sales 13,520 10,981 9,034

Cost of Sales 4,140 3,960 3,011

  1. In the range C11 :E14, enter the following expense figures (expand the column widths as necessary to show the text and data values):

2014 2013 2012

Salaries and Wages 1,632 1,481 1,392

Sales and Marketing 2,631 2,112 1,840

Administrative 521 410 375

Research and Development 501 404 281

  1. In the nonadjacent range C18:E18;C20:E20;C24:E24, enter the following values for Other Income, Income Taxes, and Shares, pressing the Enter or Tab key to navigate from cell to cell in the selected range:

2014 2013 2012

Other Income 341 302 239

Income Taxes 1,225 1,008 821

Shares 3,621 3,001 2,844

  1. In the range C8:E8, enter a formula to calculate the gross margin for each year, where the gross margin is equal to the net sales minus the cost of sales.
  2. In the range C15:E15, enter the SUM function to calculate the total operating expenses for each year, where the total operating expenses equal the sum of the four expense categories.
  3. In the range C17:E17, enter a formula to calculate the operating income for each year, where operating income is equal to the gross margin minus the total operating expenses.
  4. In the range C19:E19, enter a formula to calculate the pretax income for each year, where pretax income is equal to the operating income plus other income.
  5. In the range C22:E22, enter a formula to calculate the company’s net income for each year, where net income is equal to the pretax income minus income taxes.
  6. In the range C25:E25, enter a formula to calculate the earnings per share for each year, where

earnings per share is equal to the net income divided by the number of shares.

  1. Use the spelling checker to correct and replace any spelling errors in the worksheet. Ignore the

spelling of “ALtac.”

  1. In cell A18, use Edit mode to capitalize the word “income.”
  2. Increase the width of column A to 18 characters and increase the width of column B to 25

characters. Autofit the height of row 1.

  1. Rename the Sheet1 worksheet as Income Statement.
  2. Add a new worksheet, Sheet2, and rename the Sheet2 worksheet as Documentation and move it to

the beginning of the workbook.

  1. In the Documentation worksheet, enter the following text and values:

Cell A1: Altac Bicycles

Cell A3: Author

Cell A4: Date

Cell A5: Purpose

Cell B3: your name

Cell B4: the current date

Cell B5: Income statement for Altac Bicycles for 2012 through 2014

  1. Save the workbook, preview the workbook and make sure each worksheet in portrait orientation fits

on one page.

  1. Close the workbook, and then submit the finished workbook via Blackboard to the instructor s

gradebook.

Year 2014 2013 2012

Sales

Net Sales

Cost of Sales

Gross Margin

 

Expenses

Salaries and Wages

Sales and Marketing

Administratibe

Research and Development

Total Operating Expenses

 

Operating Income

Other income

Pre-tax Income

Income taxes

 

Net income

 

Shares

Eanings per share

 

* (in millions except per-share amounts)

 

Order Now

YOU CAN ALSO PLACE OTHER SIMILAR ORDERS ON OUR WEBSITE AND GET AMAZING DISCOUNTS!!!

Latest completed orders:

Completed Orders
# Title Academic Level Subject Area # of Pages Paper Urgency