5 Advanced Spreadsheet Skills

Download as pptx, pdf, or txt
Download as pptx, pdf, or txt
You are on page 1of 57

Advanced

Spreadsheet
Skills
Directions: Fill in the missing
letters to complete the word
that will describe the picture.
BU
_ D G E_ T_ I N G
_
A C_ C O
_ U N _T I N _G
B I _L _L I N
_G&SA
_ L E_ S
R E_ P O
_ R T _I NG
_G
CA
_ L E N _D _A R S_
SPREADSHEET
• Spreadsheet applications like Microsoft
Excel and Google Spreadsheets allow
large set or group of data to be stored,
organized, analyzed and interpreted
automatically.
Working with Spreadsheet
In creating a basic budgeting application, you will
create a presentable basic budgeting application
where you can input financial data, as your output.
You will be able to create a spreadsheet application
using the following:
- Cell Formatting
- Conditional Formatting
- Advance Formulas
- Linked Tables
Other terms to remember:

Range - a collection of two or more cells.


Worksheet – collection of cells organized in
rows and columns.
Workbook – collection of worksheets.
Cell address
- name of the cell
- combination of column heading and row
heading
Group Activity 7
Form groups with five (5) members each. Try to come
up with a product or service that you can sell or offer
around the campus. It can be sweets like polvoron, or
services like foot spa.

Add a personal twist to your product or service. If it


is a food product, add something that you think can
be marketable. Ex. you may add malunggay to
polvoron.
On a sheet of paper, fill out the information of your
product. A sample is shown below:

Product Information
Product or Service Name: _________________________________
Group Name: ___________________________________________
Group Members:
_____________________________________
_____________________________________
Product Description:
______________________________________________________
Ingredients/Materials:
______________________________________________________
Estimated Sale Price: ___________ per ___________
Quiz #4 (2 nd
Quarter)

Directions: Choose the letter of the best


answer. Write the chosen letter on a
separate sheet of paper.
1. Based on the activities, what is the use of a
Spreadsheet software?

QUIZ #1
A. It allows the data to be stored, organized
and analyzed.
B. It gives the user the ability to create tables.
C. It enables users to record words and
numbers.
D. It is a software that allows input, editing,
formatting and output of text
2. How can you create a new sheet tab in
Windows Excel?

QUIZ #1
A. In the ribbon, select Home>Tabs>Add Tabs
then input how many tab you like to add.
B. In lower portion of the work area, press the
plus button besides the tabs.
C. Press Ctrl + A then press Ctrl + Shift + N.
D. You can’t add tabs in Microsoft Excel.
3. Harold wants to drag select cells A1 to D7. How
can he accomplish this?

QUIZ #1
A. Long press Alt then click A1 then click D7.
B. While pressing shift select the A1 then D7
C. In the formula bar type A1:D7
D. Press Ctrl + A.
4. What is the formula that will subtract the
contents of the cells A7 and A8?

QUIZ #1
A. A7 - A8
B. = A7 - A8
C. A7 - A8 =
D. =SUM (A7:A8)
5. How to format a cell that will automatically add a peso sign?
A. Select the cell or cells you want to edit then press Insert > Symbol >
More Symbols. Scroll down and look for the peso sign then press

QUIZ #1
insert.
B. Select the cell or cells you like to add formatting. Press right click
then click Symbol > More Symbols. Scroll down and look for the
peso sign then press insert.
C. In the ribbon, select the Home tab then click Format Cells. In the
Number tab, click Currency then in the dropdown box search for
the peso sign symbol.
D. Select the cell or cells you like to add formatting press right click
then Format Cells. Then in the Number tab, click Currency then in
the dropdown box search for the peso sign symbol.
6. How do you resize a column or width?
A. Select the cell the press right click, select

QUIZ #1
format cell then change height and width values.
B. In the Formula bar type (h[value]:w[value]).
C. Click the cell then in the ribbon select
format cell then change height and width values.
D. Click and drag the column or row heading.
7. What is the formula that will add the
contents of the cells C1 and C10?

QUIZ #1
A. C1 + C10
B. = C1 + C10
C. C1 + C10 =
D. SUM (C1:C3)
8. How do you resize a graph?
A. Click the graph then click and drag any of

QUIZ #1
the eight handles in the edges of the graph.
B. Select the table then press right click, a
window will appear, select the Width
change the value desired then select height
change the value desired.
C. Select the table the press Ctrl + Arrow Keys.
D. You can’t change the size of graphs.
9. How do you rename a graph into “First Data” in
Microsoft Excel?

QUIZ #1
A. Press right click then select rename, a
window will appear then type “First Data”.
B. Press Ctrl + A then Ctrl + R, rename the chart
to “First Data”
C. Double click the name of the chart and type
“First Data”.
D. Graph names can’t be changed as they follow
the name from the selected data.
10. What is the purpose of the Formula Bar?
A. It shows the contents of the current cell and

QUIZ #1
lets you create and view formulas.
B. It is a selection of shortcuts different formula
in Microsoft Excel
C. It is the formula that allows computation of
values from different cells.
D. The Formula Bar is a label below the cell that
indicates the result of a formula
11. Which Ribbon Menu is needed to be

QUIZ #1
selected to change the font?
A. Home
B. Insert
C. Page Layout
D. Cell Formatting
12. How do you assign cell the formula
= (A1 + A2) + A3?

QUIZ #1
A. On the formula bar type cell address
B. Press right click then select Cell Format then
click on the Formula tab, type which cell you
want that formula will be displayed
C. In the ribbon click Cell Formatting then select
the formula then type where it will displayed.
D. Type the formula where you want it to be
displayed on.
13. Which Ribbon Menu is needed to

QUIZ #1
add a Chart?
A. Home
B. Insert
C. Page Layout
D. Cell Formatting
14. With this formula = Expense!C12, what does the
Expense! part means?

QUIZ #1
A. It a variable that allows quick reference of a
value.
B. It is formula label, allowing the user to
manage all the formula.
C. It indicates that the c12 to be displayed is on
the sheet Expense
D. This is not a proper use of the formula.
15. How do you change formula style in a sheet?
A. Click Insert > Cell Formatting > Table

QUIZ #1
Format > Table Style
B. Click Home > Format as Table > Table Style
C. Click Cell Formatting > Format Table > Table
Style
D. You have to select each cell then add cell
borders to each of them
Product Information
Product or Service Name: STARBUKO ORGANIC MILK TEA
Group Name: STARBUKO FOODS CORPORATION
Group Members:
JI PYEONG
DAL MI
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
We will use Microsoft Excel to find out if our estimated sale price is
reasonable considering the actual price of the ingredients.
Step 1. Copy the information below:

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.
Step 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 3. Check if our estimated price will earn us profit. Type the
additional information below then apply the arithmetic formula for
subtraction:
Step 4. Analyze the result if our estimated price is profitable or not.
If not, let us make an adjustment. Include the information below and
deduct the Total from the New Sale Price:

Step 5. Check the result if profitable at its price.


Step 6. Save your file as Cost of Ingredients.xlsx.
Now it is your group’s turn to do this for your own product!

Remember to research on the ingredient’s price and if


applicable, divide the quantity of a certain ingredient so that
its cost will only cover one serving. For instance, a 300 ml
condensed milk will cover about three servings of milk tea,
so you have to divide its price by three.
Analyzing Data using Microsoft Excel
It is now time for us to collect data from our target
market.

For your group’s product, come up with serval questions that will
help you determine how your sample from your target market
would perceive your product. Your target market for this activity is
the people in your school (teachers and students).

Below is a copy of Sample Survey Form.


Add more questions fitting for your product. If your product is
not a food product, replace the “taste” rate with durability or
function rate. Have this survey form approved by your teacher
then produce around 15 copies of these.

On _________, start your product testing by letting students and


teachers taste/test your product. Afterward, gather the data and
place them in Microsoft Excel.
Figure 1
Sample
Raw Survey Results of
Starbuko Milk Tea Survey

Tip: To change the orientation of


a text, press Ctrl+1. To open the
Format Cells diaglog box >
Alignment tab> under
Orientation, specify the degrees
you want.
Obviously, this data is raw. Assuming that we have a huge sample, we have
to several formulas to be able to easily analyze the result of this survey.

Step 1. 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 figure 1, determine the average for Quality:
=AVERAGE(F5:F19)

Step 2. Do the same to determine the


average of Taste, Presentation, and
Product.
Step 3. Determine how many people said YES or NO if they were satisfied with the
product or if they would recommend It to their friend. For this, we will use the 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 labels or value that determines if it is to be counted.

Example:
Using figure 1, the formula for getting the number of YES for the criteria is
=COUNTIF(J5:J19,”YES”)
Step 4. If we were to determine if we should sell your product or service to teachers or
students, we can use the AVERAGEIF function.

AVERAGEIF
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 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 (optional) – the actual range of cells that will be averaged, if omitted, the
range will be used instead.

In other instances, you may also want


to use the SUMIF function which works
similarly to the AVERAGEIF function,
except it gets the summation rather
than the average.
Performance Task #3 - Project Plan (Short Folder with Fastener)
Date of Submission: To be announced

Content:
-Product Information
-Cost of Ingredients
(Determine how much is your profit)
-Survey Results
(Determine the average rating of Quality, Taste, Presentation)
(Determine how many people said YES or NO if they were satisfied with the product or if they
would recommend it to their friend)
(Determine if we should sell your product or service to teachers or students)
-Documentation
(Pictures while the product is being developed (Procedure), Final Product and Sales Process
Format:
Font style: Tahoma
Font size: “12”

Please see rubrics for this


task.

You might also like