Creating a report on historical education trends

Assignment Help Other Subject

Reference no: EM132265586

Assignment -

Expand Your World -

Create a solution that uses cloud or web technologies by learning and investigating on your own from general guidance.

Analyzing and Graphing Development Indicators -

Note - To complete this assignment, you will be required to use the Data Files. Please contact your instructor for information about accessing the required files.

Instructions: You are working as part of a group creating a report on historical education trends on the developing nation of Mali, comparing three related development indicators concerning school enrollment over time. Your task is to format the worksheet containing the historical data, chart the historical education indicators, and make the chart available to your group using OneDrive. Run Excel and then open the workbook, Expand 3-1 Education Indicators.

Perform the following tasks:

1. Save the workbook using the file name, Expand 3-1 Education Indicators Charted.

2. Format the worksheet using techniques you have learned to present the data in a visually appealing form.

3. Create charts that present the data for each of the three indicators. Think about what interested you in these indicators in the first place, and decide which chart types will best present the data. (Hint: If you are not sure which types to use, consider selecting the data and using the Recommended Chart button to narrow down and preview suitable choices.) Format the charts to best present the data in a clear, attractive format.

4. Give each worksheet a descriptive name and color the tabs using theme colors. Reorder the sheets so that the data table appears first, followed by the charts.

5. If requested by your instructor, export the file to OneDrive.

6. Submit the revised workbook as specified by your instructor.

7. Justify your choice of chart types in Step 3. Explain why you selected these types over other suitable choices.

In the Lab -

Design, creates modify and/or use a workbook following the guidelines, concepts, do skills presented in this module.

Lab 1 - Eight-Year Financial Projection

Problem: Your supervisor in the finance department at August Online Technology has asked you to create a worksheet for the flagship product that will project the annual gross margin, total expenses, operating income, income taxes, and net income for the next eight years based on the assumptions in Table. The desired worksheet is shown in Figure 3-85 (see in attached file).

Table - August Online Technology Financial Projection Assumptions
Units Sold in Prior Year	235,411
Unit Cost	$150.00
Annual Sales Growth	3.25%
Annual Price Increase	3.00%
Margin	29.90%

Perform the following tasks -

Instructions Part 1 -

Run Excel, open a blank workbook, and then create the worksheet.

1. Apply the Parallax theme to the worksheet.

2. Enter the worksheet title August Online Technology in cell A1 and the subtitle Eight-Year Financial Projection for product X in cell A2. Format the worksheet title in cell A1 to 26-point and the worksheet subtitle in cell A2 to 16-point. Enter the system date in cell I2 using the NOW function. Format the date to the 14-Mar-12 style.

3. Change the following column widths: A = 24.00 characters; B through I = 14.00 characters. Change the heights of rows 7, 15, 17, 19, and 22 to 18.00 points.

4. Enter the eight column titles Year 1 through Year 8 in the range B3:I3 by entering Year 1 in cell B3 and then dragging cell B3's fill handle through the range C3:I3. Format cell B3 as follows:

a. Increase the font size to 12.

b. Center and italicize it.

c. Angle its contents 45 degrees.

5. Use the Format Painter button to copy the format assigned to cell B3 to the range C3:I3.

6. Enter the row titles, as shown in Figure 3-85, in the range A4:A19. Change the font size in each A7, A15, A17, and A19 to 14-point. Add thick bottom borders to the ranges A3:I3 and A5:I5. Use the Increase Indent button (Home tab | Alignment group) to increase the indent of the row tides in cell A5, the range A8:A14, and cell A18.

7. If requested by your instructor, change the entry in row 14 by inserting your surname prior to the text, Web Services.

8. Enter the table title Assumptions in cell A22. Enter the assumptions in Table 3-9 in the range A23:B27. Use format symbols when entering the numbers. Change the font size of the table title in cell A22 to 14-point and underline it.

9. Select the range B4:I19 and then click the Number Format Dialog Box Launcher (Home tab | Number group) to display the Format Cells dialog box. Use the Number category (Format Cells dialog box) to assign the appropriate style that displays number with two decimal places and negative numbers in black font and enclosed in parentheses to the range B4:I19.

10. 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) * (1 + Annual Price In Increase). Copy cell C4 to the range D4:I4.

c. Year 1 Cost of Goods (cell B5) = Year 1 Sales * (1 - Margin). Copy cell B5 to the range C5:I5.

d. Gross Margin (cell B6) = Year 1 Sales - Year 1 Cost of Goods. Copy cell B6 to the range C6:I6.

e. Year 1 Advertising (cell B8) = 1250 + 8% * Year 1 Sales. Co cell B8 to the range C8:I8.

f. Maintenance (row 9): Year 1 = 500,000; Year 2 = 600,000; Year 3 = 440,000; Year 4 = 520,000; Year 5 = 555,000; Year 6 = 420,000; Year 7 = 390,000; Year 8 = 400,000.

g. Year 1 Rent (cell B10) = 1,000,000.

h. Year 2 Rent (cell C10) = Year 1 Rent + (6.5% *Year 1 Rent). Copy cell C10 to the range D10:I10.

i. Year 1 Salaries (cell B11) = 12% * Year 1 Sales. Copy cell B11 to the range C11:I11.

j. Year 1 Shipping (cell B12) = 3.6% * Year 1 Sales. Copy cell B12 to the range C12:I12.

k. Year 1 Supplies (cell B13) = 1.2% * Year 1 Sales. Copy cell B13 to the range C13:I13.

l. Year 1 Web Services (cell B14) = 85,000.

m. Year 2 Web Services (cell C14) = Year 1 Web Services + (6% * year 1 Web Services).Copy cell C14 to the range D14:I14.

n. Year 1 Total Expenses (cell B15) = SUM(B8:B14). Copy cell B15 to the range C15:I15.

o. Year 1 Operating Income (cell B17) = Year 1 Gross Margin - Year 1 Total Expenses. Copy cell B17 to the range C17:I17.

p. Year 1 Income Tax (cell B18): If Year 1 Operating Income is less than 0, then Year 1 Income Tax equals 0; otherwise Year 1 Income Tax equals 33% * Year 1 Operating Income. Copy cell B18 to the range C18:I18.

q. Year 1 Net Income (cell B19) = Year 1 Operating Income - Year 1 Income Tax. Copy cell B19 to the range C19:I19.

r. In cell J4, insert a column sparkline chart (Insert tab | Sparklines group) for cell range B4:I4.

s. Insert column sparldine charts in cells J5, J6, J8:J15, and J17:19 using ranges B5:I5, B6:I6, B8:I8 - B15:I15, and B17:I17 - B 19:I19 respectively.

11. Apply the Accounting number format with a dollar sign and two decimal places to the following ranges: B4:I4, B6:I6, B8:I8, B15:I15, B17:I17, and B 19:I19. Apply the comma style format to the following ranges: B5:I5 and B9:I14. Apply the Number format with two decimal places and the 1000 separator to the range B18:I18.

12. Change the background colors, as shown in Figure 3-85. Use Blue, Accent 1, Lighter 40% for the background colors.

13. Save the workbook using the file name, Lab 3-1 August Online Technology Eight-Year Financial Projection.

14. 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.

Instructions Part 2 -

Create a chart to present the data, shown in Figure 3-86. If necessary, run Excel and open the workbook Lab 3-1 August Online Technology Eight-Year Financial Projection.

1. Use the nonadjacent ranges B3:I3 and B19:I19 to create a Stacked Area chart (Hint: use the Recommended Charts button). When the chart appears, click the Move Chart button to move the chart to a new sheet.

2. Change the chart title to Projected Net income.

3. Use the Chart Elements button to add a vertical axis title. Edit the axis title text to read Net Income. Bold the axis title.

4. Change the Chart Style to Style 4 in the Chart Styles Gallery (Chart Tools Design tab | Chart Styles group). Use the 'Chart Quick Colors' button (Chart Tools Design tab | Chart Styles group) to change the color scheme to Monochromatic, Color 5.

5. Rename the sheet tabs Financial Projection and Net Income Chart. Rearrange the sheets so that the worksheet is leftmost and change the tab colors to those of your choosing.

6. Click the Financial Projection tab to return to the worksheet. Save the workbook using the same file name (Lab 3-1 August Online Technology Eight-Year Financial Projection) as defined in Part 1.

Instructions Part 3 -

Use Goal Seek to analyze three different sales scenarios. If necessary, open the workbook Lab 3-1 August Online Technology Eight-Year Financial Projection.

1. Divide the window into two panes 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 3-10, analyze the effect of changing the annual sales growth (cell B25) and annual price increase (cell B26) on the net incomes in row 19. Record the answers for each case and submit the results in a form as requested by your instructor.

Table 3-10 August Online technology Alternative Projections
Case	Annual Sales Growth	Annual Price Increase
1	4.25%	2.00%
2	2.25%	3.00%
3	1.25%	4.00%

2. Close the workbook without saving it and then reopen it. Use the 'What-If Analysis' button (Data tab | Forecast grout)) to goal seek. Determine a margin that would result in a Year 8 net income of $1,500,000. Save the workbook with your needed changes as Lab 3-1 August Online Technology Eight-Year Financial Projection GS. Submit the workbook with the new values or the results of the goal seek as requested by your instructor.

3. How would you use what-if analysis tools to determine what Annual Sates Growth you would need to achieve in order to keep prices steady over the eight-year projection period?

Attachment:- Assignment File.rar

Reference no: EM132265586

Questions Cloud

Develop an information security plan : The risk assessment of a current production information system should be used to develop an information security plan. The risk assessment plan may be based.

With respect to advertising media choices : With respect to advertising media choices. Which is NOT true about public relations?

Computer science question : Can someone help me with this computer science question:

How big must the total buffer and slack be : Need a little bit of help with the following question in regard to computer science:

Creating a report on historical education trends : Expand Your World - You are working as part of a group creating a report on historical education trends on the developing nation of Mali

Line of message regardless of user age : By the end the program, print the following line of message regardless of user's age:

Fred currey purchased cattle from itano farms : Fred Currey purchased cattle from Itano Farms, Inc. As payment for the cattle, Currey gave Itano Farms worthless checks in the amount of $50,250.

How do the cultures beliefs relate to values : How do the culture's values relate to the conflict? How do the cultures beliefs relate to values?

Assets from a legal perspective : What would you advise your company to write into the SLA to protect your assets from a legal perspective? What are some examples of security you wouldn't farm

User Account

All Pages