CIS 110 Excel Project 2

Grading

This project is worth 50 points.
 

Start by creating your own original file. Use Save As to create your additional files. 

You are responsible for all decisions regarding cell width, format, labels, and formulas unless otherwise indicated.

Use this header and footer format.    

  Left
 		Center Right
Header Date Filename Your Name
Footer Time

Page number

  
  •  Use appropriate formulas including functions and absolute references where appropriate.
  •  Cell F2 must contain 35% (The Markup Percentage Value used in your retail price calculations)

I have listed the products for your store as Items 1 - 5, use any names that you think are appropriate in your worksheet.  If you want to use different values please make them realistic and include a note in your worksheet noting that you are using your own values.   

Align text on the right and use borders where appropriate

Your Store Name Here
(centered across columns)
          35%  Markup
             
Product Inventory
Start		 Inventory
End		 Quantity
Sold		 Wholesale
Cost		 Retail
Price

Gross
Profit
Item 1 529 193   50.00    
Item 2 507 282   47.74    
Item 3 98 2   18.22    
Item 4 953 456   25.30    
Item 5 3591 358   22.22    
Totals            

Create a worksheet in any format that you like answering at least the following:

  •  Quantity Sold:   quantity sold = start inventory - end inventory
  •  Retail Price: retail price = wholesale cost + (wholesale cost * markup percentage)
  •  Gross Profit: gross profit = (retail price - wholesale cost) * quantity sold

Calculate the Expenses for operating the business for 1 MONTH. Use any reasonable value except where indicated.

Include the following in your worksheet to determine this cost:

  •  Rent - use a reasonable value
  •  Utilities - use a reasonable value
  •  Wages - For at least 4 HOURLY EMPLOYEES - show all calculations for each employee including rate of pay, hours worked and total pay using.
    Note: Use one column each for pay rate, hours worked and total pay.
  •  Insurance = 5% of Total Gross Profit
  •  Advertising - use a reasonable value
  •  Taxes = 39% of Total Gross Profit
  •  Car if your store needs one
  •  anything else that you think is necessary
Requirements for Grade C
  • Calculate the total Expenses:
  • Calculate the Net Profit: =  Gross Profit - Expenses

Note: Your values will be different than the examples

  • Rename the worksheet to Store
  • Save your file as EXCEL_TEST1
  • Print the worksheet (do not submit printouts, I will see your print settings)

This printout shows the initial calculations.
Requirements for Grade B
  • Create a copy of your worksheet
  • Rename the new worksheet Store 2
  • Insert a row and add another product using any reasonable values you wish
  • Decrease the Inventory Start value for Item 5 from 3591 to 1591
  • Change the formula for calculating the value for Taxes to use the following rule

=If Gross Profit <= $15,000, then Taxes = Gross Profit * 20%, otherwise Taxes = Gross Profit * 39%  

Adjust ONLY THE MARKUP VALUE in cell F2 to obtain a increase of $10,000 in NET PROFIT from the value on the first printout. You must use goal seeking.

  • Save your file as EXCEL_TEST2
  • Make 2 printouts (do not submit printouts, I will be able to see your print settings)
    • the worksheet IT MUST FIT ON ONE PAGE
    • the cell formulas IT MUST FIT ON ONE PAGE with .25 inch left / right margins in landscape mode. (Use CTRL + ` to see the formulas)

Shows net profit after adjustments including an increase in markup percentage resulting in $10,000 more than the value on you first printout.

This printout shows all the formulas
Requirements for Grade A
  • Create a new worksheet based on Store2 and name it Store 3
  • Find the percentage markup that produces $0 Net Profit
  • Between column A and B Insert a new column adding SKU numbers (product numbers). Use the following numbers in any order: 123C7 456D8 789F2 665A1 014Z2 332X4
  • Format the worksheet (using the formatting styles similar to your class exercises)
  • Create a 3-D pie chart on another sheet for either Gross Profit of each product or Employee Wages, or Expenses
  • Rename the chart worksheet Pie Chart
  • Print the chart with the included header.
  • Print the worksheet in the landscape mode showing the $0 net profit.
  • Save as your file as EXCEL_TEST3
  • Upload EXCEL_TEST3 or the last file you finished to the Digital Drop Box.

Printout showing $0 net profit

Required pie chart
  • This test is designed to be completed in one hour. 
  • You should be able to complete the assignment in 2 hours or less 
  • After you are finished you need to upload it to the Drop Box. 
How is this assignment graded?
  • Follow instructions: naming worksheets, correct placement of markup percentage, headers, footers
  • Correct use of formulas: using cell references, functions including If and Sum, and absolute references where appropriate.
  • Formatting worksheet: layout (portrait/landscape), margins, header/footer
  • Chart: correct chart type, correct data used
  • Does the worksheet include calculations for employees, insurance and taxes?
  • Proper use of goal seeking
  • Is the net profit on worksheet Store 2 $10,000 higher than the Store worksheet? 
  • Is the net profit on Store 3 $0?
  • Is File correctly named? Does it contain all the worksheets? Are the worksheets correctly named?
  • calculated wages for 4 employees using multiple columns
  • No circular cell reference in worksheets
  • added header & footer
  • missing "IF" statement for tax calculation
  • use cell reference for quantity sold in calculating gross profit

Return to Excel Assignments