09 Oct Your friends, Elijah and Valerie Foglesong, want to build their dream house. They identified tentative costs, but they
Your friends, Elijah and Valerie Foglesong, want to build their dream house. They identified tentative costs, but they cannot afford the $414,717 estimated cost. You will use Goal Seek to determine an estimate of the total finished square footage they can afford. To help provide more flexibility in their decision-making, you will create a data table listing various finished square footages and their effects on the base house cost and total cost. Finally, you will create another data table showing combinations of square footages and lot prices to identify total costs.
Start Excel. Download and open the file named Exp19_Excel_Ch06_ML1_HW-Housing Cost.xlsx. Grader has automatically added your last name to the beginning of the filename.
Assign the range names B9 (Base), B15 (Interior), B21 (Exterior), B23 (Lot).
Enter a formula in cell B25 to calculate the total estimated cost. The total estimated cost is the sum of the base house price (B9), total interior upgrades (B15), total exterior upgrades (B21), and lot fee (B23). Be sure to use range names in the formula.
Use Goal Seek to determine the total finished square footage(B7) to meet the total cost goal of $350,000.
Enter a series of total square footage ranging from 1,800 to 3,600 in increments of 200 in the range D6:D15. Apply Blue font and Comma Style with zero decimal places to the series.
Enter a reference to the base house price in cell E5 and a reference to the total estimated cost in cell F5.
Complete the single variable data table in the range D5:F15. Be sure to use total square footage as the column input. Apply Custom number formats to give appropriate descriptions to the second and third columns. Apply these formats to the headings: bold, center, and Blue font color.
Identify the square footage, base price, and total cost in the data table that comes closest to their goal. Apply Yellow fill color to those cells.
Copy the square footage substitution values, range D6:D15 to the range H6:H15 and remove the fill color. Enter these lot price substitution values in the range I5:K5: 90000, 96000, and 102675. Format these values with Accounting Number Format with zero decimal places and Blue font color.
Enter the reference to the total estimated cost in the appropriate location for the second data table. Apply a custom number format to display Total Sq Ft in that cell. Apply Blue font and bold to that cell.
Complete the two-variable data table using total square footage as the Column Input and lot price as the Row Input. Apply a Custom number format to the reference to the formula cell. Apply Yellow fill color to the total price in each column that comes closest to their goal.
Format results in both tables with Accounting Number Format with zero decimal places.
Create a scenario named Ideal Costs, using Base price per square foot and Lot fee (use cell references). Enter these values for the scenario: 80, 102,675.
Create a scenario named Increased Costs, using Base price per square foot and Lot fee (use cell references). Enter these values for the scenario: 100, 110,000.
Create a scenario named Low Costs, using Base price per square foot and Lot fee (use cell references). Enter these values for the scenario: 60, 80,000.
Generate a scenario summary report using the cell references for Price per square foot and Lot Fee. Add the appropriate formatting to the newly created report
Save and close Exp19_Excel_Ch06_ML1_HW-Housing Cost.xlsx. Exit Excel. Submit the file as directed.
HOW OUR WEBSITE WORKS
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of
HIGH QUALITY & PLAGIARISM FREE.
To make an Order you only need to click ORDER NOW and we will direct you to our Order Page at WriteDen. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Deadline range from 6 hours to 30 days.
Once done with writing your paper we will upload it to your account on our website and also forward a copy to your email.
Upon receiving your paper, review it and if any changes are needed contact us immediately. We offer unlimited revisions at no extra cost.
Is it Safe to use our services?
We never resell papers on this site. Meaning after your purchase you will get an original copy of your assignment and you have all the rights to use the paper.
Our price ranges from $8-$14 per page. If you are short of Budget, contact our Live Support for a Discount Code. All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.
Please note we do not have prewritten answers. We need some time to prepare a perfect essay for you.