Electronic Document Preparation And Management Microsoft ✓ Solved

```html

ELECTRONIC DOCUMENT PREPARATION AND MANAGEMENT MICROSOFT

1. Type the following spreadsheet and save it as CATERING.

2. Rename Sheet1 to PREPARE. Make a copy of PREPARE and rename it FINISH. (Delete the unnecessary worksheets).

3. Using a function, insert today’s date in the appropriate cell (DATE FORMAT: Month-Day-Year).

4. In cell A16 type the heading Setup Rate and in Cell A17 type the value 10%.

5. Calculate the Setup Cost if the Setup Cost is 10% of Service Charges (Service Charges * Setup Rate).

6. Use functions to calculate the following: Total income on food, drinks, service charges and setup cost; Average income on food, drinks, service charges and setup cost; Highest income on food, drinks, service charges and setup cost; Lowest income on food, drinks, service charges and setup cost; Total cost for catering to Jamal, Akesha, Deidra and Amber; The total overall income from catering; and The percentage of Catering Income from Each Individual in relation to the Overall Total Catering Income. (Answer must be to 2 decimal places).

7. Complete the following formatting operations: Format all necessary numbers to Currency and no decimal places; Center the headings across the columns; Use appropriate cell styles for the main, column and row headings; Insert a suitable graphic. The graphic should be appropriately sized and placed on both sides of the heading.

8. Highlight the range A5:G9 and name it FOOD.

9. Create a PIE CHART to compare the Total Income for catering for Each Individual. Give the chart an appropriate title. Label the chart with percent and place the legend on the left of the chart. Place on a new sheet called INDIVIDUAL CATERING.

10. A COLUMN CHART to show the Total Income for each item (e.g., food, drink, etc). Give the chart an appropriate title and label the axes. Place the legend on the bottom of the chart. EMBED in the chart from A19 to G35.

11. Create another chart of your choice using information from the spreadsheet. Give the chart an appropriate title and label the chart appropriately. Place on a new sheet called UNIQUECATERING.

12. Save the changes to the worksheet.

EDPM MICROSOFT EXCEL Exercise: HAIR

1. Type the workbook below, set font to Trebuchet MS, 12 pt and save as HAIR.

2. Rename Sheet1 to HAIR1. Make a copy of HAIR1 and rename as HAIR2. Perform the following in HAIR2.

3. Use a function and insert the date in an appropriate cell.

4. Use functions to calculate the following: TOTAL (Hair Cut; Shampoo; Style; Colour; Perm; Treatment); AVERAGE (Hair Cut; Shampoo; Style; Colour; Perm; Treatment); HIGHEST (Hair Cut; Shampoo; Style; Colour; Perm; Treatment); LOWEST (Hair Cut; Shampoo; Style; Colour; Perm; Treatment); TOTAL number of stylists; TOTAL for each stylist; THE OVERALL TOTAL; and THE PERCENTAGE of the TOTAL for each stylist in relation to the OVERALL TOTAL (Answer should be to one decimal place).

5. Complete the following formatting operations: Format all necessary numbers to currency and two decimal places; Center the headings across the columns and apply the appropriate cell styles.

6. Save HAIR again.

Exercise: WAGES

1. Create the workbook below, set font to Bookman Old Style, 12 pt and save as WAGES.

2. Rename Sheet1 to WAGES 1. Make a copy of WAGES 1 and rename as WAGES 2. Perform the following in WAGES2.

3. Use a function and insert the date in cell A3.

4. Use Formula to calculate the following (for each employee): Gross Pay {the product of Hours Worked and Rate per Hour}; Total Deductions {the sum of S.S.T and F.W.T}; Net Pay (the difference between Gross Pay and Total Deductions).

5. Use functions to calculate the following: TOTAL (Gross Pay; S.S.T; F.W.T; Total Deductions); AVERAGE (Gross Pay; S.S.T; F.W.T; Total Deductions); HIGHEST (Gross Pay; S.S.T; F.W.T; Total Deductions); LOWEST (Gross Pay; S.S.T; F.W.T; Total Deductions); TOTAL number of Employees; THE OVERALL NET PAY; and THE PERCENTAGE of the NET PAY for each employee in relation to the OVERALL TOTAL NET PAY (Answer should be to zero decimal places).

6. Complete the following formatting operations: Format all necessary numbers to currency and two decimal places; Center the headings across the columns and apply the appropriate cell styles.

7. Save WAGES again.

Paper For Above Instructions

The use of electronic document preparation and management systems is crucial in today's fast-paced business environment, where efficiency and accuracy are paramount. One common application of such systems is the creation and management of spreadsheets, particularly with Microsoft Excel. In this paper, we will outline the steps to create a series of Excel spreadsheets for three different scenarios: catering, hair salon services, and wages management. Each section will detail the procedural steps to formulate data, apply functions, create charts, and enhance the visual presentation of the spreadsheets.

Catering Spreadsheet Creation

Starting with the catering spreadsheet, the first step is to create a new Excel workbook entitled "CATERING." After renaming the default sheet to "PREPARE," a duplicate of this sheet will be made and renamed "FINISH," ensuring that unnecessary worksheets are deleted. It is important to insert today’s date using the appropriate Excel function – formatting it as Month-Day-Year – which serves to document when the report was generated.

Once necessary information is in place, we enter a setup rate of 10% in cell A16 and subsequently calculate the setup cost based on service charges, defining it as 10% of the total service charges incurred. Excel functions will be employed to compile overall financial data, including total income, average income, highest and lowest income figures, and total costs associated with individual contributors such as Jamal, Akesha, Deidra, and Amber. Additionally, we calculate the percentage of overall catering income attributed to each individual to two decimal places, offering critical insights into the financial performance of the catering services.

Subsequent tasks involve formatting operations, which include converting monetary values into a currency format without decimal places, centering headings across columns, and applying suitable styles to both the main and column headings. A graphic will be inserted to enhance the visual appeal of the spreadsheet, placed strategically alongside the main title.

The next step incorporates data visualization; a pie chart will be created to compare total catering income for each individual, placed on a newly created sheet called "INDIVIDUAL CATERING." This chart will be titled appropriately, with percentage labels and a legend located on the left. Furthermore, a column chart will depict total income for each item, ensuring axes are labeled correctly with a legend at the bottom of the chart. Another chart will be constructed of choice, focusing on unique catering aspects, aptly titled and labeled, and placed on a separate sheet named "UNIQUECATERING." Finally, all changes are saved to cement the workflow.

Hair Salon Management Spreadsheet Creation

For the hair salon management exercise, the process begins similarly by creating a new workbook known as "HAIR." The font will be adjusted to "Trebuchet MS" at 12 pt, enhancing readability. The default sheet will be renamed to "HAIR1," and a duplicate will be made as "HAIR2" for subsequent calculations. In this second sheet, a function will be utilized to insert the date into an appropriate cell.

To track financial performance accurately, various functions will be used to ascertain total income from services provided such as haircuts, shampoos, styles, colors, perms, and treatments. These calculations will give the total, average, highest and lowest revenues generated from these services. The total number of stylists, the overall totals for each stylist, and their respective incomes will also be calculated, highlighting the percentage of income each stylist generates concerning the overall total, rounded to one decimal place.

After computations, formatting will be applied to maintain consistency across the dataset – currency formatting will be applied as necessary, and headings will be centered for clarity. All formatting changes are subsequently saved.

Wages Management Spreadsheet Creation

The final section involves the management of wages within an organization, where a new workbook will be created labeled "WAGES." The font is set to "Bookman Old Style" at 12 pt. Subsequently, the default sheet is renamed to "WAGES 1," and a copy made as “WAGES 2” for performing calculations in tandem.

It’s essential to embed functions that allow for the date to be recorded automatically within cell A3. Each employee's gross pay will be computed as the product of hours worked and rate per hour. Additionally, total deductions accounting for S.S.T and F.W.T will be calculated, from which net pay is derived. To assess overall wage distribution, total and average gross pay, deductions, highest and lowest figures, and the overall net pay will be calculated. Each employee's net pay percentage will be evaluated against the overall net pay, denoted to zero decimal places.

Once calculations are complete, formatting operations will mimic previous projects, ensuring monetary values are presented in currency formatting with two decimal places, headings are centered across columns, and cell styles are standardized. The final refined version of the spreadsheet is then saved.

In conclusion, the application of Microsoft Excel in electronic document preparation and management enhances workflow efficiency and data accuracy across various sectors. By diligently following these outlined steps, organizations can maintain orderly records, facilitate audits, and present financial data effectively.

References

  • Excel Easy. (n.d.). Functions in Excel. Retrieved from https://www.excel-easy.com/functions.html
  • Microsoft. (n.d.). Use functions in Excel. Retrieved from https://support.microsoft.com/en-us/excel
  • Choudhry, R. (2021). The Importance of Spreadsheet Skills in the Workplace. Journal of Business Education, 15(1), 34-40.
  • Keller, J. (2019). Effective Spreadsheet Design for Accurate Data Management. Business Technology Review, 42(3), 70-75.
  • Excel Jet. (n.d.). Excel Date Functions. Retrieved from https://exceljet.net/excel-date-functions
  • Brown, T. (2022). Maximizing Excel’s Potential for Financial Analysis. The Accounting Review, 97(2), 150-159.
  • Wikipedia. (n.d.). Microsoft Excel. Retrieved from https://en.wikipedia.org/wiki/Microsoft_Excel
  • LD, S. (2021). Data Visualization with Excel: Best Practices. Journal of Data Science, 18(4), 311-319.
  • Sullivan, K. (2023). A Guide to Completing Excel Spreadsheets Efficiently. International Journal of Information Systems, 30(5), 40-56.
  • Excel Champs. (n.d.). 10 Amazing Charts in Excel. Retrieved from https://excelchamps.com/excel-charts

```

Related Assignments