Question
you have to do this project on the excel. first one i level this with instructions and second one is the supporting details. (subject operation
you have to do this project on the excel. first one i level this with instructions and second one is the supporting details. (subject operation marketing management 3000)
1. Shelly Cashman Excel 2019 | Module 10: SAM Project 1a
Shelly Cashman Excel 2019 | Module 10: SAM Project 1a
ANALYZING DATA WITH POWER TOOLS AND CREATING MACROS
GETTING STARTED
Open the file SC_EX19_10a_Mac_FirstLastName_1.xlsm, available for download from the SAM website.
Save the file as SC_EX19_10a_Mac_FirstLastName_2.xlsm by changing the 1 to a 2.
If you do not see the .xlsm file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
Support_EX19_10a_Mac_Archive.xlsx
With the file SC_EX19_10a_Mac_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
To complete this project, you need to add the Developer tab. If this tab does not display, click Excel in the file menu, then click Preferences. Click Ribbon and toolbar. In the Main Tabs area of the Excel Options dialog box, click the Developer check box, then click save.
If a dialog box about enabling or disabling macros appears, click to enable macros.
PROJECT STEPS
Ravi Mehta is the finance director for the Home Security Group of Franklin National, a North American manufacturing company. Ravi is developing an Excel workbook that his managers can use to track and interpret data about customers, contracts, and invoices. He asks for your help in setting up tools to analyze the data and to automate the workbook to make it easier to use.
Go to the Home Page worksheet. Ravi wants to include a list of links to resources his managers are likely to need. However, the first link he added is unnecessary.
Remove the hyperlink from the text in cell C4.
Ravi wants to make it easy for users to navigate to the other worksheets. Add links to other places in the workbook as follows:
In cell C5, insert a link to cell A1 of the Customers worksheet.
In cell C6, insert a link to cell A1 of the Contracts worksheet.
In cell C7, insert a link to cell A1 of the Invoices worksheet.
Ravi also wants users to be able to access archived contract data as they prepare new contracts. Ravi stores the archived data in an Excel workbook. Add a link to the file as follows:
In cell C8, insert a link to the file Support_EX19_10a_Mac_Archive.xlsx.
Add the following text as a ScreenTip that explains what happens when users click the link:
Open an archive of contract data.
The Franklin National website provides information Ravi's managers use every day. In cell C9, add a link to the www.fn.example.com website.
Ravi also wants to include a way that managers can request information from the home office of Franklin National. In cell C10, add a link to the info@fn.example.com email address.
Ravi's plan is that when users click the Add Customer button in the range D3:E3, the Customers worksheet opens and Excel adds a new, blank record to the Customer table, where users can insert the new customer information. Record a macro to automate the Add Customer button as follows:
Enable all macros in the workbook (if necessary), and then display the Developer tab on the ribbon.
Record a macro stored in this workbook using Add_Customer as the name of the macro.
While recording the macro, display the Customers worksheet.
Also, while recording the macro, add a new blank record to the end of the Customer table.
Stop recording the macro.
Assign the Add_Customer macro to the Add Customer button and then run the Add_Customer macro to make sure it inserts a new record at the bottom of the Customer table on the Customers worksheet.
Ravi has already created similar macros to add a new contract record and a new invoice record. Return to the Home Page worksheet, and assign the macros to buttons as follows:
Assign the Add_Contract macro to the Add Contract button.
Assign the Add_Invoice macro to the Add Invoice button.
Go to the Customers worksheet. Ravi wants to create PivotTables that include customer, contract, and invoice data. Ravi wants to list and total each contract amount by city and signing date. Create a PivotTable that displays this information as follows:
Rename the Sheet4 worksheet, using Contracts by City as the name of the worksheet.
Create a PivotTable based on the Contract table in cell B3 of the Contracts by City worksheet.
Display the City field values as row headings.
Display the SigningDate field values as column headings.
Add the ContractAmt field to the Values area to sum the field values.
Group the signing dates to display them as years and quarters only. (Hint: Expand the year headings to display the quarter headings, if necessary.)
Remove all subtotals from the PivotTable.
Format the Sum of ContractAmt field using the Currency number format with 2 decimal places and the $ symbol.
Ravi also wants to list the paid and unpaid invoices for each month and then filter them by contract type. Create a PivotTable that displays this information as follows:
Rename the Sheet3 worksheet, using Invoices by Month as the name of the worksheet.
Create a PivotTable based on the Invoice table in cell B3 of the Invoices by Month worksheet.
Display the SigningDate field values as row headings.
Display the InvoicePaid field values as column headings.
Add the InvoiceAmt field values to the Values area to sum the field values.
Use the ContractType field as a filter.
Group the signing dates to display them as years and months only.
Format the Sum of InvoiceAmt field using the Currency number format with 2 decimal places and the $ symbol.
Ravi wants to show invoices for security plans only and want to make the PivotTable easier to interpret. Modify the PivotTable as follows to meet Ravi's requests:
Use Unpaid as the column heading in cell C4.
Use Paid as the column heading in cell D4.
Filter the PivotTable to display invoices for the Security plan and Security plan for apartment building contract types.
Return to the Customers worksheet. In the range J2:K5, Ravi wants to list the total contract amounts for three cities. Extract this information from the PivotTable on the Contracts by City worksheet as follows:
In cell K3, use the GETPIVOTDATA function to display the total contract amount for Atlanta from the PivotTable on the Contracts by City worksheet. (Hint: Type an equal sign in cell K3 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.)
In cell K4, use the GETPIVOTDATA function to display the total contract amount for College Park from the PivotTable on the Contracts by City worksheet.
In cell K5, use the GETPIVOTDATA function to display the total contract amount for Dunwoody from the PivotTable on the Contracts by City worksheet.
Save the workbook, making sure to save it as a macro-enabled workbook.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
Final Figure 1: Home Page Worksheet
Final Figure 2: Invoices by Month Worksheet
Final Figure 3: Contracts by City Worksheet
Final Figure 4: Customers Worksheet
Final Figure 5: Contracts Worksheet
Final Figure 6: Invoices Worksheet
Contract Num Contract Amt Signing Date
9111 2,000.00 2/6/2019
9112 990.00 2/18/2019
9115 2,500.00 3/1/2019
9117 1,250.00 3/1/2019
9120 3,500.00 2/19/2019
9121 8,000.00 5/3/2019
9122 2,000.00 4/14/2019
9123 9,000.00 3/22/2019
9125 5,500.00 3/25/2019
9126 5,000.00 3/11/2019
9127 1,250.00 4/17/2019
9130 5,250.00 6/11/2019
9131 8,500.00 6/3/2019
9132 1,500.00 6/22/2019
9133 1,250.00 7/8/2019
9134 2,500.00 5/28/2019
9135 1,500.00 5/28/2019
9137 8,000.00 6/22/2019
9138 4,000.00 6/25/2019
9140 8,500.00 7/27/2019
9143 10,000.00 8/11/2019
9144 5,500.00 8/19/2019
9145 5,000.00 8/23/2019
9146 1,300.00 9/2/2019
9148 4,500.00 9/23/2019
9150 11,000.00 8/27/2019
9151 8,000.00 10/5/2019
9153 1,375.00 9/17/2019
9156 950.00 9/30/2019
9157 5,500.00 9/30/2019
9160 2,000.00 11/30/2019
9161 3,800.00 12/28/2019
9162 2,000.00 1/11/2020
9163 750.00 1/20/2020
9165 8,000.00 2/3/2020
9168 1,500.00 2/7/2020
9169 5,750.00 2/24/2020
9170 3,000.00 3/3/2020
9171 7,000.00 3/11/2020
9172 6,000.00 3/11/2020
9173 5,000.00 5/5/2020
9175 10,500.00 4/8/2020
9176 2,000.00 4/8/2020
9177 3,500.00 5/17/2020
9178 3,750.00 4/25/2020
9180 1,800.00 4/14/2020
9181 1,000.00 5/10/2020
9182 5,000.00 6/1/2020
9185 2,500.00 6/23/2020
9186 10,250.00 7/11/2020
9190 2,500.00 6/24/2020
9193 3,000.00 7/25/2020
9194 2,300.00 7/26/2020
9195 4,500.00 6/29/2020
9197 2,500.00 8/21/2020
9198 3,000.00 7/15/2020
9199 1,500.00 7/25/2020
9200 1,500.00 8/8/2020
9203 12,000.00 8/17/2020
9204 2,500.00 7/28/2020
9205 1,000.00 8/24/2020
9208 1,000.00 8/22/2020
9209 4,000.00 8/18/2020
9210 3,000.00 8/25/2020
9136 2,000.00 6/15/2019
9141 3,000.00 6/22/2019
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started