COSC 1301: Excel 4 – Fund Raising Workbook



COSC 1301: Lab 5 (Excel Chapter 1) – Guest House Rental Rates

You manage a beach guest house in Ft. Lauderdale. The guest house contains three types of rental units. You set prices based on peak and off-peak times of the year. You want to calculate the maximum daily revenue for each rental type, assuming all units of each type are rented. In addition, you want to calculate the discount rate for off-peak rental times. After calculating the revenue and discount rate, you want to improve the appearance of the worksheet by applying font, alignment, and number formats.

Assigned Reading

Excel Chapter 1

a. Open the Excel chapter 1 starter file (COSC1301_Excel1_Starter.xlsx). Add a Standard Header to the top of the page.

b. Save the new file to your disk or USB drive, as lastname_firstinitial_E1.xlsx.

c. Create and copy the following formulas:

• Calculate the Peak Rentals Maximum Revenue based on the number of units and the rental price per day.

• Calculate the Off-Peak Rentals Maximum Revenue based on the number of units and the rental price per day

• Calculate the discount rate for the Off-Peak rental price per day. For example, using the peak and off-peak per day values, the studio apartment rents for 75% of its peak rental rate. However, you need to calculate and display the off-peak discount rate, which is 25%. *(Hint: if this is not clear, look at the formulas on the second page of the sample solution)*

a.

b.

c.

d. Format the monetary values with Accounting Number Format. Format the discount rate in Percent Style with one decimal place.

e. Format the headings on row 4:

• Merge and center Peak Rentals over the two columns of peak rental data. Apply Dark Red fill color and bold White, Background 1 font color.

• Merge and center Off-Peak Rentals over the three columns of off-peak rental data. Apply Blue fill color, and bold White, Background 1 font color.

f. Center, bold, and wrap the headings on row 5.

g. Apply Red, Accent 2, Lighter 80% fill color to the range C5:D8. Apply Blue, Accent 1, Lighter 80% fill color to the range E5:G8.

h. Set 1” top, bottom, left, and right margins. Center the data horizontally on the page.

i. Insert a new worksheet, and then name it Formulas. Copy the data from the Rental Rates worksheet to the Formulas worksheet. On the Formulas worksheet, select landscape orientation and the options to print gridlines and headings. Display cell formulas and adjust column widths so that the data will fit on one page. Insert a header with the same specifications that you did for the Rental Rates worksheet.

j. Save and close the workbook, and submit lastname_firstinitial_E1.xlsx based on your instructor’s directions.

Sample Solution

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download