Its Excel.
Week 2: Excel 2021 In Practice - Ch 3 Independent Project 3-4 is used to complete the assignment3/10/24, 6:43 PM Week 2: Excel 2021 In Practice - Ch 3 Independent Project 3-4 - SIMnet https://devry.simnetonline.com/sp/assignments/projects/details/8913274 1/3 Week 2: Excel 2021 In Practice - Ch 3 Independent Project 3-4 COURSE NAME BIS155 MAR24 Master | Data Analysis with Spreadsheets with Lab - 60612 Independent Project 3-4 For this project, you create a column chart to graph profit margins for Classic Gardens and Landscapes. You also create a line chart to illustrate a break-even point in units for new catalog items. [Student Learning Outcomes 3.1, 3.2, 3.3, 3.4, 3.6] File Needed: ClassicGardens-03.xlsx (Available from the Start File link.) Completed Project File Name: [your name]-ClassicGardens-03.xlsx Skills Covered in This Project Create a chart object. Size and position a chart object. Edit and format chart elements. Edit the source data for a chart. Build a line chart object. Edit axis labels. Position the legend. Add and format gridlines in a chart. Steps to complete This Project Mark the steps as checked when you complete them. 1. Open the ClassicGardens-03 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. 2. Use the Quick Analysis tool to create a Clustered Column chart object for cells A4:G9. 3. Move the chart object so that its top-left corner is at cell A12. Size the bottom of the chart to reach cell H30. 4. Edit the chart title to display CGL Gross Margin in Dollars on the first line. On the second line, type By Department. (In the Formula bar, press Alt+Enter to start a new line.) 5. Apply chart Style 14 to the chart. 6. Apply a 1 pt Black, Text 1 (second column) outline to the chart area. 7. Display the primary minor horizontal axis gridlines. Format the major value axis gridlines with a Black, Text 1 outline color. Do not change the color for the minor gridlines. (The Chart Elements drop-down list identifies the gridlines as Vertical (Value) Axis because they track values on the vertical axis.) 8. Resize the data range to remove the Design Consulting data series from the chart (Figure 3-84). 9. Select cell A1 and click the BreakEven sheet tab. 10. Review break-even formulas and build the data source for a line chart. Start Date:03/02/202411:59 PMUS/Mountain Due Date:03/10/202411:59 PMUS/Mountain End Date:04/21/202411:59 PMUS/Mountain Print Info Student Name: Bonds, Janai Student ID: D41202222 Username: D41202222 https://devry.simnetonline.com/sp/assignments 3/10/24, 6:43 PM Week 2: Excel 2021 In Practice - Ch 3 Independent Project 3-4 - SIMnet https://devry.simnetonline.com/sp/assignments/projects/details/8913274 2/3 Figure 3-84 Dragging the resize pointer Figure 3-86 “Sold” column deselected to remove line a. Select cell B8. The margin per unit is calculated by subtracting the variable cost from the sales price. b. Select cell B10. The formula divides the fixed cost by the sales price minus the variable cost (margin per unit). The results are rounded to show no decimal places. c. Review the references and formulas in row 15. Build a table for the chart by copying the formulas in row 15 to the blank rows below it up to and including row 24. d. Select cell B5 and type 200. Verify that at this price, the break- even point is 200 units. This is the level at which the profit covers all costs. 11. Create a line chart. a. Select cells B14:C24 and E14:F24. b. Create a 2-D Line chart with markers for each line. c. Position the chart object to start at cell A26 and to reach cell H45. 12. Edit the source data. a. Click the Select Data button [Chart Design tab, Data group]. b. Click Edit in the Horizontal (Category) Axis Labels area (Figure 3-85). Excel has determined a scale for the category labels, but you can override those results. Figure 3-85 Axis labels reset override default number scale c. Select cells B15:B24 for the Axis label range and click OK. d. Deselect the # Sold box in the Legend Entries (Series) area and click OK (Figure 3-86). These are category labels and should not be plotted as a line. 13. Edit the chart title to display Break Even in Units. 14. Display and format chart elements. a. Use the Chart Elements quick button to position the legend at the top. b. Display the primary major vertical gridlines. c. Format both vertical and horizontal gridlines with Black, Text 1, Lighter 50% for the Shape Outline. d. Apply a 1 pt Black, Text 1 outline for the chart area. e. Apply an Offset: Bottom Right shadow effect to the plot area. 3/10/24, 6:43 PM Week 2: Excel 2021 In Practice - Ch 3 Independent Project 3-4 - SIMnet https://devry.simnetonline.com/sp/assignments/projects/details/8913274 3/3 15. Change the sales price to $225 (cell B5). 16. Complete worksheet formatting. a. Select cells A1:A3 and change font size to 16. b. Select cells A1:H3 and center the cells across the selection. c. Apply a bottom border to cells A3:H3. d. Select cell A1. e. Set the page for landscape orientation, center the page horizontally, and scale it to fit one page. 17. Save and close the workbook (Figure 3-87). Figure 3-87 Excel 3-4 completed 18. Upload and save your project file. 19. Submit file for grading. BreakEven Classic Gardens and Landscapes Break Even Point in Units Based on Sales Price Sales Price$250 Assigned Variable Cost$125 Fixed Cost for Department$15,000 Per Unit Margin (Profit)$125 Break Even Point120 # SoldFixed CostTotal Variable CostsTotal CostsDollar SalesProfit 0$15,000$0$15,000$0-$15,000 50 75 100 125 150 175 200 225 250 Gross Margins Classic Gardens and Landscapes (CGL) Gross Margin by Department, Six Months DepartmentsAprilMayJuneJulyAugustSeptemberTotal Lawn Maintenance$4,500$5,200$6,500$8,400$8,500$10,000$43,100 Tree & Shrubbery $5,000$3,400$3,500$6,500$12,000$12,500$42,900 Patio and Furniture$12,000$9,000$15,500$13,500$15,000$12,450$77,450 Fountains and Irrigation$10,000$5,500$6,500$7,200$7,000$8,000$44,200 Design Consulting$5,000$5,200$3,500$4,500$3,500$8,000$29,700 Total Gross Margin$36,500$28,300$35,500$40,100$46,000$50,950$237,350