I need part 3 of the attached project completed involves ms access and ms excel

Project Case: An Integrated Case of Excel & ACCESS on Boat Marina

I. Analyzing Information Needs

Molly Mackenzie Marina is a resort and hospitality company based on Lake Merewether in the Midwest. The company mainly rents cabins, a variety of watercraft, and boat slips to the visitors. Currently, daily operational data and information of the company are captured and managed with manual-based information system. It needs to develop electronic database to track and capture customer and business data and information to support its operation and decision-making. Molly Mackenzie Marina needs information regarding customers, rental property, rental reservations, and rental payments to support its regular operation and internal operational & strategic decision-making.

Customer information regarding type (across age, gender, and class type), purchase time, sales from customers etc. will enable company to understand customers purchase behavior, service preference, purchase timing, purchase frequency etc. and make strategic decisions i.e. designing offers and services, undertaking marketing strategies, providing promotional offers etc. having a customer database in system, service employee at front desk can drag (automatic synchronization) customer basic information in order processing. Rental property information including property condition (type, age etc.), property rent, occupancy status etc. will help company in strategic decision making and daily operation. Strategic decisions include property purchase decision, property modification, property replacement (based on assets age and condition) etc.. Regarding supporting to daily operation, property database information instant let to know occupancy condition of property, rental rate from the operational system module and process customers’ purchase order immediately. Rental reservation information will help company execute customers’ orders and manage services. During order taking, if property reservations information readily available to transaction module screen availability check (occupancy) can be easily made and orders can be executed very fast. Based on the reservation information logistic arrangement can be made easily for cabins, watercrafts, and boat slips. Rental reservation information will help to review the average length of stay, frequency of orders, popularity of specific category and sub-category of service etc. and make strategic decisions. Rental payment information will help company in both- strategic decision making and daily operation. Rental payment information can be synchronized to property type information to assess profitability across properties and take products/services continuation or discontinuation decisions. Rental payment information will help in collection of remaining amount due from customers when they check out.

II. Design

ACCESS Design

Information regarding average length of stay by cabin rental customers, frequency of watercrafts rented, and average length of stay by watercrafts can be obtained through MS ACCESS query. In this case, all customers’ data and information will be dragged (synchronized) from transaction processing software package of Molly Mackenzie Marina. Then multiple queries will be made to search and compile data across customer types, time of reservations of each property, and reservations time. The customer types, time of reservations of each property, and reservations time will represented in the MS ACCESS interface when queries will be performed. In these data based we will use COUNTIF Function to find average length of stay for cabin rental customers and watercrafts customers. How frequently are watercrafts rented can be reviewed by calculating average vacancy time of the watercrafts through COUNTIF Function.

EXCEL Design

Information regarding average sales and total for each product category and product within each category for June Month can be obtained and pie chart comparing the revenue by property category can be generated through MS EXCEL. Information regarding average sales and total for each product category and product within each category for June month can be generated through PIVOT Table feature. In PIVOT Table, product categories will be dragged to COLUME area, customer’s payment will be dragged to ROW categories, and month will be dragged to FILTER area to generate PIVOT Table. In the PIVOT table Information regarding average sales and total for each product category and product within each category for June can be obtained by filtering JUNE month. Pie chart comparing the revenue by property category can be generated through information with PIVOT TABLE. From the PIVOT table aggregate revenue by products can be displayed by minimizing subcategories revenue information. Then INSERT pie can be employed to generate pie chart for visual representation of revenue by property category.

III. Database and Spreadsheet Implementations

IV. Conclusion

ACCESS queries will help Marvin and Dena Mackenzie to store customers, rental property, rental reservations, and rental payments information for reference, reporting and analysis. Selected data and information can be searched and compiled based on some defined conditions (parameters) through ‘Query Design’ feature of ACCESS. This will help in further to perform specific calculation, generate solution, or create reports.

EXCEL workbook will help Marvin and Dena Mackenzie to sort, format, and present the data and information regarding customers, rental property, rental reservations, and rental payments. Several interactive features of EXCEL including functions, PivotTable, Solver, and charting tools will help to perform calculations, manage data across several dimensions in user-friendly manner, generate scenarios & find solutions involving multiple unknown variables, and represent data & information visually. Several types of MIS and data analytic reports can be generated with EXCEL to support decision marking of the company.

Additional internal and external data & information will help Marvin and Dena Mackenzie to make operational and strategic decisions for boat marina business. List of additional required internal data & information include customer age & gender, customers’ reservation mode (physical, over phone, website etc.), payment mode (cash/card/online etc.), employee service quality etc. List of additional required external data & information include weather conditions, competitors’ services & prices, regulatory requirements etc.