Create A Spreadsheet Using The Following Information ✓ Solved

Create a spreadsheet using the following information. You are

Create a spreadsheet to display the sales figures for the first year of operation of your fruit store. Enter the raw data below, applying various presentation features (Font, Font Size, Font Color, Number Formats, Cell Shading, Text Rotation, etc.) to it as you wish. Apply appropriate number formats to your numbers. Center your spreadsheet horizontally on the page. Give your spreadsheet an appropriate title and center it across your spreadsheet. Select the best page orientation for your spreadsheet. Adjust the column width and row height to suit the layout you have selected. Create formulas to calculate totals for each month. Create formulas to calculate totals for each fruit item. Setup an appropriate header for this spreadsheet. Setup a page number for this spreadsheet and place it in the footer.

Sales Data:

  • Apples: January 2298, February 3512.56, March 4929.67, April 5883, May 6237.77, June 6566.78, July 6213.88, August 6001, September 5799.69, October 5527, November 3914.55, December 2564.99.
  • Bananas: January 6899.21, February 6755.33, March 6541, April 6032.79, May 5822.72, June 5968, July 6333.33, August 6544.11, September 6845.45, October 7000.01, November 7216.27, December 7283.
  • Oranges: January 4923.88, February 4444.99, March 3851, April 3399.88, May 3020.03, June 3411.89, July 3567.09, August 3999.91, September 4255.88, October 4873, November 5214.95, December 5521.17.
  • Kiwifruit: January 3349.12, February 3899.87, March 4336, April 4892.01, May 5217.97, June 4764.10, July 4523.22, August 4501, September 4219.91, October 3877.49, November 3712.12, December 3555.56.
  • Pears: January 3310, February 4524.65, March 5992.76, April 6961.44, May 7447, June 7583.87, July 7393, August 7110.10, September 6637.96, October 6275, November 4841.71, December 3456.11.
  • Grapes: January 4847.23, February 5361.58, March 5899.24, April 6853.01, May 5471.34, June 4534.22, July 4222.83, August 4137, September 3998, October 3556.36, November 3111.44, December 2789.74.
  • Peaches: January 5902.44, February 7234.12, March 5110, April 3521.87, May 1276.34, June 1227.30, July 1199.99, August 1242.09, September 1189.73, October 1195.42, November 1213.14, December 1887.49.
  • Nectarines: January 5310, February 7772.71, March 8219, April 6989.33, May 4535.52, June 1873.38, July 1198, August 1241.63, September 1187.57, October 1222.21, November 1432.43, December 2137.78.

Name: Virak Soa, Vibol, Theary, Sokha.

Using the IF function, determine the student grade based on the following rules: Total score >= 90 then grade A, 90 > Total score >= 80 then grade B, 80 > Total score >= 70 then grade C, 70 > Total score >= 60 then grade D, 60 > Total score >= 50 then grade E, Else then grade F. Also, display the word "Pass" or "Fail" under the Description column (students who pass need to have total score greater than or equal to 50).

Paper For Above Instructions

The purpose of this paper is to guide you on how to construct a spreadsheet that displays the sales data of a fruit store over the course of the first year. This involves not only entering the raw data correctly but also applying various formatting and calculations to ensure the spreadsheet is both functional and presentable.

Step 1: Data Entry

Begin by entering the sales data for each fruit and each month in a structured manner. It's important to separate the data clearly for each fruit type and month to avoid confusion. Use the data provided to create a table that lists fruits in the first column and months in the first row.

Step 2: Formatting

Once the data is entered, you will want to apply various formatting features. Start by selecting a clear font and adjusting the font size to ensure legibility. Use font colors to highlight key figures, such as total sales. Number formats should be applied to financial figures to ensure they are represented in a currency format, making it easy for viewers to understand. Cell shading can also be used to differentiate between different fruits, using light colors to keep the spreadsheet easy on the eyes.

Step 3: Centering and Page Orientation

Next, ensure that the spreadsheet is centered horizontally on the page. This involves adjusting the page layout settings in your spreadsheet software. The best orientation for this type of data might be landscape, as it allows for more horizontal space for your fruit columns.

Step 4: Adjusting Sizes

Adjust the column width and row height according to the data displayed. This step ensures that all text and numbers are visible without unnecessary wrapping, which can make the spreadsheet harder to read. Aim for a balance that allows for clarity and space efficiency.

Step 5: Calculating Totals

To get a comprehensive view of the sales figures, create formulas that calculate the total sales for each month and for each type of fruit. For example, use the SUM function to add the total sales for each fruit across all months. This is crucial for analyzing which fruits perform best over the year.

Step 6: Spreadsheet Header and Footer

Set up an appropriate header that includes your company’s name and possibly the title of the report. Ensure the title is centered across the spreadsheet to make it more prominent. Finally, set up a footer that includes page numbers to help keep track of printed copies of the spreadsheet.

Step 7: Student Grade Calculation

If the task involves determining student grades, implement the IF function in your spreadsheet. Create a corresponding section where you list student names alongside their total scores. Use the defined grading scale to calculate and display the grade next to each student’s name. In addition, create another column to indicate "Pass" or "Fail" based on student scores.

Conclusion

Creating a comprehensive and well-formatted spreadsheet requires attention to detail and an understanding of the various functions available in spreadsheet software. By following the outlined steps, you can produce a professional and effective sales report for your fruit store that will not only showcase your yearly sales but will also allow for easy analysis of the data.

References

  • Excel Functions. (2023). Microsoft Excel Help and Learning.
  • Spreadsheet Basics. (2023). TechRepublic.
  • Formatting Numbers in Excel. (2023). Exceljet.
  • Creating and Editing Spreadsheets. (2023). GCF Global.
  • How to Create a Perfect Excel Spreadsheet. (2023). Investopedia.
  • Using the IF Function. (2023). Excel Easy.
  • Excel Tips and Tricks. (2023). Spreadsheeto.
  • Page Layout in Excel. (2023). Vertex42.
  • Visual Formatting in Excel Spreadsheets. (2023). Smartsheet.
  • Understanding Excel Formulas. (2023). Excel Campus.

Related Assignments