Documentation Shelly Cashman Excel 2013 Chapter 2 SAM Projec

Documentation Shelly Cashman Excel 2013 Chapter 2: SAM Project 1a Flex Cab Company ADD FORMULAS AND FORMAT A REVENUE REPORT

Apply formulas, formatting, and adjustments to a revenue report in an Excel workbook for Flex Cab Company. Complete calculations for total, average, maximum, and minimum daily income, format the report with appropriate number formats, styles, colors, borders, and alignment. Create weekly revenue change formulas, conditional formatting, and orient the worksheet to landscape. Rename the worksheet to "Revenue Detail" and save the completed workbook as specified.

Paper For Above instruction

In this project, the primary goal is to accurately analyze and present revenue data for Flex Cab Company using advanced Excel functionalities. The task involves adding essential formulas, formatting data for clarity, and ensuring that computations are correct and visually appealing. These actions not only improve data readability but also facilitate better decision-making and reporting accuracy, which are critical in business environments such as transportation services.

Introduction

Flex Cab Company, operating in Toronto, Ontario, requires an effective financial reporting system to monitor weekly revenues. This report is essential for the accounting department, particularly for assessing income patterns, making informed operational decisions, and preparing financial statements. The task emphasizes the importance of mastery in Excel formulas, formatting techniques, and report presentation skills essential for accounting and financial analysis.

Data Preparation and Formula Application

The first step involves preparing the worksheet for calculations. The user is instructed to work within the designated worksheet, initially selecting cell B13, then utilizing the SUM function to total values in a specific range (B6:B12). This summative calculation provides the total revenue for a segment of the week. The formula is then propagated horizontally across columns C to J using the fill handle, automating total calculations for other days or centers.

Further, average daily income calculations involve the AVERAGE function applied to the same range (B6:B12), with results copied across adjacent cells to display the daily average revenue. Likewise, the MAX and MIN functions find the highest and lowest daily income, respectively, ensuring a comprehensive view of revenue variation over the period. These formulas offer insight into the best and worst revenue days, aiding operational assessments.

Data Formatting Techniques

Data presentation is critical for clarity and professionalism. The project requires applying the Comma Style number format with two decimal places to raw revenue figures, enhancing readability by including thousand separators. The header row (B6:K6) should be formatted with the Accounting number format, utilizing the dollar sign and two decimals to conform to financial presentation standards. The total and summary rows (B13:K13) also follow similar formatting to emphasize total figures.

Additionally, date values under A6:A12 are formatted as Short Date, ensuring consistent and concise display. Centering the header row (A5:K5) aligns the labels for improved readability. The fill color for the totals row (A13:K13) is changed to yellow for visual distinction, while borders—specifically a thick top border—are applied to delineate section boundaries clearly.

Column Adjustment and Data Analysis

Column A width is adjusted to 32.00 units to accommodate date entries without truncation. Calculations for weekly revenue changes are made by subtracting last week’s total (cell B22) from the current week (cell B13). This difference formula is extended across columns C to K, providing a snapshot of revenue fluctuations across centers or periods.

Following this, the weekly revenue change is expressed as a percentage in cell B24, computed by dividing the change (B23) by the previous week’s total (B22), and then formatted as a percentage with two decimal places. To enhance data analysis, conditional formatting is applied so that any negative percentage (indicating revenue decline) is highlighted with an orange fill, instantly drawing attention to areas of concern.

Final Layout Adjustments and Presentation

The worksheet orientation is switched to Landscape, optimizing space for printing or viewing. The worksheet is renamed to "Revenue Detail" to clearly identify its purpose. After completing these modifications, the workbook is saved, closed, and prepared for submission as per instructions.

Conclusion

This project encapsulates essential Excel skills such as formula creation, data formatting, visual styling, and worksheet management. Mastery of these techniques enables the production of professional, accurate, and visually clear financial reports vital for operational assessment and strategic planning in a business like Flex Cab Company. Proper execution of these tasks exemplifies the ability to utilize Excel as a powerful tool for business analysis.

References

• Walkenbach, J. (2013). Microsoft Excel 2013 Plain & Simple. Wiley.
• Gaskin, J. (2014). Microsoft Excel Data Analysis and Business Modeling. Microsoft Press.
• Chamberlain, M. (2014). Microsoft Excel 2013 VBA Programming. Pearson Education.
• Ross, S. A., Westerfield, R. W., & Jordan, B. D. (2013). Fundamentals of Corporate Finance. McGraw-Hill Education.
• Higgins, R. C. (2012). Analysis for Financial Management. McGraw-Hill Education.
• Heizer, J., Render, B., & Munson, C. (2016). Operations Management. Pearson.
• Shim, J. K., & Siegel, J. G. (2012). Financial Accounting. McGraw-Hill Education.
• Gordon, L. A., & Natarajan, S. (2014). Fundamentals of Financial Management. South-Western College Pub.
• Microsoft Office Support. (2023). Create formulas in Excel. Microsoft Support. https://support.microsoft.com/excel
• Excel Easy. (2023). Excel formulas and functions. https://www.excel-easy.com