Fill This Form To Receive Instant Help
Homework answers / question archive / New Perspectives Excel 2019  Modules 912: SAM Critical Thinking Capstone Project 1c Hwan Sung is a financial analyst at W&K Engineering in Bismarck, North Dakota
New Perspectives Excel 2019  Modules 912: SAM Critical Thinking Capstone Project 1c
Hwan Sung is a financial analyst at W&K Engineering in Bismarck, North Dakota. He is using an Excel workbook to analyze the financial data for engineering projects, recent revenue, and funding for additional construction equipment. He asks for your help in analyzing data, making financial calculations, and correcting errors. 



Unprotect a worksheet. 

Hwan created a macro named CopyStats to copy the project statistics in columns I to L, and then paste them on a new worksheet. When he runs the CopyStats macro, however, it includes extra data from column G. View and edit the macro as follows: 



Edit and run a VBA macro. 


Rename a new worksheet. 

Return to the Projects worksheet. Hwan also created a macro named AddRow that inserts a new blank row in the Projects table. He wants to assign the AddRow macro to a new button to make it easy to run the macro and add a new project. Create the button as follows: 



Insert and position a macro button. 


Edit the text in a macro button. 


Assign a macro to a button. 


Resize a button. 


Align a macro button. 

In the Projects table, Hwan wants to make sure that everyone entering project information enters the correct project categories, which are listed in the range I4:I7. He asks you to ensure users enter a value from this range. 



Add a data validation rule to a range. 


Add an input message to a data validation rule. 


Add an error alert to a data validation rule. 

Hwan wants to make sure all of the categories entered in the Category column of the Projects table are valid. Identify and correct any invalid values. 



Edit a value in a cell. 

Hwan wants to summarize project information in column I. Calculate the number of projects, the total estimates, and the average estimate in each category. 



Create a formula using a function. 


Copy a formula into a range. 


Create a formula using a function. 


Copy a formula into a range. 


Create a formula using a function. 


Copy a formula into a range. 

Hwan needs to identify the number of projects that have an estimate of more than $50,000. Next to the cell labeled "Projects:", use a database function to count the number of projects with estimates of more than $50,000, using the data in the entire Projects table and counting the values in the column of estimates that are equal to the values in the range I9:I10. 



Create a formula using a function. 

Hwan also needs to calculate the total amount paid by client W0338 for engineering projects. Next to the cell labeled "Total paid:", use a database function to calculate the total paid by client W0338, using the data in the entire Projects table and totaling the values in the Paid to Date column that are equal to the values in the range I13:I14. 



Create a formula using a function. 

Go to the Projects by State worksheet. Hwan has created a PivotTable on this worksheet to list projects by state, start date, and category. Refresh the PivotTable so it displays the changes you made to the Category data on the Projects worksheet. 



Refresh Pivot Table data. 

Reduce the clutter in the PivotTable by modifying it. 



Change the layout of a PivotTable. 


Group row fields in a PivotTable. 

Hwan occasionally would like to focus on the project data according to the start date. 



Add a slicer to a PivotTable. 


Reposition and resize a slicer. 

Return to the Projects worksheet. In the section labeled "Projects by State", Hwan wants to display the grand totals of the project estimate per state. He can retrieve this information from the PivotTable on the Projects by State worksheet. Display the estimates for each states projects. 



Create a formula using a function. 


Create a formula using a function. 


Create a formula using a function. 


Create a formula using a function. 


Create a formula using a function. 

Go to the Project Payments worksheet, which contains a PivotTable showing the estimate and total paid amounts for each client. Add Category as the second field in the Rows area to include the category of each project in the PivotTable. 



Add a field to the Rows area of a PivotTable. 

Hwan wants to know the difference and the percentage of difference between the estimated and the total paid amounts. Provide this information for Hwan. 



Add a calculated field to a PivotTable. 


Add a calculated field to a PivotTable. 


Modify the number format of a PivotTable field. 


Enter text in a cell. 

Hwan needs to identify the location of each project. 



Add a slicer to a PivotTable. 


Reposition and resize a slicer. 


Filter a PivotTable using a slicer. 

Go to the Projects by Category worksheet, where Hwan has inserted a PivotTable showing the total estimates per category. He wants to further divide the project information by state. Display the State field values as column headings. 



Add fields as columns in a PivotTable. 

Hwan asks for another way to compare the total estimates for each project category and state. 



Create a PivotChart. 


Resize and reposition a PivotChart. 

Go to the Monthly Sales worksheet, where Hwan wants to list the sales per month in fiscal year order (March–February). He has a text file that already contains this data. 



Import data from a text file. 


Format imported data from an external file. 


Format imported data from an external file. 


Format a range as a table. 


Change the number format. 

Go to the Employees worksheet. Hwan wants to display information about employees in the five locations where W&K Engineering has offices. This information is available in another text file. 



Import data to a new worksheet. 


Format imported data from an external file. 


Format a range as a table. 


Apply a table style. 


Set the name of a table. 

Create a PivotTable. 



Add fields as rows in a PivotTable. 

Hwan wants to show the number of employees by title and the average of their years employed at each W&K Engineering office. Modify the new PivotTable on the Employees worksheet to provide this information. 
Add and summarize values in a PivotTable. 

Modify the number format of a PivotTable field. 



Insert WordArt. 

Enter text in a WordArt shape. 



Format WordArt. 

Reposition WordArt. 



Reposition WordArt. 

Go to the Expansion Loan worksheet, which contains data about a loan to expand the business by purchasing additional construction equipment. Hwan wants to include a worksheet title to match the format of the other worksheets in the workbook. Insert and format WordArt as follows: 
Edit a formula using a function. 


Edit a formula using a function. 


Copy a formula into a range. 

Copy a formula into a range. 



Create a formula using a function. 


Copy a formula into a range. 

Before performing any calculations, Hwan asks you to correct the errors in the worksheet. 
Copy a formula into a range. 


Create a formula using a function. 


Copy a formula into a range. 


Copy a formula into a range. 

Now Hwan is ready to calculate the annual principal and interest payments for the expansion loan. Start by calculating the cumulative interest payments. 
Create a formula using a function. 


Copy a formula into a range. 


Copy a formula into a range. 

Next, calculate the cumulative principal payments. 
Create a formula using a function. 


Copy a formula into a range. 


Copy a formula into a range. 

Hwan wants to compare straightline depreciation amounts with declining balance depreciation amounts to determine which method is more favorable for the company's balance sheet. In column G, he estimates that the construction equipment will be worth $800,000 in tangible assets at the beginning of the loan, and that the useful life of these assets is 10 years with a salvage value of $128,000. Start by calculating the straightline depreciation amounts. 
Create a formula using a function. 


Create a formula using a function. 


Create a formula using a function. 

Next, calculate the declining balance depreciation amounts for the construction equipment. 
Create a formula using a function. 


Create a formula using a function. 


Create a formula using a function. 

Hwan also wants to determine the sumofyears depreciation balance for the first year and the last year of the useful life of the construction equipment. Determine these amounts as follows: 
Add a trendline to a chart. 







Go to the Investments & Projections worksheet. Besides using a bank loan to fund their upcoming expansion, W&K Engineering is looking for investors. This worksheet should show the returns potential investors could realize if they invested a total of $400,000 in the company. Hwan knows that a desirable rate of return would be 9 percent. He also estimates the investment would pay different amounts each year, as shown in the repayment schedule section. Calculate the net present value of the investment and the internal rate of return. 








The Monthly Revenue Projections scatter chart in the range E3:K20 is based on monthly revenue estimates listed on the hidden Monthly Revenue Projections worksheet. Hwan wants to include a trendline for this chart that shows investors how revenues will most likely increase quickly at first and then level off in later months. Modify the Monthly Revenue Projections scatter chart as follows to include a logarithmic trendline: 





Already member? Sign In