Med Supply Online Warehouse, excel assignment help

Unit 3: Assignment – 65 points Unit outcomes addressed in this unit:  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.  Create a 3 -D column chart with appropriate data and formatting.  Use the Goal Seek function in Excel. Course outcome(s) practiced in this unit : IT153 -2: Use formulas and functions to perform calculations. Scenario : Your supervisor in the finance department at Med Supply Online W arehouse 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 6 years based on the assumptions 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 Instructions Part 1: 1. Download the Unit 3 data file from Doc Sharing . 2. Start Excel. Apply the Civic theme (or a similar 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. l. Year 1 Salaries (cell B11) = 17% * Year 1 Sales m. Copy cell B11 to the range C11:G11. n. Year 1 Shipping (cell B12) = 3.9% * Year 1 Sales o. Copy cell B12 to the range C12:G12. p. Year 1 Supplies (cell B13) = 1.3% * Year 1 Sales q. Copy cell B13 to the range C13:G13. r. Year 2 W eb Services (cell C14) = Year 1 W eb Services + (15% * Year 1 W eb 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 bb 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 W orksheet above. Use a background color ( e.g., teal ); add an Accent . 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 and 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 1 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. Instructions Part 2: 1. Open the workbook Med Supply Online W arehouse Six -Year Financial Assignment if not already open. 2. Use the nonadjacent ranges B3:G3 and B19:G19 to create a 3-D stacked column chart . Draw the chart by clicking the Column button (Insert tab | Charts group). W hen the Column gallery is displayed, click the Stacked Column chart type (column 1, row 2). W hen the chart is displayed, click the Move Chart button to move the chart to a new sheet and Name the chart sheet 3 -D Column 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 the 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 columns 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 columns 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 a color similar to the screenshot below . 5. Rename the sheet tabs to Six -Year Financial Projection and 3-D Column Chart. Rearrange the sheets so that the Six -Year worksheet is leftmost and color the tabs (your choice of different colors). 6. Click the Six -Year Financial Projection tab to display the worksheet. • Save the workbook as Unit_3_ Assignment_Your Name . 7. Click the chart area and use the Shape Effects button, click on Format (Chart Tools Format Tab | Shape Effects). Click 3 -D Rotation –3-D Rotation Options. Check the Right Angle Axes box. C lick the c lose button. Instructions Part 3: 1. Re -open the workbook if not already open. 2. Make four copies of the Six -Year Financial Projection W orksheet (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 three 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 2 14.75% 23.00% (2,353,532) 3 25.50% 2.65% 14,668,149 3. Use the W hat -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 W orksheet, 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 any blank worksheets and submit the workbook with the new values or the results of the goal seek . 6. Save the workbook with the latest changes and submit the workbook to the Dropbox for Unit 3. Unit 3 Assignment grading rubric = 65 points Assignment Requirements Points Possible Points Earned 1. The worksheet is formatted appropriately. (2) The worksheet title and subtitle are entered and formatted. (2) The column widths are adjusted, and column and row titles are entered. (2) The Assumptions data is entered correctly. ( 2 ) The numbers in the worksheet are formatted. (2) 0–10 All formulas are entered as specified (5) using proper Absolute References where indicated. (5) 0–10 The IF function was entered correctly (5) obtaining the specified results. (5) 0–10 The background colors are modified. (1) The worksheet is divided into sections. (1) The worksheet header and footer are changed. (1) The file is saved as instructed and blank 0–5 worksheets deleted . ( 1 ) Sparkline created. (1) Four copies of the worksheet are made, named as instructed (4) and tab colors inserted. (2) The sheet tabs are renamed, (2) and their order and colors are changed. (2) 0–10 A 3-D column chart is created on a new sheet with appropriate data and formatting. (6) 0–6 The Right Angle Axes box was checked. 0–3 W hat -If Analysis is completed for the three case worksheets . (2 points each) 0–6 Goal seek was completed as instructed. 0–5 Total Points 0–65