Estimating Remodeling Prices - Design and implement a workbook solution to the problem described below. Remember to

No answer yet for this question. Ask a Tutor

Question:

Estimating Remodeling Prices - Design and implement a workbook solution to the problem described below. Remember to create the new workbook by first launching Microsoft Office Excel 2007, then clicking on the Office button and selecting "New". 1. You should use your sketch to help you set up the workbook. Carefully consider inputs, outputs, calculations, cases etc. Also consider using intermediate calculations for values that are used multiple times. If necessary, review the slides on spreadsheet organization before beginning your work. An efficient structure will substantially cut down on how much work will be needed including repetitious typing, the complexity of your formulas and help in presenting each remodeling case as a separate entity. Be sure that a final display is available for each case on a separate worksheet including all relevant inputs/outputs for that case. 2. Begin typing in your data and create your formulas to calculate each cost component for each case. Name your file Xmart.xlsx and save it to your personal directory. Be sure to save often during this process, so that only minimal amounts of work will be lost if any technical difficulties arise. 3. Follow along the problem - which now contains all of the data. Also note that additional analyses have been added which you will have to incorporate into your solution. X-Mart - The Problem Description: Congratulations, you've just started your new job as financial analyst for a mid-size grocery store company operating on the east coast, X-Mart. X-Mart is currently in the process of remodeling their existing stores to better keep up with the competition and recent consumer trends. One of the first projects you'd like to tackle is to assist X-Mart in this process by creating a workbook that can quickly and easily estimate the cost for remodeling of an existing store. To do this you'd like to setup an estimating workbook that outlines inputs and costs for three of the most common remodeling options - low, medium and high. Management has asked that your workbook include the following design elements. Data inputs and cost outputs should be carefully organized with these two required elements in mind.  Easily modify all data inputs and pricing variables independently for each remodeling case.  Present data to management on a case-by-case basis - listing all related inputs and outputs for a specific remodeling case on a single worksheet  The Data - This section will list the information required to perform the necessary calculations. 1. Store Dimensions & Features The following table contains the 3 cases you will initially evaluate. The lengths and widths, given in You should now be ready to implement the design you created in your pre-lab using the values given below to perform the necessary calculations. Remember that ultimately your output must display each remodeling case on a separate worksheet that contains the relevant data inputs/outputs for a customer to view. In-lab 5 1 Merchandise Area Office Area Warehouse Area Width Entryway CS&E 2111 linear feet (1LF=1 foot), are the actual size of the store building. The store building includes multiple internal areas including the following: - The merchandise area is where the food and other products are displayed to customers - The office area contains staff office. Each office contains 200 square feet. Office areas can vary for different remodeling cases, and different cases can have different number of offices. - The warehouse area is where trucks come in and items are held until they are shelved. This area includes the truck dock area. The square footage for these options are listed in the table below. - The entryway is where customers enter/leave the store including an area for shopping carts and lavatory facilities. The square footing of the basic entryway is 5000 SF. The extended entryway will be 8500 SF total. Each store remodeling plan has a different combination of these areas. A list of these variables for each specified case can be found below. Your solution should include the ability to substitute any of the variables, number of offices, levels of furnishings etc. The length and width given below are for the entire building. In addition is the square footage (SF) for the warehouse area, the number offices, truck docks, and lavatories stalls. Each office is assumed to be a total of 200 SF. The lavatory area are part the entryway square footage. A basic entryway is 5000 SF while an extended entryway is assumed to be 8500 SF. The remaining area of the building is for merchandising. Length case# 1 - Low End 2- Medium 3 - High End Length in LF for Total Building 400 450 450 Width in LF for Total Building 500 550 550 Number of Offices (200 SF each) Type of Entryway Ware- house area in SF 10000 12000 12000 Number of Truck Docks 3 4 5 #ofsetsof Merchandise Lavatory stalls Area (men/women) Furnishings Level 3 2 4 3 5 4 35 Basic 40 Basic 40 Extended 2. Cost Data  Building Components Remodeling Costs To calculate the cost of each remodeling option use the list of component prices detailed below. Some items are priced based on the square footage (SF) - area of building space, some items are priced based on linear footage (LF) - perimeter of the building, and some items are priced by unit, type or level. For each case you will be calculating the cost of each component using the quantities and pricing given. Be sure to explicitly list each component cost as well as the total remodeling costs. Again remember to design your worksheet(s) efficiently to minimize the amount of inputting/calculations you will need to input. If you use an intermediate calculations over and over again (square footage of warehouse area, merchandising area, total area, perimeter etc.), it is recommended that you explicitly list it to simplify your calculations. In-lab 5 2        Utilities (pipe, electrical, ductwork, heating, etc.) - $45.00 per LF of exterior perimeter wall Offices - $4500 per office includes walls, floor, ceiling, lighting and furnishings Lavatories - Lavatories are priced as follows:  The basic lavatory facilities include one men's room and one women's room each with 2 stalls and 2 sinks - the price for this set is $25,000. For each additional stall set (one men's and one women's stall) the cost increases by $5000 per set. Entryway - $100,000 each for basic and $150,000 for the extended entryway (excluding lavatories). Truck Docks - $15,000 each Warehouse Lighting/Electrical - $3 per SF of warehouse space Merchandise Area Furnishings CS&E 2111 There are 4 different furnishing levels for the merchandise area including floor coverings, lighting, shelving, and refrigeration options. The basic level has metal shelving and 1 aisle of each of refrigeration and freezer cases. The more upscale stores with level 4 furnishings will have faux wood floors, wood shelving and multiple aisles of refrigeration, freezer and deep freeze cases. Prices for each level are listed here. Again be sure that these prices can vary for each remodeling option - as specific locals can affect specific costs.  Level1:$7.50perSFmerchandisearea.  Level2:$10.15perSFmerchandisearea.  Level3:$14.80perSFmerchandisearea.  Level4:$23.40perSFmerchandisearea. We recommend you setup a reference table to accommodate these various levels and pricing variables. Construction Remodeling Overhead Costs In addition to the above building component costs, there is also overhead expenses associated with each remodeling project. These costs include management oversight and store design and vary as a function of total cost of all previous components. Overhead costs are calculated as a percent of total component costs as follows: Additional Calculations  Financing Options Another typical question from your department is what types of financing is available for the corporation. For each of the two loan options below - list the annual interest rate, the loan duration in years, the number of periods per year, the annual payments, the initial loan value and the balloon payment. For each option, you will need to insert the data inputs (based on the information below) and calculate the remaining values as needed. Use as the cost of the remodeling case2 (medium) in your analysis, though design it so each of the data inputs can be easily modified. Use a new worksheet name Financing for this analysis. Option Annual Duration Number of Total annual Present Future Value Rate in Years Periods per yr Payments Value 1 2  Option 1: A mortgage for the total cost of the building remodeling at a rate of 6.25% per year compounded monthly for five years. At the end of 5 years there will be a balloon payment of $200,000. Mortgage payments are made once per month.  Option 2: A mortgage for the total cost of the building where yearly payments of $400,000 are made and the interest rate compounded annually is 6.9%.  Preparing the Chart In-lab 5 3 Total Component Costs Overhead % $ 0 25 $ 1,250,000.00 22 $ 2,500,000.00 20 $ 5,000,000.00 18 $ 7,500,000.00 16 CS&E 2111 On a separate worksheet in the workbook create a column chart with 3 stacked columns (3-d) showing the cost estimates of the 3 building remodeling cases including each of the component costs and overhead for each case. It may be helpful to copy the required data to a separate sheet of the workbook to help make this task easier. Each column should represent 1 case stacked with the component cost breakdowns. Lab Checklist Submit: 1. Complete Xmart.xlsx (in-lab) You have completed the lab - always remember to logoff the computer before leaving.