Microsoft Excel Project - Simple Functions and Formulas

 Hey! Welcome back!!


In this blog, I will show you a project using simple functions and formulas in Excel. Here is a CPU and Monitor sales report year 2023. I'm sorry in advance that some sentences and words in this report might be in Indonesian. Feel free to contact me if you have any suggestions or questions about it.
Thankyou 😁

CPU and Monitor Sales Report

(Rekapitulasi Penjualan CPU dan Monitor)

There are three sub-tables in this report. On the top, there's a company heading. In the middle is the main table containing the sales report. At the bottom is a reference table, which is used for calculating some elements in the sales report.

Here are the conditions and functions that I use in this project : 

  • Paper size A4
  • Landscape orientation
  • Margin 2222
  • Custom Border and Shading
  • Merge and Center for some merge cells
  • Cells alignment
  • Accounting format number
  • Custom Header Footer

The formulas and functions for this project are :

Multiplication (*)

"HARGA TOTAL" and "DISCOUNT" on both CPU and Monitor are calculated using simple multiplication (*).

 "HARGA TOTAL" was obtained by multiplying "JUMLAH UNIT" in the main table and "DAFTAR HARGA UNIT" in the reference table.

 "DISCOUNT" was obtained by multiplying "TOTAL" in the main table and "% DISC" in the reference table.

Addition (+) and Subtraction (-)

"TOTAL" was obtained by adding (+) "HARGA TOTAL" on both CPU and Monitor.

"TOTAL BERSIH" was obtained by subtracting (-) "TOTAL" with the addition (+) of "DISCOUNT" on both CPU and Monitor.

Absolute Reference (F4 )

For easier calculation in each row, I use absolute reference for every calculation using the reference table, such as "HARGA TOTAL" and "DISCOUNT". F4 was used to make the absolute references and mark them with the symbol "$"

The formula of "HARGA TOTAL" for Monitor in the first row (Bulan 1) is :

=B7*$C$26

"B7" is the "JUMLAH UNIT" column and "$C$26" is the absolute reference of the "DAFTAR HARGA UNIT" for the Monitor.

The same goes for the "HARGA TOTAL" for CPU and "DISCOUNT".

SUM, MAX, MIN, AND AVERAGE

For the calculation of "TOTAL" I summed up the "TOTAL BERSIH" using "SUM"
=SUM(H7:H18)
H7:H18 is the range of the "TOTAL BERSIH" in each month (Bulan) 
 
 "MAKSIMUM" was obtained by finding the maximum number of "TOTAL BERSIH" using "MAX" 
=MAX(H7:H18)
 
"MINIMUM" was obtained by finding the minimum number of "TOTAL BERSIH" using "MIN" 
=MIN(H7:H18)

 "AVERAGE" was obtained by calculating the average of "TOTAL BERSIH" using "AVERAGE" 
=AVERAGE(H7:H18)


Files (xlsx and pdf) :

CPU and Monitor Sales Report 

Comments

Post a Comment

Popular posts from this blog

Microsoft Excel Project - Vlookup and Hlookup

Image
  Hey, how are you? Welcome again!! So now we are moving to some complex functions and formulas, which are Vlookup and Hlookup. VLOOKUP & HLOOKUP VLOOKUP is a function to read or find data vertically. The function form is :     =Vlookup(lookup_value;  table_array; column_index_num; range_lookup)     "lookup_value": the value as the basic table reading     "table_array": the range of the reference table     "column_index_num": the number of the column position on the reference table     "range_lookup": TRUE for approximate results, or FALSE for accurate results. HLOOKUP is a function to read or find data horizontally. The function form is :     =Hlookup(lookup_value;  table_array; row_index_num; range_lookup)     "lookup_value": the value as the basic table reading     "table_array": the range of the reference table     "row_index_num": the number of the row position on the refe...
Read more

Microsoft Excel Project - Simple Tabel

Image
 Hello, welcome back!! Last week, I finished Microsoft Office Basic Training organized by BLKIP Bali. I learned a lot about  Word, Excel, PowerPoint, and any other soft skills needed in the workplace. Starting now, I will upload some of my portfolios to show off my skills in Microsoft Office. In this post, I will show you some simple Excel projects, regarding bills and invoices. This project does not include any formulas, just some simple Excel templates. Because the training was in Indonesian, some of the words and sentences in the portfolios might not be in English. Feel free to contact me if you have any suggestions or questions about it. Thankyou 😁 Bills ( Fraktur Tagihan ) Bills Here are the conditions and functions that I use in this project :  Paper size A4 Landscape orientation Margin 2222 Custom Border and Shading Merge and Center for some merge cells Cells alignment Accounting format number Custom Header Footer Invoices Invoice Here are the conditions...
Read more

SQL Project #2 - Myanmar 3 Superstore

Image
  (photo credit:  https://unsplash.com/photos/kLwgUwv_m_g?utm_source=unsplash&utm_medium=referral&utm_content=creditShareLink ) In this post, I will show you my second SQL Project about data analysis of Superstores. These superstores are located in Myanmar and have 3 branches. Branch A located in Yangon City, Branch B is located in Mandalay City, and Branch C is located in Naypyitaw City. I got the data from AUNG PYAE on Kaggle : https://www.kaggle.com/datasets/aungpyaeap/supermarket-sales I used the same tools as the previous project, MySQL Workbench and Tableau. You can see the SQL code on my GitHub account:  https://github.com/ratnaprasada/3Superstore So here's the analysis, feel free to contact me if you have  any suggestions or questions about it. Thankyou 😁
Read more