IT Spreadsheet Applications Kaplan

timer Asked: Jul 22nd, 2014

Question Description

Outcomes addressed in this activity: Unit 3 Data File.xlsx Rubric spreadsheet.pdf

  • Use Freeze Panes so that column and row titles remain visible
  • Apply the use of absolute cell references
  • Use the IF function to enter values in a cell on the basis of a logical test

Course Outcome: IT153-2: Create formulas and functions.

Download the Unit 3 data file from Doc Sharing and save the workbook.

Perform the tasks as instructed in the rubric below. The Rubric will have all the steps that need to be completed.

Med Supply Online Warehouse Six-Year Financial Projection Year 1 Sales Cost of Goods Gross Margin Expenses Advertising Maintenance1605000 4378000 Rent 2700000 Salaries Shipping Supplies Web Services 250000 Total Expenses Operating Income Income Tax Net Income Assumptions Units Sold in Prior 1589712 Year Unit Cost 59,5 Annual Sales Growth 0,045 Annual Price Decrease 0,038 Margin 0,388 5920000 5050000 3200000 4250000
Unit 3 [IT153: Spreadsheet Applications] Assignment Details and Rubric Outcomes addressed in this activity:    Use Freeze Panes so that column and row titles remain visible Apply the use of absolute cell references Use the IF function to enter values in a cell on the basis of a logical test Course Outcome: IT153-2: Create formulas and functions. Instructions: Six- Year Financial Projection Problem: Your supervisor in the Finance department at Med Supply Online Warehouse has asked you to create a worksheet that will project the annual gross margin, expenses, total expenses, operating income, income taxes, and net income for the next six years based on the assumptions in Table below. The desired worksheet is shown in Figure 1 below. In Part 1 you will create the worksheet. In Part 2 you will create a chart to present the data, shown in Figure 2. In Part 3 you will use Goal Seek to analyze three different sales scenarios. Figure 1 Unit 3 [IT153: Spreadsheet Applications] Instructions Part 1: 1. Download the Unit 3 data file from Doc Sharing and save the workbook using the file name: Unit 3 Assignment Your Name. 2. Start Excel. Apply the Civic theme to the worksheet by using the Themes button (Page Layout tab | Themes group). Bold the entire worksheet by selecting the entire worksheet and using the Bold button (Home tab | Font group). 3. Format the worksheet title in cell A1 to 36- point Copperplate Gothic Bold (or a similar font). Format the worksheet subtitle in cell A2 to 20- point Verdana (or a similar font). Enter the system date in cell G2 using the NOW function. Format the date to the 14- Mar- 01 style. 4. Change the following column widths: A = 25.00 characters; B through H = 15.00 characters. 5. Change the heights of row 1 to 45; row 2 to 25 and rows 7, 15, 17, 19, and 22 to 18.00 points. 6. Enter the six column titles Year 1 through Year 6 in the range B3: G3 by taking Year 1 in cell B3 and then dragging cell B3’ s fill handle through the range C3: G3. Format cell B3 as follows: (a) increase the font size to 14; (b) center and italicize it; and (c) angle its contents clockwise. Use the Format Painter button (Home tab | Clipboard group) to copy the format assigned to cell B3 to the range C3: G3. Increase the Row Height to 45. 7. Change the font in cells A7, A15, A17, and A19 to 14- point Verdana (or a similar font). Add thick bottom borders to the ranges B3: G3 and B5: G5. Use the Increase Indent button (Home tab | Alignment group) to increase the indent of the row titles in cell A5, the range A8: A14, and cell A18. 8. In cell A22, change the font size to 14- point Verdana and underline it. 9. Complete the following entries: a. Year 1 Sales ( cell B4) = Units Sold in Prior Year * ( Unit Cost / ( 1 - Margin)) b. Year 2 Sales ( cell C4) = Year 1 Sales * ( 1 + Annual Sales Growth(Cell Absolute)) * ( 1 - Annual Price Decrease(Cell Absolute)) c. Copy cell C4 to the range D4: G4. d. Year 1 Cost of Goods ( cell B5) = Year 1 Sales * ( 1 – Margin(Cell Absolute)) e. Copy cell B5 to the range C5: G5. f. Gross Margin ( cell B6) = Year 1 Sales - Year 1 Cost of Goods g. Copy cell B6 to the range C6: G6. h. Year 1 Advertising ( cell B8) = 500 + 8% * Year 1 Sales i. Copy cell B8 to the range C8: G8. j. Year 2 Rent ( cell C10) = Year 1 Rent + ( 12% * Year 1 Rent) k. Copy cell C10 to the range D10: G10. Unit 3 [IT153: Spreadsheet Applications] l. m. n. o. p. q. r. Year 1 Salaries ( cell B11) = 17% * Year 1 Sales Copy cell B11 to the range C11: G11. Year 1 Shipping ( cell B12) = 3.9% * Year 1 Sales Copy cell B12 to the range C12: G12. Year 1 Supplies ( cell B13) = 1.3% * Year 1 Sales Copy cell B13 to the range C13: G13. Year 2 Web Services ( cell C14) = Year 1 Web Services + ( 15% * Year 1 Web Services) s. Copy cell C14 to the range D14: G14. t. Year 1 Total Expenses ( cell B15) = SUM( B8: B14) u. Copy cell B15 to the range C15: G15. v. Year 1 Operating Income ( cell B17) = Year 1 Gross Margin - Year 1 Total Expenses or = B6- B15 w. Copy cell B17 to the range C17: G17. x. Year 1 Income Taxes ( cell B18): If Year 1 Operating Income is less than 0, then Year 1 Income Taxes equal 0; otherwise Year 1 Income Taxes equal 45% * Year 1 Operating Income y. Copy cell B18 to the range C18: G18. z. Year 1 Net Income ( cell B19) = Year 1 Operating Income - Year 1 Income Taxes aa. Copy cell B19 to the range C19: G19. bb. In cell H4, insert a Sparkline Column chart ( Insert Tab| Sparklines group) for cell range B4: G4 cc. Repeat step ee for the ranges H5: H6, H8: H15, and H17: H19 10. Select the range B4: G19 apply the Comma style with no decimal places. Set cells B25 to B27 to Percent with 2 decimal places. 11. Change the background colors as shown in Figure 1 of the Finished Worksheet above. Use Teal, Accent 3, Lighter 40% for the background colors. 12. Change the worksheet header with your name (Right Side) and course number (Left Side). Change the Footer to include the Date (Left Side) and Page number (Right Side & use tools). Save the workbook using the file name, Unit 3 Assignment Your Name. 13. Preview the worksheet. Use the Orientation button (Page Layout tab | Page Setup group) to fit the printout on one page in landscape orientation. Preview the formulas version ( ctrl+`) of the worksheet in landscape orientation using the Fit to option. Press ctrl +` to instruct Excel to display the values version of the worksheet. Save the workbook again. Unit 3 [IT153: Spreadsheet Applications] Instructions Part 2: 1. Open the workbook Med Supply Online Warehouse Six- Year Financial Assignment if not already open. 2. Use the nonadjacent ranges B3: G3 and B19: G19 to create a Clustered Cylinder Chart. Draw the chart by clicking the Column button (Insert tab | Charts group). When the Column gallery is displayed, click the Clustered Cylinder chart type (column 1, row 3). When the chart is displayed, click the Move Chart button to move the chart to a new sheet and Name the chart sheet 3-D Cylinder Chart. 3. Select the legend on the right side of the chart and delete it. Add the chart title by clicking the Chart Titles button (Chart Tools Layout tab | Labels group). Click Above Chart in the Chart Title gallery. Format the chart title as shown in completed chart below. 4. To change the color of the cylinders, click one of the cylinders and use the Shape Fill button (Chart Tools Format tab | Shape Styles group). To change the color of the wall, click the wall behind the cylinders and use the Shape Fill button to change the chart wall color. Use the same procedure to change the color of the base of the wall. Change colors to match screenshot below. 5. Rename the sheet tabs to Six- Year Financial Projection and 3-D Cylinder Chart. Rearrange the sheets so that the Six-Year worksheet is leftmost and color their tabs (your choice of different colors). 6. Click the Six- Year Financial Projection tab to display the worksheet. Save the workbook. Unit 3 [IT153: Spreadsheet Applications] Instructions Part 3: 1. Re-Open the workbook if not already open. 2. Make 4 copies of the Six-Year Financial Projection Worksheet (make sure you use the Move or Copy by right-clicking the Tab): a. Name the new worksheets Case 1, Case 2, Case 3 and Goal Seek. b. Color the 3 Case Tabs using a Green color and the Goal Seek Tab as Red Table 2 Med Supply Online Warehouse Data to Analyze and Results Case Annual Sales Growth Annual Price Decrease Year 6 Resulting Net Income in Cell G19 1 8.45% 5.75% 2,925,008 Unit 3 [IT153: Spreadsheet Applications] 2 14.75% 23.00% ( 2,353,532) 3 25.50% 2.65% 14,668,149 3. Use the What- If Analysis button (Data tab | Data Tools group) to goal seek. Determine a margin (cell B27) that would result in a Year 6 net income of $ 4,000,000 (cell G19). You should end up with a margin of 40.49% in cell B27. 4. In the Six-Year Financial Projection Worksheet, divide the window into two panes by dragging the horizontal split box between rows 6 and 7. Use the scroll bars to show both the top and bottom of the worksheet. Using the numbers in columns 2 and 3 of Table 2 below, analyze the effect of changing the annual sales growth (cell B25) and annual price decrease (cell B26) on the net incomes in row 19. The resulting answers are in column 4 of Table 2 above. Save the workbook and results of the what- if analysis for each case. 5. Delete and blank worksheets and Submit the workbook with the new values or the results of the goal seek, save the workbook with the latest changes and submit the workbook to the Dropbox for Unit 3. All deliverables should be professionally formatted and should be free of spelling errors. Points deducted from the grade for each error are at your instructor’s discretion. Unit 3 Assignment grading rubric = 50 points Assignment Requirements Points possible The worksheet is formatted appropriately. The worksheet title and subtitle are entered and formatted. The column widths are adjusted, and column and row titles are entered. The Assumptions data is entered correctly. The numbers in the worksheet are formatted. 0-5 All formulas are entered as specified using proper Absolute References where indicated. 0 - 10 The IF function was entered correctly obtaining the specified results. 0-5 Points earned Unit 3 [IT153: Spreadsheet Applications] The background colors are modified. The Worksheet is divided into sections. The worksheet header and footer are changed. The file is saved as instructed and blank worksheets deleted Sparkline created. 0-5 Four copies of the worksheet are made, named as instructed and tab colors made. The sheet tabs are renamed, and their order and colors are changed. 0-4 A 3-D Cylinder chart is created on a new sheet with appropriate data and formatting. 0-5 What If Analysis is completed for the 3 Case Worksheets. 0-6 Goal seek was completed as instructed. 0-5 Project reflects the concepts from the Readings and Step-bysteps of the text. 0-5 TOTAL POSSIBLE POINTS: 0 - 50 Points deducted for spelling or formatting Adjusted total points

This question has not been answered.

Create a free account to get help with this and any other question!

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