EX19_DSP_Integrated_Business

Step

Instructions

Points Possible

1

You want to store the shareholders’ information in the database. Since you already have the shareholders’ mailing information in an Excel workbook, you import the data into the Shareholders table. You add data to some of the fields, and then use the Input Mask Wizard to standardize some fields. Start Access. Download and open the file named 02i_HealthyLiving.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

Import the data from Sheet1 of the 02i_Shareholders.xlsx Excel file into a new table. Use the column headings from the Excel file, and let Access add the primary key. Save and name the table as Shareholders.

2

3

Change the Data Type for Phone to Short Text. Create an Input Mask for Phone using the default settings. Accept the underscore as the placeholder and store the data without the symbols in the mask. View the data in the Shareholders table in Datasheet view and adjust column widths so all data is visible. Save and close the table.

3

4

You create a query in Access to locate the Virginia shareholders and export the results of the query to an Excel file. You’ll use this data later when you write a letter to select shareholders using Mail Merge. Create a query using all the fields except for ID and Phone from the Shareholders table. Use State criteria to find all shareholders living in Virginia. Sort the results in Ascending order based on Last. Name the query VA Shareholders. Run, save, and then close the query.

3

5

Export the VA Shareholders query to an Excel file. Export data with formatting and layout. Save the workbook and name it 02i_VaShareholders_LastFirst.xlsx. Do not save the export steps. Close Access.

3

6

Your executive assistant has collected enough financial data to create the income statement for the current year. You use the data to project the income data for the next five years and create a chart to better illustrate the trends. You format the worksheet to give it a more professional look. Start Excel. Download and open the file named 02i_IncomeStatement.xlsx. Save the file to the location where you are storing your files.

0

7

Enter the following formulas in the corresponding cells: • In cell B8: Gross profit = Sales − Cost of goods sold • In cell B13: Total operating expenses = Selling expenses + Administrative expenses • In cell B17: Income before taxes = Operating income − Interest expense • In cell B20: Net income after taxes = Income before taxes − Income tax expense • In cell B23: Earnings per share = Net income after taxes / Total # of outstanding shares Copy the cell contents in cells B8, B13, B17, B20, and B23 to the corresponding cells in columns C:G (Ignore the #DIV/0! error for now).

4

8

Use absolute references and the projected rates in B26:B33 to project the financial figures for Year 2019 in cells C6, C7, C11, C12, C15, C16, C18, and C22. Copy the cell contents in the range C6:C22 to the corresponding cells in columns D:G.

4

9

Rename the worksheet tab to Income Statement and change the worksheet tab color to Red. Apply the Retrospect theme to the worksheet.

2

10

Merge & center cell A1 to the range A1:G1 and then apply the Title Cell Style and Bold. Use Format Painter to apply the same format to cell A2. Merge & center cell A3 to the range A3:G3 and then apply the Heading 2 Cell Style. Apply the Heading 4 Cell Style to the range A6:A33. Apply the Total Cell Style to ranges B8:G8, B13:G13, and B20:G20.

3

11

Create a footer with your first name and last name on the left side, the sheet name element in the center, and file name element on the right side of the worksheet.

2

12

Change orientation to Landscape, top and bottom margins to .5”, left and right margins to .25”. Change the scale to 95%. Center the worksheet horizontally on the page. Preview the worksheet to ensure that the whole worksheet fits on one page.

3

13

Select the content in ranges A5:G5 and A23:G23 and insert a 3-D Clustered Column chart that shows the current and five-year projected Earnings per share. Apply the Style 3 Chart Style. Change the chart title to Current and 5-Year Projected EPS. Add data labels with the EPS values above each bar.

3

14

Move the chart to a new chart sheet (following the Income Statement worksheet). Name the tab as EPS Column Chart. Save and close the workbook.

2

15

Your company periodically mails out newsletters to shareholders. Your assistant has written a draft of the cover letter and the current issue of the company’s newsletter. You format the letter and the educational newsletter and financial data before sending them to shareholders. Start Word. Download and open the file named 02i _Newsletter.docx. Save the file to the location where you are storing your files.

0

16

Apply the Retrospect theme and a DRAFT 1 watermark to the document. Replace [Student First Name Last Name] that displays after Sincerely yours, with your name.

3

17

Click to the left of the words Educational Information on the first page. Insert a Next Page Section Break. Apply the Title style to the section heading Educational Information. Modify the Title style to use Times New Roman, Bold, Olive Green, Text 2, and Align Center.

3

18

Apply the Heading 1 style to the following text in the document: • Introduction • Understanding Financial Statements • Understanding Financial Ratios • Summary • References

2

19

Apply Heading 2 style to the following headings: • Balance Sheet • Income Statement • Statement of Cash Flows • Liquidity Ratios • Leverage • Rates of Return • Stock Market Ratios

1.75

20

Modify the Heading 1 and 2 styles to use Times New Roman, Bold, and center aligned.

2

21

Insert two Next Page Section Breaks on the blank line above the Understanding Financial Ratios heading. Change the layout of the blank page (Page 4) to Landscape.

2.25

22

Open 02i_IncomeStatement.xlsx using Excel. Select the range A1:G23 from the Income Statement worksheet and copy and paste it as a picture to the new blank page in the newsletter that you just formatted to landscape layout. Close the workbook and close Excel.

3

23

Position the insertion point to the left of Sincerely yours on the cover letter (Page 1). Insert a Table of Contents with the Automatic Table 1 format. Delete the word Contents (leave the end of paragraph mark). Save the document.

2

24

Place the insertion point before the period at the end of the first sentence in the Introduction section on Page 2. Add the following footnote: The materials in this newsletter are summarized from the sources listed in the References section of this document. (include the period). Modify the footnote style to use Times New Roman font.

2

25

Position the insertion point on Page 1, insert the Integral style footer. Format the page number to start at 0. Apply Times New Roman font to the footer. Check the Different First Page box so that the page numbering begins on Page 2.

3

26

Go to the References section on the last page of the newsletter, apply bullets to the six sources and insert hyperlinks to the six URLs. Save the document.

2

27

You conduct a mail merge to prepare a mailing to the shareholders residing in Virginia by merging the newsletter you have prepared with a data source that you had created using Access.

Position the insertion point to the left of [Address Block] at the top of the document. Begin a new mail merge, creating letters using the current document. The data source is the 02i_VaShareholders_LastFirst.xlsx Excel file.

0

28

Edit the recipient list to include only the two recipients with the last names of Jones and Williams.

2

29

Replace [Address Block] with the Joshua Randall Jr. Address block. (Don’t delete the blank line). Insert the <<First>> merge field, replacing [first name] in the salutation. (Don’t delete the comma).

2

30

You will preview the results and view the document as addressed to one recipient; do not finish and merge the document at this time. Preview the results to ensure only two Virginian shareholders are receiving the letter. Ensure that the second recipient (Kwasi) is previewed. The document contains eight pages, with a cover letter and the newsletter. Save the new document as 02i_MergedLetter and close all documents.

2

31

You create the PowerPoint presentation from a blank using an outline that your assistant created for you. You include financial data from the worksheet you updated earlier and format the presentation, so it looks professional. Start PowerPoint. Download and open the blank presentation named 02i_Finance.pptx. Save the file to the location where you are storing your files. Apply the Retrospect theme with the default variant.

2

32

Enter the following text into the placeholders on the title slide: Title placeholder: Healthy Living Motorcycles, Inc. Change the font size to 60. Subtitle placeholder: Presented By: FirstName LastName.

2

33

Create Slide 2 using the Title and Content layout and add Agenda as the slide title. Add the following content. Format the bullets as Star Bullets. Understanding Financial Statements Understanding Financial Ratios Comparison of EPS over Five Years

5

34

Insert a new slide using the Title Only layout and add Any Questions? as the title. Center the title.

2

35

Select Slide 2 and import the downloaded 02i_Outline.docx file into the presentation.

3

36

Insert the downloaded 02i_Figure1.jpg on the Title Slide. Change the height to 1.5”. Position it horizontally at 10.55” from the top left corner and vertically at 0.20” from the top left corner.

3

37

Create a slide footer that displays the current date, the slide number, and your full name on each slide, except the title slide.

2

38

Open the 02i_IncomeStatement.xlsx Excel file. Select and copy the range A1:G23 from the Income Statement worksheet tab and paste it keeping Source formatting on Slide 3. Change the table height to 5” and width to 7”. Apply an Outside border to the table. Reposition the table as needed so it fits nicely on the slide.

3

39

Insert a new slide with the Title Only layout after Slide 8. Type Comparison of EPS over Five Years as the slide title. Copy and then paste and embed the EPS Column Chart from the 02i_IncomeStatement Excel file to Slide 9.

3

40

Remove the Chart Title. Change the chart height to 4.8” and position the chart in the middle of the slide content area.

2

41

On Slide 2, create hyperlinks to the respective slides: Slide 3: Understanding Financial Statements<br>Slide 4: Understanding Financial Ratios<br>Slide 9: Comparison of EPS over Five Years

3

42

Apply the Wipe transition to all slides. Apply the Fly In animation to the bullets on Slide 2 and set the timing to After Previous. Set the duration to 01.50 and delay of 00.50. Save and close the presentation.

3

43

Reopen the 02i_MergedLetter.docx. At the end of the document, in the blank line, insert the file 02i _Finance.pptx as an embedded object that will display the PowerPoint presentation in the Word document. (Hint: Click Object in the Text group on the Insert tab, and then select Object. On the Create from File tab, Browse to locate the file, confirm that the Display as icon option is not selected, and then click OK.) Press ENTER. In the next empty paragraph, insert the file 02i_IncomeStatement.xlsx as an embedded object that will display the Excel workbook in the Word document. Resize the embedded Excel object so that it fits on the page. Press ENTER. In the next empty paragraph, insert the file 02i _HealthyLiving.accdb as an embedded object that will display the Access database in the Word document. Save the Word document. Close Word, and submit the 02i_MergedLetter.docx as directed.

1

Total Points

100