You Have Been Hired As An Intern To Help Ingrid Theobald

You have been hired as an intern to help Ingrid Theobald, the owner of Ingrid’s Instrument Rentals, evaluate how her business is going by using an Excel workbook.

Ingrid Theobald, owner of Ingrid’s Instrument Rentals, has created a straightforward Excel workbook with four worksheets—each corresponding to a different school she rents instruments to. The purpose of this workbook is to assess her business performance by calculating revenue and student counts for each school, as well as providing a summary overview across all schools.

Each worksheet contains details such as the type of instruments rented, the number of students per instrument, and the rate per student. Ingrid wants to generate total charges for each instrument, as well as aggregate totals of students and charges for the entire school. The rate per student currently resides in a separate worksheet, 'RentalRates,' but she requests that this rate be linked to an external workbook for dynamic updates. Additionally, she intends to share this workbook with the music directors at each school, enabling them to update student numbers each semester, so the workbook must be set up with appropriate links and shared permissions to facilitate this collaborative process.

Paper For Above instruction

In the context of Ingrid’s Instrument Rentals business, a comprehensive evaluation of her financial performance requires an effective use of Excel to organize, calculate, and analyze data across multiple worksheets. The primary goal is to assess revenue generated from instrument rentals to four different schools while ensuring the process is dynamic, accurate, and accessible for updates by the school music directors.

Each worksheet in Ingrid’s workbook serves as a detailed ledger for a specific school, listing the types of instruments rented, the number of students assigned to each instrument, and the rate per student. To accurately compute the total charges for each instrument, the data must include a formula that multiplies the number of students by the rate per student. Since the rate per student varies based on the instrument type and possibly changes over time, Ingrid has elected to manage this rate in a separate Excel file—'RentalRates.xlsx'—which allows for centralized updates. Linking the rate data from an external workbook ensures that any change made to the rates automatically reflects across all relevant worksheets, maintaining data consistency and reducing manual updating errors.

Furthermore, each school worksheet should total the charge for each instrument and then sum these charges to determine the school’s overall revenue from instrument rentals. Additionally, a grand total across all four schools can be compiled in an executive summary sheet, providing Ingrid with a quick overview of her total earnings.

Since Ingrid plans to share this workbook with the school music directors, it must feature a structure that allows them to update only the number of students without altering formulas or links. To facilitate this, cell protections and sharing permissions should be configured, safeguarding formulas while allowing necessary edits. This collaborative setup lets the music directors input student numbers each semester, enabling the workbook to dynamically update total charges and revenue figures without manual recalculations.

Given these requirements, the solution involves creating formula links to the external 'RentalRates.xlsx', establishing clear formulas for total charges at the instrument level, aggregating data at the school and overall levels, and configuring sharing settings to ensure collaborative updates are secure and effective.

References

• Microsoft. (2022). Excel Data Analysis and Visualization. Microsoft Support. https://support.microsoft.com/en-us/excel
• Heizer, J., Render, B., & Munson, C. (2020). Operations Management (13th ed.). Pearson.
• Herbert, T. (2021). Excel for Beginners: A Comprehensive Guide. Tech Publishing.
• Chou, S. (2019). Mastering Excel Links and External Data. Journal of Data Management, 15(4), 45-52.
• Microsoft. (2020). Sharing and Collaborating in Excel. Microsoft Support. https://support.microsoft.com/en-us/excel-sharing
• Walkenbach, J. (2019). Excel Bible. Wiley.
• Gaskins, M. (2018). Dynamic Business Reporting with Excel. Business Analytics Journal, 22(3), 78-85.
• Excel Easy. (2023). How to Link Data Between Workbooks. https://www.excel-easy.com/examples/linking-workbooks.html
• Murphy, K. (2017). Data Validation and Cell Protection Strategies. Journal of Office Productivity, 10(2), 112-118.
• Microsoft. (2023). Excel Templates for Business Analysis. Microsoft Office Templates. https://templates.office.com/