You are building an Excel spreadsheet to calculate

You own and operate a catering business called Rosie’s Home-Cooked Meals. You are building an Excel spreadsheet to calculate your profits for the previous year. You have entered sales and most of the expense data in the worksheet. Now you need to enter the necessary formulas to calculate the delivery costs and the profits for each month.

a. Open the file H-S.xlsx from where you store your Data Files, then save it as H-Catering Profits. Enter your name in cell A26.

b. Rosie’s Home-Cooked Meals pays for food deliveries through a delivery service, which charges a $9.00 flat fee per delivery. The delivery fee is in cell B20. Enter a formula in cell IS that calculates the cost of deliveries for the month of January. (Hint The formula needs to multiply cell HS- the cell that contains the number of deliveries for January-by cell B20, with B20 as an absolute cell reference.)

c. Enter a complex formula in cell JS that calculates profits for January. The formula should subtract the sum total of cells CS:GS and cell IS from BS (Sales for January). (Hint: Start the formula with BS followed by the – mathematical operator, followed by the SUM function to add CS:GS and cell IS.)

d. Select cells IS and JS, then use the fill handle in cell JS to copy the formulas down the columns.

e. Enter a formula in cell B21 that identifies the highest profit amount.

f. Enter a formula in cell B22 that identifies the smallest profit amount.

g. Enter a formula in cell B23 that calculates the average monthly profit for the entire year.

h. Apply conditional formatting to the cells JS:J16 to format any cells containing values greater than 35000 with green fill and dark green text.

i. Format the raµge A4:J16 as a table, choosing any table style you like. Add a Total row. Use the fill handle to copy the formula in cellJ17 to cells I17:B17. (Note: If any cells display#####, increase column widths until all cells display numbers.) Apply the General number format to cell H17. (Hint: Click the HOME tab, click the Number Format list arrow in the Number group, then click General.)

j. Sort the table by the Profits column by Largest to Smallest.

k. Apply shading (your choice) to cells A21: B24. Apply All borders around the shaded cells.

I. Preview the worksheet in Backstage view. Change the orientation to landscape, then adjust the scaling so all columns fit on one sheet.

m. Save your changes, close the workbook, then exit Excel. Submit your completed worksheet to your instructor.

The post You are building an Excel spreadsheet to calculate appeared first on My Assignment Online.