Introducing
Your new presentation assistant.
Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.
Trending searches
Advanced Spreadsheet Skills: Using Microsoft Excel
178
Advanced Spreadsheet Skills:
Using Microsoft Excel
Leila Cervantes
Maria Samantha Lesaca
Estimating the Product Cost using Microsoft Excel
Let us assume that we are going to sell milk tea with the following information:
Estimating the Cost
Product Information
Product Name/Brand: Starbuko Organic Milk Tea
Company/Group Name: Starbuko Foods Corporation
Product Description:
Starbuko Milk Tea is a special milk tea using organic ingredients especially made for Filipinos.
Ingredients/Materials: tea, water, condensed milk, ice, special sweetener
Estimated Sale Price: 25 PHP per cup
1.) Copy the Information Below
Step 1
Note
Note
Values with PHP or Philippine peso sign use the Accounting Number format. Its default uses the dollar symbol ($). To change this, click the drop-down arrow at the currency button then select More Accounting Formats, then change the symbol to PHP.
2.) Use the SUM formula to get the summation of the values from C4 to C7. The SUM formula is =SUM(C4:C7). Type this on cell C8 as shown below
Step 2
3.) Check if our estimated price will earn us profit. Type the additional information shown below then apply the arithmetic formula for subtraction.
Step 3
4.) The result is PHP2.00. Our estimated price is not profitable. Let us make an adjustment. Include the information below and deduct the Total from the New Sale Price:
Step 4
5.) The result is PHP7.80. This is definitely profitable at this price. However, this does not guarantee our product's success.
Step 5
6.) Save your file as L4 Cost of Ingredients.xlsx.
Step 6
Analyzing Data using Microsoft Excel
Analyzing Data
It is now time for us to collect data from our target market. In reality, researchers would also come up with survey questions before they release the product. The data they gathered would help them determine if the product has any chance of succeeding in a target market.
Sample Survey (for food products)
Sample Survey (for food products)
Name: __________________________________________________________ Age: _______
Income/day: ____________________________________________________ (optional)
Instructions: Circle the letter of your answer.
1. On a scale of 1-5, how would you rate the product's quality?
a. 1 b. 2 c. 3 d. 4 e. 5
2. On a scale of 1-5, how would you rate the product's taste?
a. 1 b. 2 c. 3 d. 4 e. 5
3. On a scale of 1-5, how would you rate the product's presentation?
a. 1 b. 2 c. 3 d. 4 e. 5
4. Are you satisfied with the product?
a. Yes b. No
5. Would you recommend a product to a friend?
a. Yes b. No
6. How much are you willing to pay for this product?
a. PHP15 and below
b. PHP16-25
c. PHP26-35
d. PHP36-45
e. PHP46-55
Suggestion or comments:
________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Raw Survey Results
Tip
To change the orientation of the text, press Ctrl+1. To open the Format Cells dialog box > Alignment tab > under Orientation, specify the degrees you want.
Formulas/Functions
Formulas are used to make your work in Excel easier. There are many ways of how you can use these functions.
Applying Different Formulas/Functions
Simple Average Formula
Simple Average Formula
Let us start by a simple average formula to determine the average rating of Quality, Taste, Presentation, and Product.
The syntax would be =AVERAGE(cells involved)
Example:
Using the Raw Survey Results, determine the average for Quality:
=AVERAGE(F5:F19)
COUNTIF Function
COUNTIF Function
Using the =COUNT function will allow us to count the number of cells that contains something. However, in this case, we just need to count the ones that have YES or NO in them. For this, we have to use the COUNTIF function.
COUNTIF Function Syntax:
=COUNTIF(range, criteria)
Range - the cells where the counting will take place.
Criteria - the label or value that determines if it is to be counted.
Example:
Using the Raw Survey Results, the formula for getting the number of YES for the criteria is
=COUNTIF(J5:J19,"YES")
AVERAGEIF Function
AVERAGEIF function
Using the AVERAGEIF function, we can average a range if the cell beside it equals to "teacher" and/or "student."
AVERAGEIF uses the following syntax:
=AVERAGEIF(range, criteria, average range)
Range - the cells where the counting will take place.
Criteria - the label or value that determines if it is to be counted.
Average Range (optional) - the actual range of cells that will be averaged, if omitted, the range will be used instead.
Example:
Using the Raw Survey Results:
Result:
With this, we can definitely say that the teachers rated the product higher.
Key Terms
Accounting Number Format - a number format that is used for accounting.
Orientation - the angle at which a text is displayed.
Count - a function used to count the cells with content in a range.
Sum - a function used to compute for the summation of the number of a range.
Average - a function used to compute for the average of the number of a range.
COUNTIF - a function used to count the cells with a specified content within a range.
SUMIF - a function used to compute for the summation of a range if a certain condition is met.
AVERAGEIF - a function used to compute for the average of a range if a condition is met.
Range - the range of cells where you want to look for the criteria.
Criteria - a value or label that determines if a cell is part of the range to be averaged.
Average Range - the actual range of cells that will be averaged, if omitted, the range will be used instead.