Please let me know how much it is to get this by Sunday. Thank you
ACCT 1120 Exam 2 Exam 2 consists of 3 independent parts. You can only access the dropbox in Blackboard once so you will need to complete the entire exam before uploading. Please double check your work to ensure you are submitting your final documents for grading as resubmissions will not be accepted. You will submit a total of 3 files when you are done: · Yourname Exam 2 Part A (Example: Tiffaney Wilson Exam 2 Part A) · Yourname Exam 2 Part B (Example: Tiffaney Wilson Exam 2 Part B) · Yourname Exam 2 Part C (Example: Tiffaney Wilson Exam 2 Part C) If any of the pictures or exhibits displayed below are unclear please see the following pages in your textbook, but be sure to follow the instruction in this document and not the textbook because I have modified some portions of the assignment (Part A page EX 237; Part B page EX 300-301; Part C page EX 363-365) Part A Problem: You have been asked by the employee relations and resource department to develop an education planning worksheet that will allow each current employee to see the effect (dollar accumulation) of investing a percentage of his or her monthly salary in a 529(c) Education Savings plan over a period of years (Figure 4–68). Employees can contribute up to $15,000 per year per child to plans. The employee relations and resource department wants a one-input data table to show the future value of the investment for different years. The final worksheet is shown in Figure 4–68. Figure 4–68 Perform the following tasks: 1. Run Excel. Apply the Retrospect theme to the worksheet. Change the column widths to the following: A and F = 6.57 (51 pixels); B, E, and I = 0.75 (9 pixels); C = 22.14 (160 pixels); D, G, and H = 12.71 (94 pixels). Change the heights of rows 2, 4, and 25 to 8.25 (11 pixels). 2. In cell C3, enter Education Savings Calculator as the worksheet title. Merge and center cell C3 across columns C through H. Apply the Title cell style to cell C3. Change the background color of C3 to Olive Green, Text 2, Lighter 80% and change its font color to Brown, Accent 3, Darker 25%. 3. Enter Employee Name in cell C5, Annual Salary in cell C6, Percent Invested (up to) in cell C7, Annual Return in cell C8, Years in cell C9, Monthly Contribution in cell C10, and Future Value in cell C11. Add the data in Table 4–5 to column D. Use the currency and percent style formats to format the numbers in the range D6:D8. Table 4–5 Education Savings Employee Data Row Title Data Employee Name Josh Velez Annual Salary $94,250 Percent Invested 6.00% Annual Return 5% Years 18 4. Use the Create from Selection button (Formulas tab | Defined Names group) to assign the row titles in column C (range C6:C11) as cell names for the adjacent cells in column D. Use these newly created names to assign formulas to cells in the range D10:D11. a) Employee Monthly Contribution (cell D10) = IF(Percent_Invested__up_to * Annual_Salary < 15000, percent_invested__up_to * annual_salary / 12, 15000 / 12) b) future value (cell d11) = –fv(annual_return/12, years*12, monthly_contribution) the future value function (fv) returns to the cell the future value of the investment. the future value of an investment is its value at some point in the future based on a series of payments of equal amounts made over a number of periods while earning a constant rate of return. c) if necessary, use the format painter button (home tab | clipboard group) to assign the currency style format in cell d6 to the range d10:d11. 5. add the background color orange, accent 1, lighter 60%, and the font color olive green, text 2 to cells c5:c11, and a medium outside border to the range c5:d11, as shown in figure 4–68. 6. use the concepts and techniques developed in this module to add the data table in figure 4–68 to the range f5:h24 as follows: a) enter and format the table column titles in row 5 as shown in figure 4–68. b) use the fill handle to create the series of years beginning with 1 and ending with 18 in increments of 1 in column f, beginning in cell f7. c) in cell g6, enter =d11 as the formula. in cell h6, enter =12 * d10 * d9 as the formula (recall that using cell references in the formulas means excel will copy the formats). d) use the data table command to define the range f6:h24 as a one-input data table. use cell d9 as the column input cell. e) add the fill color orange, accent 1, lighter 80% to the range f6:h24. format the numbers in the range g7:h24 using the comma style format. add the background color orange, accent 1, lighter 60%, the font color olive green, text 2, and a light bottom border to cells f5:h5. add light vertical borders to cells f6:h24, and a medium outside border to the range f5:h24, as shown in figure 4–68. 7. add a fill pattern with the pattern color tan, accent 5, lighter 60% and the pattern style 12.5% gray to the range b2:i25. hint: look on the fill sheet in the format cells dialog box. change the sheet tab name to education savings calculator and color to orange, accent 1, lighter 60%, as shown in figure 4–68. 8. remove gridlines by removing the check mark from the view gridlines check box (page layout tab | sheet options group). 9. if requested by your instructor, change the employee name in cell d5 to your name. 10. unlock the cells in the range d5:d9. protect the worksheet. allow users to select only unlocked cells. 11. save the workbook using the file name, yourname exam 2 part a (example: tiffaney wilson exam 2 part a) . part b problem: you are part of a task force assessing the classroom capacities of the middle schools in your district. you have been charged with creating a master worksheet for the district and separate worksheets for each of the two middle schools. the middle school worksheets should be based on the district worksheet. once the worksheets have been created, the middle school data can be entered into the appropriate worksheets, and the district worksheet will reflect district-wide information. the district worksheet appears as shown in figure 5–74. perform the following tasks: 1. run excel. open the workbook lab 5 – 1 gladstone schools from the data files. save the workbook using the file name yourname exam 2 part b (example: tiffaney wilson exam 2 part b) 2. add two worksheets to the workbook after sheet1 and then paste the contents of sheet1 to the two empty worksheets. 3. from left to right, rename the sheet tabs district, hillview middle school, and mccarty middle school. color the tabs as shown in figure 5–74. on each of the school worksheets, change the title in cell b2 to match the sheet tab name. on each worksheet, fill the range a2:f3 to match the color of its sheet tab. enter the data in table 5–8 into the school worksheets. table 5–8 middle school classroom capacity figures school grade # of students # of rooms # of rooms over capacity # of rooms under capacity hillview middle school 6 180 8 2 0 7 188 8 8 0 8 145 7 0 7 mccarty middle school 6 124 5 4 0 7 132 6 0 0 8 118 5 2 1 4. on the two school worksheets, calculate average students per room in column d and totals in row 9. 5. on the district worksheet, use the sum function, 3-d references, and copy-and-paste capabilities of excel to populate the ranges b6:c8 and e6:f8. first, compute the sum in cells b6:c6 and e6:f6, and then copy the ranges b6:d6 and e6:f6 through ranges b7:c8 and e7:f8 respectively. finally, calculate average students per room for the district for each grade level, and for the district as a whole. 6. select the range e6:e9 on the district worksheet. select all the worksheets and then use the format cells dialog box to apply a custom format of [red]#,###;;“-”. 7. select the range f6:f9 on the district worksheet. select all the worksheets and then use the format cells dialog box to apply a custom format that will format all nonzero numbers similar to the format applied in step 6 but with green for nonzero entries. 8. use the cell styles button (home tab | styles group) to create a new cell style named my title. use the format button (styles dialog box) to create a format. use the font sheet (format cells dialog box) to select the britannic bold font, a font size of 22, and a white font color. check only the alignment and font check boxes in the style dialog box. 9. select cells a2:a3 on the district worksheet. select all the worksheets. apply the my title style to the cell. 10. using figure 5–74 as a guide, add borders to the worksheets. the borders should be the same on all worksheets. 11. select the district worksheet. create a 3-d pie chart using the range a6:b8. edit the title to match figure 5–74. apply the chart style 3 to the chart 12. move the chart to the right of the data. right-click the pie to display the shortcut menu and then click ‘format data series’ to open the format data series task pane. set the pie explosion to 10% to offset all of the slices. 13. select the chart area and display the format chart area task pane. set the x rotation to 100°. 14. use the chart elements button to display the data labels submenu. click more options. select only the value and ‘show leader lines’ options. choose the outside end label position and adjust the labels as necessary to display the leader lines. 15. save the workbook. part c problem: the city market wants an easier way to keep track of the shelf life of fresh vegetables. you will format and summarize the data as shown in figure 6–80a, and create the treemap shown in figure 6–80b. perform the following tasks: 1. open the workbook lab 6–1 city market. save the workbook using the file name, yourname exam 2 part c (example: tiffaney wilson exam 2 part c). format the data as a table. adjust column widths as necessary. 2. create three new calculated columns, shelf life, days left, and grade. the formula for calculating the shelf life is = [sell by date] − [stock date]. the formula for calculating the days left is = [sell by date] − currentdate. (hint: currentdate is a named cell, e19.) 3. the grade column will require you to create a lookup table area, shown in table 6–6. type the heading, grade table in cell j1 and fill in the column headings and data below that, as shown in table 6–6. the calculation for 15000,="" percent_invested__up_to="" *="" annual_salary="" 12,="" 15000="" 12)="" b)="" future="" value="" (cell="" d11)="–FV(Annual_Return/12," years*12,="" monthly_contribution)="" the="" future="" value="" function="" (fv)="" returns="" to="" the="" cell="" the="" future="" value="" of="" the="" investment.="" the="" future="" value="" of="" an="" investment="" is="" its="" value="" at="" some="" point="" in="" the="" future="" based="" on="" a="" series="" of="" payments="" of="" equal="" amounts="" made="" over="" a="" number="" of="" periods="" while="" earning="" a="" constant="" rate="" of="" return.="" c)="" if="" necessary,="" use="" the="" format="" painter="" button="" (home="" tab="" |="" clipboard="" group)="" to="" assign="" the="" currency="" style="" format="" in="" cell="" d6="" to="" the="" range="" d10:d11.="" 5.="" add="" the="" background="" color="" orange,="" accent="" 1,="" lighter="" 60%,="" and="" the="" font="" color="" olive="" green,="" text="" 2="" to="" cells="" c5:c11,="" and="" a="" medium="" outside="" border="" to="" the="" range="" c5:d11,="" as="" shown="" in figure="" 4–68.="" 6.="" use="" the="" concepts="" and="" techniques="" developed="" in="" this="" module="" to="" add="" the="" data="" table="" in figure="" 4–68 to="" the="" range="" f5:h24="" as="" follows:="" a)="" enter="" and="" format="" the="" table="" column="" titles="" in="" row="" 5="" as="" shown="" in figure="" 4–68.="" b)="" use="" the="" fill="" handle="" to="" create="" the="" series="" of="" years="" beginning="" with="" 1="" and="" ending="" with="" 18="" in="" increments="" of="" 1="" in="" column="" f,="" beginning="" in="" cell="" f7.="" c)="" in="" cell="" g6,="" enter ="D11 as" the="" formula.="" in="" cell="" h6,="" enter ="12" *="" d10="" *="" d9 as="" the="" formula="" (recall="" that="" using="" cell="" references="" in="" the="" formulas="" means="" excel="" will="" copy="" the="" formats).="" d)="" use="" the="" data="" table="" command="" to="" define="" the="" range="" f6:h24="" as="" a="" one-input="" data="" table.="" use="" cell="" d9="" as="" the="" column="" input="" cell.="" e)="" add="" the="" fill="" color="" orange,="" accent="" 1,="" lighter="" 80%="" to="" the="" range="" f6:h24.="" format="" the="" numbers="" in="" the="" range="" g7:h24="" using="" the="" comma="" style="" format.="" add="" the="" background="" color="" orange,="" accent="" 1,="" lighter="" 60%,="" the="" font="" color="" olive="" green,="" text="" 2,="" and="" a="" light="" bottom="" border="" to="" cells="" f5:h5.="" add="" light="" vertical="" borders="" to="" cells="" f6:h24,="" and="" a="" medium="" outside="" border="" to="" the="" range="" f5:h24,="" as="" shown="" in figure="" 4–68.="" 7.="" add="" a="" fill="" pattern="" with="" the="" pattern="" color="" tan,="" accent="" 5,="" lighter="" 60%="" and="" the="" pattern="" style="" 12.5%="" gray="" to="" the="" range="" b2:i25. hint: look="" on="" the="" fill="" sheet="" in="" the="" format="" cells="" dialog="" box.="" change="" the="" sheet="" tab="" name="" to education="" savings="" calculator and="" color="" to="" orange,="" accent="" 1,="" lighter="" 60%,="" as="" shown="" in figure="" 4–68.="" 8.="" remove="" gridlines="" by="" removing="" the="" check="" mark="" from="" the="" view="" gridlines="" check="" box="" (page="" layout="" tab="" |="" sheet="" options="" group).="" 9.="" if="" requested="" by="" your="" instructor,="" change="" the="" employee="" name="" in="" cell="" d5="" to="" your="" name.="" 10.="" unlock="" the="" cells="" in="" the="" range="" d5:d9.="" protect="" the="" worksheet.="" allow="" users="" to="" select="" only="" unlocked="" cells.="" 11.="" save="" the="" workbook="" using="" the="" file="" name,="" yourname="" exam="" 2="" part="" a="" (example:="" tiffaney="" wilson="" exam="" 2="" part="" a)="" .="" part="" b="" problem: you="" are="" part="" of="" a="" task="" force="" assessing="" the="" classroom="" capacities="" of="" the="" middle="" schools="" in="" your="" district.="" you="" have="" been="" charged="" with="" creating="" a="" master="" worksheet="" for="" the="" district="" and="" separate="" worksheets="" for="" each="" of="" the="" two="" middle="" schools.="" the="" middle="" school="" worksheets="" should="" be="" based="" on="" the="" district="" worksheet.="" once="" the="" worksheets="" have="" been="" created,="" the="" middle="" school="" data="" can="" be="" entered="" into="" the="" appropriate="" worksheets,="" and="" the="" district="" worksheet="" will="" reflect="" district-wide="" information.="" the="" district="" worksheet="" appears="" as="" shown="" in figure="" 5–74.="" perform="" the="" following="" tasks:="" 1.="" run="" excel.="" open="" the="" workbook="" lab="" 5="" –="" 1="" gladstone="" schools="" from="" the="" data="" files.="" save="" the="" workbook="" using="" the="" file="" name="" yourname="" exam="" 2="" part="" b="" (example:="" tiffaney="" wilson="" exam="" 2="" part="" b)="" 2.="" add="" two="" worksheets="" to="" the="" workbook="" after="" sheet1="" and="" then="" paste="" the="" contents="" of="" sheet1="" to="" the="" two="" empty="" worksheets.="" 3.="" from="" left="" to="" right,="" rename="" the="" sheet="" tabs="" district,="" hillview="" middle="" school,="" and="" mccarty="" middle="" school.="" color="" the="" tabs="" as="" shown="" in figure="" 5–74.="" on="" each="" of="" the="" school="" worksheets, change="" the="" title="" in="" cell="" b2="" to="" match="" the="" sheet="" tab="" name.="" on="" each="" worksheet,="" fill="" the="" range="" a2:f3="" to="" match="" the="" color="" of="" its="" sheet="" tab.="" enter="" the="" data="" in table="" 5–8 into="" the="" school="" worksheets.="" table 5–8="" middle="" school="" classroom="" capacity="" figures="" school="" grade="" #="" of="" students="" #="" of="" rooms="" #="" of="" rooms="" over="" capacity="" #="" of="" rooms="" under="" capacity="" hillview="" middle="" school="" 6="" 180="" 8="" 2="" 0="" 7="" 188="" 8="" 8="" 0="" 8="" 145="" 7="" 0="" 7="" mccarty="" middle="" school="" 6="" 124="" 5="" 4="" 0="" 7="" 132="" 6="" 0="" 0="" 8="" 118="" 5="" 2="" 1="" 4.="" on="" the="" two="" school="" worksheets,="" calculate="" average="" students="" per="" room="" in="" column="" d="" and="" totals="" in="" row="" 9.="" 5.="" on="" the="" district="" worksheet,="" use="" the="" sum="" function,="" 3-d="" references,="" and="" copy-and-paste="" capabilities="" of="" excel="" to="" populate="" the="" ranges="" b6:c8="" and="" e6:f8.="" first,="" compute="" the="" sum="" in="" cells="" b6:c6="" and="" e6:f6,="" and="" then="" copy="" the="" ranges="" b6:d6="" and="" e6:f6="" through="" ranges="" b7:c8="" and="" e7:f8="" respectively.="" finally,="" calculate="" average="" students="" per="" room="" for="" the="" district="" for="" each="" grade="" level,="" and="" for="" the="" district="" as="" a="" whole.="" 6.="" select="" the="" range="" e6:e9="" on="" the="" district="" worksheet.="" select="" all="" the="" worksheets="" and="" then="" use="" the="" format="" cells="" dialog="" box="" to="" apply="" a="" custom="" format="" of="" [red]#,###;;“-”.="" 7.="" select="" the="" range="" f6:f9="" on="" the="" district="" worksheet.="" select="" all="" the="" worksheets="" and="" then="" use="" the="" format="" cells="" dialog="" box="" to="" apply="" a="" custom="" format="" that="" will="" format="" all="" nonzero="" numbers="" similar="" to="" the="" format="" applied="" in step="" 6 but="" with="" green="" for="" nonzero="" entries.="" 8.="" use="" the="" cell="" styles="" button="" (home="" tab="" |="" styles="" group)="" to="" create="" a="" new="" cell="" style="" named="" my="" title.="" use="" the="" format="" button="" (styles="" dialog="" box)="" to="" create="" a="" format.="" use="" the="" font="" sheet="" (format="" cells="" dialog="" box)="" to="" select="" the="" britannic="" bold="" font,="" a="" font="" size="" of="" 22,="" and="" a="" white="" font="" color.="" check="" only="" the="" alignment="" and="" font="" check="" boxes="" in="" the="" style="" dialog="" box.="" 9.="" select="" cells="" a2:a3="" on="" the="" district="" worksheet.="" select="" all="" the="" worksheets.="" apply="" the="" my="" title="" style="" to="" the="" cell.="" 10.="" using figure="" 5–74 as="" a="" guide,="" add="" borders="" to="" the="" worksheets.="" the="" borders="" should="" be="" the="" same="" on="" all="" worksheets.="" 11.="" select="" the="" district="" worksheet.="" create="" a="" 3-d="" pie="" chart="" using="" the="" range="" a6:b8.="" edit="" the="" title="" to="" match figure="" 5–74.="" apply="" the="" chart="" style="" 3="" to="" the="" chart="" 12.="" move="" the="" chart="" to="" the="" right="" of="" the="" data.="" right-click="" the="" pie="" to="" display="" the="" shortcut="" menu="" and="" then="" click="" ‘format="" data="" series’="" to="" open="" the="" format="" data="" series="" task="" pane.="" set="" the="" pie="" explosion="" to="" 10%="" to="" offset="" all="" of="" the="" slices.="" 13.="" select="" the="" chart="" area="" and="" display="" the="" format="" chart="" area="" task="" pane.="" set="" the="" x="" rotation="" to="" 100°.="" 14.="" use="" the="" chart="" elements="" button="" to="" display="" the="" data="" labels="" submenu.="" click="" more="" options.="" select="" only="" the="" value="" and="" ‘show="" leader="" lines’="" options.="" choose="" the="" outside="" end="" label="" position="" and="" adjust="" the="" labels="" as="" necessary="" to="" display="" the="" leader="" lines.="" 15.="" save="" the="" workbook.="" part="" c="" problem: the="" city="" market="" wants="" an="" easier="" way="" to="" keep="" track="" of="" the="" shelf="" life="" of="" fresh="" vegetables.="" you="" will="" format="" and="" summarize="" the="" data="" as="" shown="" in figure="" 6–80a,="" and="" create="" the="" treemap="" shown="" in figure="" 6–80b.="" perform="" the="" following="" tasks:="" 1.="" open="" the="" workbook="" lab="" 6–1="" city="" market.="" save="" the="" workbook="" using="" the="" file="" name,="" yourname="" exam="" 2="" part="" c="" (example:="" tiffaney="" wilson="" exam="" 2="" part="" c).="" format="" the="" data="" as="" a="" table.="" adjust="" column="" widths="" as="" necessary.="" 2.="" create="" three="" new="" calculated="" columns,="" shelf="" life,="" days="" left,="" and="" grade.="" the="" formula="" for="" calculating="" the="" shelf="" life="" is="[Sell" by="" date]="" −="" [stock="" date].="" the="" formula="" for="" calculating="" the="" days="" left="" is="[Sell" by="" date]="" −="" currentdate.="" (hint: currentdate="" is="" a="" named="" cell,="" e19.)="" 3.="" the="" grade="" column="" will="" require="" you="" to="" create="" a="" lookup="" table="" area,="" shown="" in table="" 6–6.="" type="" the="" heading, grade="" table in="" cell="" j1="" and="" fill="" in="" the="" column="" headings="" and="" data="" below="" that,="" as="" shown="" in table="" 6–6.="" the="" calculation="">