Microsoft Excel assignment, accounting homework help

Anonymous
account_balance_wallet $10

Question Description

It is a 24 question assignment that must be done on on an excel spreadsheet. You must be proficient to advanced on microsoft excel.

1Start Excel. Open the downloaded Excel file named GO_eV1_Grader_CAP.xlsx.
0
2On the Net Sales worksheet, in the range F4:F8, calculate the annual total sales for each state.
2
3In the range B9:F9, calculate totals.
2
4Using absolute cell references as necessary, in cell G4, construct a formula to calculate the percent that the Colorado Total is of Total Sales, and then fill the formula down through the range G5:G8.
2
5Apply the Total cell style to the range B9:F9.
2
6In cell H4, insert a Line sparkline using the data range B4:E4. Format the sparkline by applying the Sparkline Style Accent 2 (no dark or light). Copy the sparklines down to cell H8.
2
7Insert a 3-D Pie chart using the nonadjacent ranges A4:A8 and G4:G8. Change the chart style to Style 7. Reposition the chart so that the top left corner of the chart is centered in cell A10. Change the width of the chart to 6.5 inches.
2
8Change the chart title to Annual Net Sales by State and add data labels to the chart that display only the Category Name and Percentage positioned in the Center. Change the data labels Font color to Black, Text 1 and apply Bold Italic. Remove the Legend from the chart.
2.5
9Change the angle of the first slice of the pie to 200, and then explode the red New Mexico slice of the pie by 10%. Change the solid fill color of the orange Colorado slice to Tan, Accent 6.
2.5
10On the Seattle Inventory worksheet, in cell G14, enter an IF function to determine the items to be ordered. If the Quantity in Stock is less than 50, then the cell should displayOrder. If not, then the cell should display OK. Copy the function down through cell G19.
2
11Format the range A13:G19 as a table with headers and then apply Table Style Medium 5.
2
12Add Gradient Fill Orange Data Bars to the range A14:A19. Add conditional formatting to the range G14:G19 so that the cells with text that contain the word Order are formatted as Yellow Fill with Dark Yellow Text. Sort the table by Item # from Smallest to Largest.
2.5
13Filter the table on the Sport column to display only the Skiing types. Display a Total Row in the table, and then sum the Quantity in Stock for Skiing items. Type the result in cell B11. Remove the total row from the table and then clear the Sport filter.
2
14In cell B5, enter a function that will display the average retail price. In cell B6, enter a function that will display the median retail price. In cell B7, enter a function that will calculate the lowest retail price. In cell B8, enter a function that will calculate the highest retail price. Format the range B5:B8 with Accounting Number Format.
4
15Display the Inventory Summary sheet. In cell B4, enter a formula that references cell B4 in the Seattle Inventory sheet so that the Seattle Total Items in Stock displays in cell B4. In cell B5, enter a formula that references cell B5 in the Seattle Inventory sheet so that the Seattle Average Price displays in cell B5. In cells B6, B7, and B8, enter similar formulas to reference the Median Price, Lowest Price, and Highest price in the Seattle Inventory sheet.2.5
16In cell C4, enter a formula that references cell B4 in the Denver Inventory sheet so that the Denver Total Items in Stock displays in cell C4. In cells C5, C6, C7, and C8, enter similar formulas to reference the Average Price, Median Price, Lowest Price, and Highest price in the Denver Inventory sheet.2.5
17On the Annual Expenses sheet, construct formulas to calculate Totals by Quarter in the range B10:E10 and the Annual Totals in the range F5:F10. Apply the Total cell style to the Totals by Quarter (B10:F10), and then center the column headings (B4:G4) and apply the Heading 4 cell style.
2
18Using absolute cell references as necessary, in cell G5, construct a formula to calculate the % of Total by dividing the Sales Expense Annual Total by the Annual Totals by Quarter. Fill the formula down through the range G6:G9.
2
19Using the data in the nonadjacent ranges B4:E4 and B10:E10, insert a Line with Markers chart. Position the upper left corner of the chart slightly inside the upper left corner of cell A12. Change the height of the chart to 1.75 inches and the width of the chart to 7.25 inches.
1.5
20Apply chart Style 7. Change the chart title to 2016 Direct Expenses. Edit the Vertical (Value) Axis so that the Minimum is 8000000 and the Major unit 1000000.
3.5
21Use Format Painter to copy the formatting from cell A2 to A20. In cell B23, enter a formula that references the value in cell F10.2
22Using absolute cell references as necessary, in cell C23, construct a formula to calculate the projected expenses for 2018 after the Forecasted increase in cell B21 is applied. Fill the formula through cell F23.2.5
23Display the Sales Reps sheet. By using Flash Fill and deleting columns as necessary, place the last names in column A, the first names in column B, and then merge and center the title Sales Reps across the two columns.
2
24Ensure that the worksheets are correctly named and placed in the following order in the workbook: Net Sales; Seattle Inventory; Denver Inventory; Inventory Summary; Annual Expenses; Sales Reps. Save the workbook. Close the workbook and then close Excel. Submit the workbook as directed.
0

Unformatted Attachment Preview

Front Range Action Sports Annual Net Sales 2016 Quarter 1 Colorado $ 48.123.789 Quarter 2 $ Quarter 3 42.468.256 $ 45.159.681 Quarter 4 $ 49.452.695 New Mexico Oregon 25.783.516 35.658.498 21.985.365 34.689.526 19.987.269 37.986.369 22.252.487 39.456.899 California Washington Total Sales 58.123.789 42.143.258 64.468.256 46.598.456 65.159.681 44.874.332 68.452.695 50.546.222 Total Percent of Total Sales Trend Seattle Facility: Inventory Status of Apparel As of December 31, 2016 Total Items in Stock Average Price 271 Median Price Lowest Price Highest Price Ski Products Ski Quantity in Stock Quantity in Stock 32 78 28 58 57 18 Item # 115689 114568 456897 465899 465888 165332 Item Name Deluxe Biking Shorts Vented Explorer Wicked Heavy Socks Gripper Gloves Airflow Hat Mini Crew Socks Retail Price 69,52 34,78 9,35 25,55 2,99 6,45 Category Pants Hats Socks and Gloves Socks and Gloves Hats Socks and Gloves of Apparel Sport Biking Hiking Skiing Skiing Hiking Fitness Stock Level Denver Facility: Inventory Status of Apparel As of December 31, 2016 Total Items in Stock Average Price Median Price Lowest Price $ $ $ Highest Price $ 18,98 Number of Ski Products Ski Quantity in Stock Quantity in Stock 265 9,68 6,45 2,99 0 353 Item # Item Name 58 965888 Cotton Visor 35 658752 Bandana 18 58 96 765332 Mini Crew Socks 729567 Striped Crew Socks 796689 Sun Runner Cap 20170330225118lopez_aguilar_go_ev1_grader_cap.xlsx Retail Price Category 2,99 Hats 4,99 Hats 6,45 Socks and Gloves 14,99 Socks and Gloves 18,98 Hats Sport Biking Hiking Stock Level OK Order Fitness Fitness Fitness Order OK OK Apparel Inventory Summary As of December 31, 2016 Seattle Total Items in Stock Average Price Median Price Lowest Price Highest Price Denver Direct Expenses by Quarter Summary, December 2016 Sales Expense Quarter 1 Quarter 2 Quarter 3 Quarter 4 Annual Total $ 4.458.578 $ 4.678.712 $ 4.887.953 $ 5.325.215 Administrative Expense Rent Expense Interest Expense Advertising Expense Totals by Quarter Expenses 5-Year Forecast Forecasted Increase Year Projected Expenses 1.311.774 1.317.470 1.587.963 1.317.470 1.689.563 1.317.470 1.856.878 1.317.470 129.713 964.872 138.963 1.184.532 115.786 1.278.741 128.963 1.581.372 2018 2019 2020 3,5% 2017 2021 % of Total Alexander, Rebekah Alvarez, Eliza Bradley, Christine Caputo, Stephanie DiAntonio, Avelina Furfy, Jana Hernandez, Anna ...
Purchase answer to see full attachment

Tutor Answer

bsabillon
School: UCLA

Here are the answers. Kindly let me know of any edits needed and remember to invite me in your next assignment too.Also remember that you need to upload the file named as is listed in the guideline: "GO_eV1_Grader_CAP.xlsx."Regards.

Front Range Action Sports
Annual Net Sales 2016
Quarter 1
Colorado
New Mexico
Oregon
California
Washington
Total Sales

$

Quarter 2

48.123.789

$

Quarter 3

42.468.256 $

45.159.681

25.783.516
35.658.498

21.985.365
34.689.526

58.123.789
42.143.258
$ 209.832.850

64.468.256
46.598.456
$ 210.209.859

Quarter 4
$

49.452.695

$

185.204.421

19.987.269
37.986.369

22.252.487
39.456.899

$
$

90.008.637
147.791.292

65.159.681
44.874.332
$ 213.167.332

68.452.695
50.546.222
$ 230.160.998

Anual Net Sales by State
New Mexico
10%
Colorado
22%

Total

Oregon
17%

California
30%

Washington
21%

$ 256.204.421
$ 184.162.268
$ 863.371.039

Percent of
Total Sales
21,45%
10,43%
17,12%
29,67%
21,33%

Trend

Seattle Facility: Inventory Status of Apparel
As of December 31, 2016
Total Items in Stock
Average Price

271
$ 24,77

Median Price
Lowest Price

$ 17,45
$ 2,99

Highest Price

$ 69,52

Ski Products
Ski Quantity in Stock
Quantity in Stock
78
32
18
28
57
58

86
Item #
114568
115689
165332
456897
465888
465899

Item Name
Vented Explorer
Deluxe Biking Shorts
Min...

flag Report DMCA
Review

Anonymous
Tutor went the extra mile to help me with this essay. Citations were a bit shaky but I appreciated how well he handled APA styles and how ok he was to change them even though I didnt specify. Got a B+ which is believable and acceptable.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors