Formula Excel

Special request by Iyamba Ebeiyamba
Special thanks to Google Translate

Document License:
Copyright © 2008 IlmuKomputer.Com
All the documents on IlmuKomputer.Com can be used, modified and distributed freely for noncommercial purposes (profit), in condition may not delete or modify attributes of the author and copyright statement is included in each document. Not allowed to do a rewrite, unless given permission in advance from IlmuKomputer.Com.

Every internet cafe must need a tool to record transactions every day that can not be done manually. There are many internet café’s billing software that can record all transactions starting from the rental service, scan, print, or even a soft drink that supports internet café’s business. In addition, the software also provides features which connected with its operating system, to the financial statements itself. But internet café’s billing software are quite expensive. With little ability of Excel, actually you can create your own Internet billing easily. These tips and tricks is to discuss briefly and clearly how to make internet café’s billing easily with the help of Microsoft Excel.


MAKING FRAMEWORK

Determining Price
First of all, you have to determine the tariffs. Regular user tariffs may be different from the Student user or Member user. Also the first price-per-minute may be different to the next price-per-minute. With this tariff it will be easier to raise rates if it wants to. Make a Code field, Type, First Minutes, Price, Next Minutes, and Price. An example follows:



Price mentioned above is the assumption for Regular users is Rp. 4,500/hour and for Member and Student users is Rp. 4,000/hour. You can change it according the tariff you’re using.

Creating Tables
In designing a internet billing, you will determine what columns are needed in a calculation table. Make the columns as Client Number, Code, Type, Start Time, Finish Time, Duration / Status, Minutes Duration, Print, Scan, Amount, and Description. An example follows:

• Client: A client / workstation that is used.
• Code: the code as the user type for the Regular “B”, “M” for the member, or “S” for Student
• Type: explanation of the Code field
• Start time: the time internet rental is started
• Finish time: the time internet rental is completed
• Duration: the time used for Internet rental in hh:mm format. If the user is still active (Finish Time not inputted), then this column will shown "ACTIVE" status.
• Duration Minutes: Duration column is translated in minutes. For instance duration 01:29 is 89 minutes.
• Print: if there is any additional for Print
• Scan: if there is any additional for Scan
• Amount: the amount actually paid by the user.
• Description: description field can be used such as the use of a webcam, soft drinks, or other information.

Internet Café’s Name
You certainly can display the name and logo cafe on these columns. Add well as the date and total revenues today. An example follows:



CREATING FORMULAS

Before you create a formula in these columns, you should first determine the price-table’s name by blocking the price-table from F6 to K8 and select Insert > Name> Define. Enter "tabel" in the Names in workbook and then select Add. The price table has been defined as "tabel" to facilitate the writing of formulas.

Column A: Client
To facilitate you, use data validation to these fields through the menu Data> Validation. In the Allow dropdown menu, select List, and the Source text box, fill with the client computer you have. Suppose you have 10 clients, then fill 1;2;3;4;5;6;7;8;9;10 in the textbox. Be sure the list separator you’re using. Replace the separator with "," (comma) if you use the English (United States) on Regional and Language Options in Control Panel.

Column B: Code
Same as column A, use the validation data by filling in the value of B;M;S on textbox Source.

Column C: Type
Use this formula:
=IF(ISNA(VLOOKUP(B11;tabel;2;0));"";VLOOKUP(B11;tabel;2;0))

Column D: Start Time
Change cell’s format with hour format via Format > Cells and the Number tab select Time with 13:30 format. Fill out the start time columns with hh:mm format.

Column E: Finish Time
Do the same thing like column D.

Column F: Duration/Status
Use this formula:
=IF(AND(B11="";E11="";A11="");"";IF(AND(B11<>"";E11="");"ACTIVE";E11-D11))
Change the cell’s format of this column using format like D & E.

Column G: Minutes Duration
Use this formula:
=IF(OR(B11="";E11="");"";(HOUR(F11)*60)+MINUTE(F11))
Change the cell’s format of this column using Number format.

Column H&I: Print & Scan
Change the cell’s format of this column using Number format with comma separator.

Column J: Amount
Use this formula:
=IF(OR(B11="";E11="");"";IF(G11<VLOOKUP(B11;tabel;3;0);(VLOOKUP(B11;tabel;4;0))+H11+I11;(((P11+Q11)*VLOOKUP(B11;tabel;6;0))+VLOOKUP(B11;tabel;4;0)+H11+I11)))

Column O:
Use this formula:
=IF(OR(B11="";E11="");0;G11-VLOOKUP(B11;tabel;3;0))

Column P:
Use this formula:
=IF(OR(B11="";E11="");0;ROUNDDOWN(O11/VLOOKUP(B11;tabel;5;0);0))

Column Q:
Use this formula:
=IF(OR(B11="";E11="");0;ROUNDUP(O11/VLOOKUP(B11;tabel;5;0);0)-P11)

Hide columns O, P, & Q using command Format > Column > Hide


After all the columns given the formulas and format correctly, provide about 100 lines with the assumption of approximately 100 transactions per day. Then add the column Today Total Income in row 101. Give SUM formula in column Print, Scan, and Total. Color other than white means the area should not be changed or deleted. The operator may input only in white columns. An example follows:


At the bottom of the table, you can use for statistics of the usage.


Use the following formula:

Row 154: Duration Hours Used
Use this formula:
=SUM(F11:F150)

Row 155: Duration Minutes Used
Use this formula:
=SUM(G11:G150)

For rows 154 and 155, change cell’s format to hh:mm

Row 156: Regular User
Use this formula:
=COUNTIF(B11:B150;"b")

Row 157: Member User
Use this formula:
=COUNTIF(B11:B150;"m")

Row 158: Student User
Use this formula:
=COUNTIF(B11:B150;"s")

Row 159: Total Users
Use this formula:
=SUM(D156:D158)

For rows 156 to 159, change cell’s format to Number format

Row 160: Print Revenue
Use this formula:
=H151

Row 161: Scan Revenue
Use this formula:
=I151

Row 162: Internet Revenue
Use this formula:
=D163-D160-D161

Row 163: Total Revenues
Use this formula:
=J151

For rows 160 to 162, Change cell’s format to Number format with comma separator.

You may also be able to provide written information at the bottom of this file as follows:

Description:
- Only the white columns that can be filled
- Fill in the fields as instructed
- Changing Price requires a password
- Adding new rows or change the format requires a password
- Do not forget to always save after enter the data

Change the sheet’s name as date and make as many as 30 or 31 sheets. For the formula Today’s Total Income at the top, can refer to the amount in column J151 (formula: = $J$151).


Other Tips and Tricks

To provide clearer guidance to your Operator, you'll need to make the validity of the format to prevent input errors or loss of the formulas that you create through the menu Data> Validation. Determine the validation value as mentioned in the Column A: Client. At Massage Input tab, input the title and content of the message according to the column. On the Error Alert tab, input the title and content of the message in case of input error.

In addition, you may want to truly protect the formula columns that you create. Block the white columns and disable check boxes in the menu Format> Cell > Protection tab > Locked and then create a password on the Tools menu > Protection > Protect Sheet. Enter the password twice.