Artykuł AFIN.NET:
Budżetowanie w AFIN.NET © AFIN 2008
Wojciech Gardziński, 2008.12
15 minut
wystarczy, aby stworzyć podstawy sprawnego systemu budżetowania w Excelu,
wspomaganym przez program AFIN.NET.
Summary:
This article shows how to make an effective budgeting system basing on Excel files. AFIN.NET provides a functionality to enable you to use hundreds of Excel files in different folders. This is also possible in many ways:
1. as a function, getting data from the closed workbooks without using Excel links (!)
2. as a multidimensional analysis
(pivot tables) basing on the AFIN.NET Budget OLAP Cube
3. as a function, getting data from AFIN.NET Budget
OLAP Cube
Na skróty:
Postępowanie z plikami przykładu
Budżetowanie – dostępne rozwiązania
Organizację systemu budżetowania w Excelu
Pobór danych z niezależnych plików Excela, administrowanych
przez szefów jednostek
Jak uruchomić własny system informacji budżetowej?
Wstęp
Szybki start!
Jeżeli chcesz zobaczyć TYLKO
EFEKT KOŃCOWY, pobierz na dysk lokalny plik kostki budżetowej i otwórz go w
Excelu.
http://www.afin.net/samples/data/OLAP/StandardBudgetCube.cub .
Plik otwiera się jako tabela
przestawna. Wartości są zgodne z wartościami w rozproszonych skoroszytach
przykładu-plikach budżetowych. Więcej o kostce budżetowej OLAP, jej strukturze,
zawartości, automatyzacji odświeżania – poniżej.
Wszystko – w AFIN.NET bez
dodatkowych kosztów.
Jak uruchomić przykład?
Uruchomienie przykładu
wymaga działania programu AFIN.NET.
Położenie plików
Pliki
przykładu znajdują się w następujących folderach przykładów AFIN.NET:
·
Model budżetowy – foldery i pliki budżetów komórek:
[AFIN.NET.Folder]\Samples\Data\Excel\SheetRanges\StandardBudgetModel
·
Plik AFIN.NET.IS
- program zasilający kostkę budżetową:
[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.Information.Services\AFIN.NET.IS_MakeOLAPCubFile_Budget.xls
·
Kostka budżetowa
(.cub) i jej plik definicji (.oqy):
[AFIN.NET.Folder]\Samples\Data\OLAP\StandardBudgetCube\StandardBudgetCube.cub
[AFIN.NET.Folder]\Samples\Data\OLAP\PredefinedCubeDefinitions\StandardBudgetCube.oqy
Postępowanie z plikami przykładu
1. Pierwszym krokiem powinno być pobieżne przeglądnięcie
plików budżetów. Są to rozproszone pliki Excela, w których kierownicy centrów
budżetowych wpisują (i administują nimi) swoje dane budżetowe. Można je
dowolnie zmieniać, najpierw zaczynając od prostej zmiany wartości numerycznych,
potem dodania wierszy/kolumn własnych (np. podsumowań częściowych). W
przykładzie nie wolno zmienić wyłącznie 1. wiersza
(nazw kolumn) i nazw arkuszy. Wolno dodawać zarówno dane, jak i dodatkowe
arkusze.
2.
Następnym
krokiem jest przeglądnięcie danych za pomocą funkcji AFIN.NET
Funkcja DANE() / miara StandardBudgetModel
3. Uruchomienie programu, odświeżającego kostkę
budżetową
Należy otworzyć plik-program:
[AFIN.NET.Folder]\Samples\Cases\AFIN.NET.Information.Services\AFIN.NET.IS_MakeOLAPCubFile_Budget.xls
Przyciśnięcie START powinno wygenerować w tymże katalogu plik StandardBudgetCube.cub,
który otwarty pod Excelem jest "kostką budżetową".
4. Otwarcie kostki w Excelu
5. Dostęp funkcjami AFIN.NET do kostki budżetowej StandardBudgetCube.cub
Funkcja DANE() / miara StandardBudgetModel.OLAP
Więcej
o funkcjach – poniżej.
Powstały plik Excela z
tabelą przestawną można zapisać i otwierać niezależnie i również w normalnym
Excelu (czytaj: "raport dla szefa"). Można go też opublikować opcją Plik / Zapisz jako stronę sieci Web / Włącz
interakcję na dowolnej (niekoniecznie obsługiwanej przez system firmy
Microsoft) stronie sieci Web lub w sieci lokalnej.
Plik z wpisanymi funkcjami wymaga
działania programu AFIN.NET, ale można go również opublikować odpowiednią opcją
AFIN.NET – wtedy jest dostępny zarówno z czystego Excela, jak i przez
przeglądarkę internetową (publikacje html)
Budżetowanie – dostępne rozwiązania
Excel
Budżetowanie
w arkuszu kalkulacyjnym (oznacza: „w Excelu”, ze względu na powszechność
rozwiązania) jest stosowane właściwie we wszystkich firmach.
Dzieje
się tak, ponieważ Excel oferuje niespotykaną dostępność możliwości zapisania
różnego rodzaju danych, wyliczenia ich na podstawie innych danych,
skonfrontowania z założeniami, danymi historycznymi, itp. Stworzenie dodatkowej
kopii, wersji, jak również przesłanie jej np. pocztą, nie stanowi żadnego
problemu
Tak więc,
menedżerowie jednostek budżetowych (JB) używają Excela jako, przede wszystkim,
formatki budżetowej.
Również analitycy preferują Excela, z jednej strony jako narzędzie analizy
budżetu firmy jako całości, jak również jako narzędzie organizacji ich pracy.
Jeżeli budżet danej JB nie zgadza się z ich założeniami, przesyłają plik Excela
do szefa JB, plikiem również otrzymują odpowiedź. Fakt, że plików tworzy się
przy tym mnóstwo, ale jest to wygodne.
Niestety,
rozwiązanie to ma też wady. Są one szczegółowo wyliczane przez dostawców
oprogramowania budżetowego. Wymieńmy kilka z nich, aby się do nich
ustosunkować:
·
Brak bezpieczeństwa plików – tu sam Excel nie oferuje praktycznie żadnych
zabezpieczeń.
·
Brak bezpieczeństwa danych w plikach. Hasłowanie dostępu do plików to tylko pewne
złudzenie zabezpieczenia danych – zarówno sam plik jak i jego hasło można łatwo
skasować, natomiast zbezpieczenia danych, gdy plik jest już otwarty, nie ma w
ogóle. I, choć elastyczność to największa zaleta tego rozwiązania, jest ono
podstawą oskarżeń o brak profesjonalizmu.
Poza tym tworzenie coraz to nowych kopii plików również nie sprzyja zachowaniu
tajemnicy danych.
·
Brak centralnego serwera danych
Specyfika
budżetowania wymaga DECENTRALIZACJI danych, tj. w przypadku stosowania arkuszy
kalkulacyjnych - wielu plików, w różnych lokalizacjach. Brak możliwości
informatycznej centralizacji tak powstałego systemu informacyjnego powoduje
powstawanie w firmach rozproszonych modeli budżetowych, połączonych (bardzo
awaryjnymi) łączami międzyskoroszytowymi. To działa, lecz utrzymanie spójności
systemu wymaga ogromnej dyscypliny i jest zawodne.
·
Brak możliwości zabezpieczenia poszczególnych wersji
danych
Natychmiastowe, automatyczne
przeliczanie arkusza kalkulacyjnego w przypadku budżetowania jest wadą. Gdy
raport analityka jest otwierany, skrupulatnie wyłączana jest „aktualizacja
łącz” – odbywa się ona dopiero potem i jest to najbardziej precyzyjnie
wykonywana operacja. Modele budżetowe są
hierarchiczne, podobnie do struktury organizacyjnej firmy. Odświeżanie łącz
prowadzi jednak do nieodwracalnych błędów, gdy naruszona zostanie spójność modelu np. przez zmianę nazwy katalogu.
·
Wady techniczne:
o Gąszcz połączeń międzyarkuszowych (spaghetti łącz)
o Nielimitowany dostęp
o Wiele kopii danych
Programy do budżetowania
To
programy komputerowe „specjalizowane” do budżetowania. Zwykle oparte o
hurtownię danych, oferują również panele zarządzania systemem budżetowym. Poza
oczywistym faktem, że sporo kosztują (serwer, licencje, wdrożenie), ich
największą wadą pozostaje… sztywność. Analitycy radzą więc
sobie dublując dane tych systemów we własnych skoroszytach Excela (Bo tu można
coś po swojemu wyliczyć, przesłać, uzgodnić).
Reasumując:
tam, gdzie istnieją „programy do budżetowania” różnica jest taka, że dane z
Excela przepisuje się do formatek tych programów „bo
takie sa wymogi”, itp.
Co wspomaga AFIN.NET?
AFIN.NET
łączy zalety powyższych rozwiązań, skwapliwie unikając ich wad.
Przede
wszystkim zachowuje elastyczność rozwiązania excelowego – wszyscy, analitycy oraz
menedżerowie, pracują tak, jak umieją, czyli w Excelu.
Narzędzia bazodanowe AFIN.NET pozwalają jednak cały system odpowiednio
zorganizować i zautomatyzować, również wykorzystując technologię OLAP.
Przykład zawiera również opisaną automatyzację.
Organizację systemu budżetowania w Excelu
Organizacja
systemu plików budżetowych jest precyzyjnie przemyślana: W głównym katalogu
analityka znajduje się wiele podkatalogów – osobny dla każdej JB. Mają one
nazwy, jednoznacznie identyfikujące zarówno poziom I. (firma - „Company” - „C”), jak
i poziom II. (Wydział – „Department” – „D”)
Przykład
struktury folderów dla 2 „C” i 3 „D”:
\BudgetModel\C01D01
\BudgetModel\C01D02
\BudgetModel\C01D03
\BudgetModel\C02D01
\BudgetModel\C02D02
\BudgetModel\C02D03
Są
one zabezpieczone na poziomie sieci informatycznej przez służbę IT, która
odpowiada za bezpieczeństwo informacji wewnętrznej. Każdy szef JB ma wgląd
tylko do swojego katalogu, a w nim do swoich plików, z których jeden ma status
„oficjalny” i „zewnętrzny” – jest to informacja o aktualnym stanie wartości
budżetowych danej JB
Poziomy
budżetowania nie muszą dotyczyć Firmy i Wydziału – może to być również podział
Wydział / Grupa budżetowana
Pobór danych z niezależnych plików Excela, administrowanych przez szefów jednostek
(UWAGA!) AFIN.NET nie
stosuje łącz międzyskoroszytowych jako metody przekazywania informacji
pomiędzy plikami Excela.
Ze względu na rozproszenie
danych w plikach w różnych folderach, pobór danych metodą inną, niż za pomocą
łącz międzyskoroszytowych, jest w „czystym” Excelu niemożliwy.
AFIN.NET oferuje dwie,
niezależne metody poboru danych z takiego systemu plików:
- Funkcję arkuszową, zastępującą łącza
międzyskoroszytowe
Do działania funkcji NIE JEST POTRZEBNY żaden dodatkowy program, wystarczy uruchomić Kreatora funkcji GETDATA() – (polski alias: „DANE()”, dostęp: z menu, 1.ikoną na pasku narzędzi lub poprzez skrót [F3]), wybrać odpowiednią miarę z listy miar w Kreatorze i wpisać funkcję
Przykładowa miara: StandardBudgetModel
Można również zastosować funkcję GETDATA() bezpośrednio do danych, zapisanych już w kostce OLAP
Przykładowa miara: StandardBudgetModel.OLAP
Dodatkowo, w przypadku potrzeby skorzystania z funkcji, imitującej łącze międzyskoroszytowe, nie stosującej żadnych ułatwień użytkowych, również jest taka możliwość (niezależnie) – poprzez funkcję GETDATALINK()
=GETDATALINK("'D:\AFIN\AFIN.NET\Samples\Data\Excel\SheetRanges\BudgetModel\C01D01\[Budget2008C01D01.xls]Ver1'!$C$2")
(Wynik: 901010801001)
- Program AFIN.NET.IS
Program posiada ustawione parametry dla wszystkich katalogów przykładu StandardBudgetModel i działa po wciśnieciu przycisku Start na arkuszu.
Do wykonania kostki potrzebuje pobrać dane-tabele budżetowe z kilkudziesięciu plików (każdy w innym katalogu), ułożyć je w sposób poprawny informatycznie (pole miary, odpowiednie pola wymiarów), odpowiednio uszlachetnić dodatkowymi atrybutami (np. miejscem pochodzenia danej tabeli - poprzez określenie atrybutów „C01” i „D01”) oraz zapisać dane do specjalnie do tego celu przygotowanej bazy danych Accessa: WorkBase.mdb
Posiadanie Accessa nie jest wymagane.
Na zakończenie swojego działania, AFIN.NET.IS uruchamia automatyczną budowę kostki OLAP ze wszystkimi danymi z plików budżetowych (opis szczegółowy poniżej)
Automatyzację poboru danych (przeliczania modelu) z kilkudziesięciu-kilkuset plików, rozproszonych w wielu folderach
AFIN.NET.IS jest to,
zapisany w zwykłym arkuszu Excela, program przetwarzania danych dla potrzeb ich
analizy. Więcej o
AFIN.NET.IS…
Wszystkie procedury wykonuje
krok po kroku – umożliwia to budowę bardzo skomplikowanych procesów, np., w
przypadku budowy bazy dla budżetowej kostki OLAP, oznacza to pobór kilkudziesięciu
całych tabel (budżetów), pocięcia ich na elementy dla poszczególnych miesięcy,
powtórnego złożenia w jedną, bardzo „długą” tabelę z odpowiednimi, zadanymi
wcześniej atrybutami, zapisanie do bazy i zbudowanie na tej tabeli kostki OLAP.
Odbywa się to w kilkudziesięciu wierszach – krokach procedury, z możliwością
sparametryzowania poszczególnych jej kroków danymi w arkuszu. Oznacza to, że
użytkownik może samodzielnie dopisać ich dowolną ilość poprzez kopiowanie i
zmianę parametrów i zwykłe kopiowanie formuły danego kroku.
Budowę pliku budżetowej kostki OLAP – centralnej bazy danych budżetowania, wersjonowanej i publikowalnej
Przykład kostki budżetowej
AFIN.NET opiera się na standaryzowanej strukturze danych, stosowanej przez
większość firm budżetujących swoje koszty.
Składa się z
- miary: MyMeasure – może mieć dowolne znaczenie
biznesowe
- wymiaru daty, ale celowo(!) zawartego
w 2 wymiarach niezależnych:
- wymiaru: rok
- wymiaru: miesiąc
- wymiaru jednostki budżetowej, dwustopniowego,
zawierającego podwymiary:
- podwymiar1: firma (Company)
- podwymiar2: wydział (Department)
- wymiaru pozycji budżetowane, dwustopniowego,
zawierającego podwymiary:
- podwymiar1: temat (Topic)
- podwymiar2: pozycja (Item)
Kostkę lub kostke dodatkową
można zbudować samodzielnie i podłączyć do programu zasilającego.
Jak uruchomić własny system informacji budżetowej?
Jeżeli
AFIN.NET działa na danym komputerze, wystarczy wykonać następujące czynności:
- Przeglądnąć pliki w folderze ‘…\AFIN.NET\Samples\Data\Excel\SheetRanges\BudgetModel’
- Otworzyć plik:
’…\AFIN.NET\Samples\Cases\AFIN.NET.InformationServices\AFIN.NET.IS_MakeOLAPCubFile_Budget.xls’ - Przeliczyć arkusz [Ctrl+Shift+F9]
(ścieżki powinny się uaktualnić samoczynnie) - Uruchomić program poprzez naciśnięciu przycisku
Start w arkuszu lub skrótu [Ctrl+Q]
- Otworzyć w Excelu (może być bez dodatku
AFIN.NET) kostkę budżetową:
’…\BudgetModel1.cub’ (Otworzy się jako tabela przestawna w skoroszycie Excela) i zapisać skoroszyt na dysku jako normalny skoroszyt Excela (będzie go wtedy można odświeżać bez konieczności zamykania) - Zmieniać dane budżetowe w skoroszytach
budżetowych w katalogach …\C01D01 (i podobnych), uruchamiać krok 4 i obserwować,
jak zmiany w plikach źródłowych odzwierciedlają się w budżetowej kostce
OLAP (po odświeżeniu danych należy standardowo odświeżać tabelę
przestawną)
System kontroli budżetu
gotowy – reszta to organizacja systemu budżetowania w firmie, zabezpieczenie
folderów (IT) itp.
W przypadku wystapienia
błędów przetwarzania danych, należy najpierw skupić się na uruchomieniu
gotowego przykładu AFIN.NET, a następnie powrócić do prób z własnym systemem.
Błędy mogą występować z różnorakich przyczyn:
- Błędy w definicjach katalogów (błąd operatora)
- Brak zainstalowanych sterowników ODBC
(Instalacja Excela lub Accessa je zawiera w standardzie -> IT)
- Brak zainstalowanych dodatków ADO MD (od Excela
XP SP3 wzwyż – standardowo w systemie)
Podsumowanie
|
Cecha |
Excel |
Rozwiązania
budżetowe |
|
Elastyczność |
Pełna |
Ograniczona
funkcjonalnością systemu |
|
Cena |
Bardzo niska (licencje
AFIN.NET wyłącznie na stanowiskach analityków, wdrożenie
(jeżeli na podstawie przykładu – brak kosztów) |
Zależy od systemu, w
Polsce do 50.000 PLN |
|
Szybkość wdrożenia |
Natychmiast |
Kilka miesięcy (Zakup
systemu, analiza, wdrożenie techniczne) |
|
Bezpieczeństwo systemu
plików i bezpieczeństwo danych |
Zabezpieczenia wewnętrzne
sieci |
Zabezpieczenia systemu
(dodatkowe hasła, itp.) |
|
Administracja |
Pliki budżetowe – szefowie
JB, Dział analiz |
Specjalny moduł
administacji |