페이지 이력

연도별 손익계산서를 웹에서 손쉽게 확인할 수 있는 보고서입니다.

연도별로 엑셀 파일을 쌓아 관리하던 방식에서 벗어나, 웹을 통해 보다 편리하게 손익 데이터를 열람하실 수 있습니다.

월별 당기순이익 및 매출액 추이 차트를 통해 기간별 경영성과를 한눈에 파악할 수 있고, 주요 이익률 지표를 시각적으로 제공하여 연간 수익성을 직관적으로 이해할 수 있습니다.

제공된 샘플 엑셀파일을 활용하여 손익계산서를 웹으로 변환해보세요.

Image Removed

Step 1. 엑셀 파일 준비

1. • 샘플로 제공된 엑셀 파일을 EPA 게시판에서 다운로드합니다.

Step 2. 엑셀 시트 이름 규칙

...

다운로드 받은 엑셀 파일에 있는 데이터를 활용해 손익계산서를 작성합니다.

각 시트에 대해 먼저 알아보겠습니다.

...

Step 3. 변수 설정, V시트에 데이터 연결하기

...

엑셀의 '이름정의'기능을 사용해 P시트에서 '연도' 변수를 설정합니다.

...

This report allows you to easily view annual income statements directly on the web.

Move beyond managing stacked Excel files each year and being to view and analyze profit and loss data more conveniently online.

Explore monthly net income and revenue trends, and visualize key profitability indicators to quickly grasp overall business performance.

Try converting your own income statement to the web using the provided sample Excel file and experience how seamless financial reporting can be.

Image Added

Step 1. Prepare your Excel file

1. • Download the sample Excel file from the Learning Experience Samples.
     Image Added

Step 2. Excel sheet naming conventions

• • Create the Income Statement report by utilizing the data in the downloaded Excel file.

  • Let’ start by understanding the role of each sheet in this report.

    • Sheet V : V_ProfitAndLoss
      When multiple sheets exist in Excel, the sheet to be displayed on the web must follow the format ‘V_(letters)’ or ‘V(number)’.
      
      Image Added
      
      

    • Sheet D : D1
      This sheet allows you to enter data or retrieve it by connecting to a database.
      Image Added
      
      

    • Sheet P : P1
      Create variables to be used when querying data. You can define cell values as variables by using Excel’s ‘Name Manager’ feature.
      In this exercise, we’ll use ‘Year’ as a variable to view the income statement by year.Image Added
      
      
      

      
      

      참고사항
      title Excel Sheet Naming Convention
      The naming convention for Excel sheet is as follows: Image Added

      
      

Step 3. Set variables and connect data to the V sheet

• • Set the ‘Year’ variable in the P Sheet using Excel’s ‘Name Manager’ feature.

  • The variable can be used as a filter condition on the web screen. Once the condition is set, only data matching the variable’s value will be retrieved.
    Image Added
    
    

...

Step 6. 보고서 저장하기

• 완성된 보고서를 내 폴더에 저장합니다.
  Image Removed
  
  • EPA 사이트 메인 화면 좌측 [메뉴] > [개인]을 선택합니다.
  • '돋보기' 버튼을 클릭해 보고서 목록을 새로 고침 하면 저장된 보고서를 확인할 수 있습니다.
  • 연도를 바꾸고 '조회' 버튼을 클릭하면 데이터가 갱신 되는 것을 확인할 수 있습니다.
    Image Removed

    Use Excel’s SUMIFS function to calculate the monthly values corresponding to the selected year for each item.
    * Year : VS_YYYY
    * Month : Jan, Feb, Mar …
    * Value : Sales, COGS (Cost of Goods Sold) …
    Example) Cell D17 : =SUMIFS('D1'!$C:$C,    'D1'!$A:$A, VS_YYYY,   'D1'!$B:$B,V_손익계산서ProfitAndLoss!D$14)
    Image Removed
    

    데이터 입력을 완료하면 손익계산서 보고서가 완성됩니다. 완성된 엑셀 파일을 저장합니다.
    Image Removed

Step 4. 웹 화면으로 변환하기

...

EPA 사이트 메인 화면으로 돌아가 좌측 [도구]에서 i-AUD Designer를 클릭 합니다.

...

저장한 엑셀 파일을 마우스로 끌어 i-AUD Designer 도구 창 위에 놓습니다.

...

변환된 엑셀 화면이 웹 브라우저의 화면 크기에 맞게 자동으로 조정되도록 설정합니다.

...

Border에서 Line Type을 'None'으로 설정하면 디자이너 화면에서 보고서의 테두리가 표시되지 않도록 설정 할 수 있습니다

Step 5. 조회 조건 설정하기

...

• • Image Added
    
    

  • Once you have finished entering the data, the Income Statement report is complete. Save the completed Excel file.

Step 4. Convert the Excel file for the web using i-AUD Designer

• • • From the EPA main screen, go to [Menu] > [Tool] and launch i-AUD Designer.

    • Drag and drop the saved Excel file into the i-AUD Designer window.

    • The Excel report now appears in the web view, preserving the original layout.
      Image Added
      
      

    • Ensure the report automatically resizes to fit the web browser window.

    • In the Properties pane on the right, check Docking: Left, Right, Bottom.
      
      Image Added
      
      

    • To remove the border from the report on the designer screen, set the Line Type property under Border to ‘None’.

Step 5. Configure the Search filter

• • • Next, add and configure the 'Year' filter to view the Income Statement report by year on the web.
    • Place a ‘Year’ label and a calendar so that users can select the year, and add a ‘Refresh’ button to refresh data when clicked.
      
      
    • From the UI Bot menu at the top, click Label and place it on the designer. In the Properties pane, change the Text propertyto ‘Year’, or press F2 on your keyboard. You can also customize the label’s font, color, and other style properties in Style.
      
      Image Added
      
      
      
    • Next, add the calendar.
    • From UI Bot > Calendar, click Year and place it next to the label..
    • Set the calendar’s Name property to 'VS_YYYY', the same variable you defined in the Excel’s ‘Name Manager’. This ensures that the value selected in the calendar is used as the filter condition.
      Optionally, set InitDate to define the default value when the report is first opened.
      
      Image Added
      
      
      
    • Finally, add the ‘Refresh’ button to the designer.
    • From the UI Bot menu at the top, click Button and place it in the desired location on the designer.
    • Similar to the ‘Year’ label, change the button’s Text property to ‘Refresh’, and adjust its formatting in Style.
      You can use BoxStyle to quickly apply a saved style to the button.
      Image Added
      
      
    • Set up an event so that when the ‘Refresh’ button is clicked, the report data is refreshed using the value from the Year variable.
    • Click Process Bot at the top. In the Objects panel, double-click Button with a name ‘Button’; in the Activities panel, double-click 'Refresh'.
    • In the Refresh module details, select the MXGrid component to refresh, and drag a connection line from the Button to Refresh Module.
      (Note: Dragging and dropping the Excel file onto the web screen automatically creates the MXGrid component.)
      Image Added
      
      
    • Save and close the Process Bot.
    • The report setup is now complete.

Step 6. Save your report

• • • Save the completed report to My Folder.
      Image Added
      
      
    • From the EPA main screen, go to [Menu] > [Individual] in the left-hand sidebar.
    • Click the Search(Magnifying glass icon) button to refresh the report list and confirm your saved report.
    • Change the year and click the 'Search' button to see the data updated.
      
      Image Added