create a spreadsheet

Step 1: Adding a Documentation Sheet

  1. Insert a new spreadsheet into the workbook.
  2. Change the tab name from sheet 2 to Documentation. The documentation sheet should be moved to the first sheet in the workbook.
  3. Include your name, the date, and the purpose of the spreadsheet.
  4. Make sure you include block color formatting and text wrapping as needed. Your documentation sheet should look something like the one below.
Image showing block color formatting and text wrap
Documentation Sheet

Step 2: Adding Calculations (Formulas)

Now we need to begin working on the worksheet with all of the data.

  1. Create the formulas needed to calculate the following for each item.Column N: Total Quantity Sold = Sum of all Daily Sales
    Column O: Net Selling Price = Selling Price * (1 – Sales Discount)
    Column P: Total Sales = Total Quantity Sold * Net Selling Price
    Column Q: Total Profit = Total Sales – (Cost per item * Total Quantity Sold)
    Column R: Average Profit % = Total Profit / Total Sales
  2. Create a Grand Total for the Total Profit column and place it in cell B21.
  3. Create a formula to calculate the Remaining Profit needed and place it in cell B22.Remaining Profit = The Profit Goal – Total Profit (all items)

One of the strengths of Excel is its ability to make tedious and repetitious calculations for speed and to reduce errors. Like all tools, there is always the possibility that you could use it incorrectly, so with formulas, it is important to verify the formulas you enter are correct. Notice that the daily sales columns between columns C and N are hidden in the picture below.

Click on Image to Expand

Daily sales columns between columns C and N are hidden

Step 3: Formatting

Apply formatting to your spreadsheet.

Your goal is to take into consideration readability, reliability, and reusability. Remember that presentation has an impact on the viewer, and professional appearance is important for reporting data, just as personal appearance impacts perception. The goal is to make the data more readable and possibly to highlight data that might otherwise be lost in many numbers.

The minimum formatting requirements (per the Grading Rubric) are the following.

  1. Title Rows formatted as titles, centered across all columns A–R
  2. Apply text wrapping to the column headers.
  3. Make professional font size and color changes.
  4. Make use of shading and borders.
  5. Format for currency, percentage, and numeric formatting.

Click Image to Expand

Image formatted as per the bulleted list above which is based on grading rubric

Step 4: Professional Appearance (Cleanup)

Cleanup the formatting of your report, taking into account professional appearance.

The Minimum Requirement (per the Grading Rubric)

  1. Apply a color and assign a descriptive name to each tab (sheet) in the workbook.
  2. Hide the Daily Sales columns (D–M).
  3. Create a custom page header with your professor’s name and your name.
  4. Set the page orientation to landscape.

Remember, professional appearance doesn’t only promote better readability of your report, it represents you as a professional.

Click Image to Expand

Well formatted promotes readability and represents you as a professional

Finish: Save and Submit

Save your Excel file. Saving your file often is good practice (Ctrl + s).

Your Excel file should contain two worksheets.

  • Documentation
  • Sales-Data

Remember to provide a comment in the comments area explaining what you learned from completing this lab activity. Submit your completed Excel file (see the Deliverables above).

Keep this finished work, because you will be using it to begin the lab for Week 2.

Next

 
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.