# We Helped With This Excel (VBA) Economics Homework: Have A Similar One?

SOLVED
Category Economics Excel VBA solution College Solved Excel Vba Assignment Help

## Short Assignment Requirements

Enclosed you will find detailed step by step information for how an excel lab needs to be completed, it also includes pictures of how it should look after each step. (this assignment is graded for points per step) so every step needs to be followed.

## Assignment Description

Step 1: Add Formulas, Functions, and Formatting to Condo Sales Data Worksheet

Now we need to begin working on the worksheet 1. Let's begin by renaming sheet 1 to Condo Sales Data.

A.   Create the formula needed to calculate the percent of asking price.

Column H: Selling Price/ Asking Price

B.   Format the data on this sheet for currency, percent, and so forth.

C.  Format the title rows and center them across the column selection. Format the column headings appropriately.

An example of what your Condo Sales worksheet might look like is below.

Click Image to Expand

Step 2: Add Formulas, Functions, and Formatting to Loan Data Worksheet

Now we need to begin working on the worksheet 2. Let's begin by renaming sheet 2 to Loan Data.

A.   Use the VLOOKUP function to determine the Interest Rate & PMT function to determine the monthly payment.

Column E: Interest Rate using VLOOKUP

Column F: Down Payment = VLOOKUP to determine % Required for Down Payment * Selling Price

Column G: Amount Financed = Selling Price - Down Payment

Column H: Monthly Payment = PMT function (Monthly payment is at the beginning of each period)

Cells C41 - C44: Use functions COUNT, MIN, MAX & SUM to complete these loan statistics

B.   Format the data on this sheet for currency, percent, and so forth.

C.  Format the title rows and center them across the column selection. Format the column headings appropriately.

D.  Format the Loan Term Data and Loan Statistics with a clean outside border, color formatting, currency, and percent.

An example of what your Loan Data worksheet might look like is below.

Loan Data Worksheet

Step 3: Sort and Create Summary Report and Chart

A.   To create the Sorted Data worksheet, begin by creating a copy of the Condo Sales Data. Once a copy has been made of the Condo Sales Data and moved to a new sheet, name the new worksheet Sorted Data. We will not need the Address column, so please delete that column of data from the Sorted Data worksheet.

B.   Convert the data in the Sorted Data worksheet into a table. Select the rectangular block of cells contained in the Sorted Data worksheet. Click the Insert tab and select Tables from the Tables group. Remember to check that your table has headers.

C.  Sort the data by selling agent and then by asking price. Once the data has been sorted, you can convert it back into a range. You must convert to range before you can provide a summary report.

D.  Create a summary report showing total asking and total selling price by agent. Using subtotals, you will select the criteria for your subtotal. Each time the agent name changes, you want the total (sum) of both the Asking Price and Selling Price. Make sure the outline level of your subtotal is set to two on the left top area of your screen. Make sure you format your worksheet professionally.

Click Image to Expand

E.   Create a Column Chart to compare asking price versus selling price by agent. Make certain you provide a professional chart title and formatting for your chart. Include a legend to show what each color represents. Move this chart to a new worksheet and name it Ask vs. Sell Price Chart.

Click Image to Expand

Step 4: Create Filtered Data Worksheet

A.   To create the Filtered Data worksheet, begin by creating a copy of the Condo Sales Data. Once a copy has been made of the Condo Sales Data and moved to a new sheet, name the new worksheet Filtered Data.

B.   Convert the data in the Filtered Data worksheet into a table. Select the rectangular block of cells contained in the Filtered Data worksheet. Click the Insert tab and select Tables from the Tables group. Remember to check that your table has headers.

C.  Click the drop-down box next to Selling Agent. Uncheck Select All, and then check Langston.

D.  Now click the drop-down next to City and select only Pagosa. Now only the properties sold by Langston in Pagosa will display.

Click Image to Expand

Step 5: Create Conditional Formatting Worksheet

A.   To create the Conditional Formatting worksheet, begin by creating a copy of the Condo Sales Data. Once a copy has been made of the Condo Sales Data and moved to a new sheet, name the new worksheet Conditional Formatting.

B.   Convert the data in the Conditional Formatting worksheet into a table. Click the Inserttab and select Tables from the Tables group. Remember to check that your table has headers.

C.  Select the data for just the column reflecting the % of Asking Price field. From the Hometab, select Conditional Formatting from the Styles group and choose Manage Rules. Three new rules will need to be created.

1.   Format only cells that are greater than or equal to 100%; associate a green fill color and a white font with this rule.

2.   Format only cells that are between 95% and 99.9%; associate a yellow fill color and black font with this rule.

3.   Format only cells that are less than 95%; associate a red fill color and white font with this rule.

D.  Now sort the data based on the percentage of the asking price. Click the filter drop-down arrow and select Sort by Smallest to Largest.

Click Image to Expand

Step 6: Create a Pivot Table

A.   In this step, we will create a named range called CondoSalesData. Open the Condo Sales Data worksheet and select Cells A3 through J33. From the Formulas tab, select Define Name from the Defined Names group. Give this segment of the worksheet the name CondoSalesData. Notice it will appear in the name box when all of these cells are selected.

B.   Create a pivot table based on the CondoSalesData range.

1.   Select Selling Agent as your row labels.

2.   Click on City and then drag it into the Column Labels field.

3.   Select Percent of Asking (Price) as your values by clicking and then dragging it onto the values area.

4.   The summary value of the percent will be a SUM. To change this, click the drop-down area in the Values field and select Value Field Settings.

1.   Change the summary type to Average.

2.   Change the number format to percent with two decimals by clicking the Number Format button.

3.   Change the summary label to Average % of Asking Price.

C.  Apply Data Bars Conditional Formatting to the percentages. Last, hide the field headers. Rename the sheet PivotTable, and move it so it is the last tab in the workbook.

Click Image to Expand

Step 7: Professional Appearance (Cleanup)

Cleanup the formatting of your Excel workbook, taking into account professional appearance.

The Minimum Requirement (per the Grading Rubric)

1.   Apply a color and assign a descriptive name to each tab (sheet) in the workbook.

2.   Create the documentation worksheet using a format similar to Labs 1 and 2. Be sure to include a description of each worksheet. An image is provided below.

3.   Make certain to include a custom page header with your name and your instructor's name on the Condo Sales Data worksheet.

4.   Based on your analysis, provide your conclusion concerning the Finance Company slogan. Do they really sell a condo for the asking price? Provide your answer on the Documentation sheet.

Remember, professional appearance doesn't only promote better readability of your report; it represents you as a professional.

Week 3 Documentation Sheet

Step 8: Finish and Submit

Save your Excel file. Saving your file often is good practice (Ctrl + s).

Your Excel file should contain eight worksheets.

·         Documentation

·         Condo Sales Data

·         Loan Data

·         Sorted Data

·         Ask vs. Sell Price Chart

·         Filtered Data

·         Conditional Formatting Data

·         PivotTable

Submit one workbook. When submitting the workbook, provide a comment in the comments area explaining what you learned from completing this lab activity.

Is it free to get my assignment evaluated?

Yes. No hidden fees. You pay for the solution only, and all the explanations about how to run it are included in the price. It takes up to 24 hours to get a quote from an expert. In some cases, we can help you faster if an expert is available, but you should always order in advance to avoid the risks. You can place a new order here.

How much does it cost?

The cost depends on many factors: how far away the deadline is, how hard/big the task is, if it is code only or a report, etc. We try to give rough estimates here, but it is just for orientation (in USD):

 Regular homework \$20 - \$150 Advanced homework \$100 - \$300 Group project or a report \$200 - \$500 Mid-term or final project \$200 - \$800 Live exam help \$100 - \$300 Full thesis \$1000 - \$3000

How do I pay?

Credit card or PayPal. You don't need to create/have a Payal account in order to pay by a credit card. Paypal offers you "buyer's protection" in case of any issues.

Why do I need to pay in advance?

We have no way to request money after we send you the solution. PayPal works as a middleman, which protects you in case of any disputes, so you should feel safe paying using PayPal.

Do you do essays?

No, unless it is a data analysis essay or report. This is because essays are very personal and it is easy to see when they are written by another person. This is not the case with math and programming.

Why there are no discounts?

It is because we don't want to lie - in such services no discount can be set in advance because we set the price knowing that there is a discount. For example, if we wanted to ask for \$100, we could tell that the price is \$200 and because you are special, we can do a 50% discount. It is the way all scam websites operate. We set honest prices instead, so there is no need for fake discounts.

Do you do live tutoring?

No, it is simply not how we operate. How often do you meet a great programmer who is also a great speaker? Rarely. It is why we encourage our experts to write down explanations instead of having a live call. It is often enough to get you started - analyzing and running the solutions is a big part of learning.

What happens if I am not satisfied with the solution?

Another expert will review the task, and if your claim is reasonable - we refund the payment and often block the freelancer from our platform. Because we are so harsh with our experts - the ones working with us are very trustworthy to deliver high-quality assignment solutions on time.

Customer Feedback

"Thanks for explanations after the assignment was already completed... Emily is such a nice tutor! "

Order #13073

Find Us On