Planowanie sprzedaży w pizzerii przy ograniczonej ilości zamówień „z dostawą”

Planowanie sprzedaży w pizzerii przy ograniczonej ilości zamówień „z dostawą”

Właściciel lokalnej pizzerii zastanawia się nad wyborem promocji na kolejny miesiąc. Przy wyborze konkretnych promocji kieruje się zyskiem firmy a także ograniczoną ilością promocji jakie może zrobić w ciągu jednego miesiąca. Wąskim gardłem przy planowaniu sprzedaży jest ilość dostaw do mieszkań klientów. Z racji ograniczonej ilości dostawców dzienna ilość dostaw nie może przekroczyć 90. Tabela poniżej przedstawia poszczególne promocje jakie były przeprowadzane do tej pory, a także odpowiadające im średnie tygodniowe ilości zamówień, procent zamówień „z dostawą” i zysk jednostkowy z każdego zamówienia.

01

W wierszu 1 podanych zostało dziesięć promocji jakie były przeprowadzane w pizzerii. Każdej z promocji odpowiada średnia tygodniowa ilość zamówień (wiersz 4), procentowy udział zamówień z dostawą do domu (wiersz 6) a także jednostkowy zysk z zamówienia (wiersz 8). I tak dla przykładu dla promocji „napój gratis” średnia tygodniowa ilość zamówień to 278 (komórka C4), z których 40% stanowią zamówienia z dostawą do domu (komórka C6). Średni jednostkowy zysk wynosi 9 zł (komórka C8). W wierszu 10 obliczono całkowity zysk ze sprzedaży poprzez poszczególne promocje. Obliczono to mnożąc ilość zamówień przez zysk jednostkowy. Dla przykładu w komórce C10 wpisano =C8*C4. Funkcję tą przeciągnięto do komórek D10:L10 i w ten sposób otrzymano całkowity zysk dzięki poszczególnym rodzajom promocji w pizzerii.

Wybór promocji dla klienta

Właściciel pizzerii wie, że maksymalnie w ciągu tygodnia może przeprowadzić 5 promocji. Celem tej analizy jest pokazanie w jaki sposób należy wybrać pięć z dziesięciu promocji aby zysk był jak największy przy ograniczeniu dotyczącym ilości dostaw. Aby to obliczyć należy wpisać dane tak jak na rysunku poniżej.

02

W wierszu 14 powielono rodzaje promocji z wiersza 1. W komórkach C17:L17 wpisano 0. To w tych komórkach Excel wskaże nam wynik obliczeń. W komórce M17 zsumowano komórki C17:L17. Suma wyświetlona w tej komórce będzie mniejsza od komórki O17, w której wpisano 5. Wynika to z ograniczenia, iż maksymalna tygodniowa ilość promocji jaka opłaca się właścicielowi pizzerii to 5. W komórkach C20:L20 wyliczono ilości dostaw przy danych promocjach, (czyli średnia ilość zamówień) * (procent zamówień z dostawą) * (zakwalifikowanie promocji). Dla komórki C20 będzie to =C6*C4*C17. W komórce M20 zsumowano komórki C20:L20. W komórce 20 podzielono M20 przez 7 co da nam dzienną ilość zamówień, która ma być mniejsza niż 90 (komórka O20). W komórkach C23:L23 wyliczono zysk z promocji zakwalifikowanych na kolejny miesiąc. W związku z tym w komórce C23 wpisano =C10*C17 i przeciągnięto to do komórek D23:L23. W komórce O23 obliczono sumę zysków. Ponieważ celem właściciela pizzerii jest maksymalizacja zysku, komórkę tą będziemy używać jako cel analizy.

Maksymalizacja przychodu

Teraz należy przystąpić do obliczeń za pomocą dodatku „solver”. Dodatek ten znajduje się w menu „analiza” na karcie „dane”.

03

Po kliknięciu w solver wyskoczy okno dialogowe, które należy wypełnić tak jak na rysunku poniżej.

04

Ustaw cel – to komórka, w której właściciel pizzerii będzie maksymalizować zysk ze sprzedaży – O23. Poniżej zaznaczamy „max” – zależy nam na maksymalnym zysku.

„przez zmienne komórek zmiennych” wstawiamy C17:L17, czyli komórki w których excel dokona wyboru konkretnych promocji.

W „podlegającym ograniczeniom” należy dodać ograniczenia. W tym celu należy kliknąć w pole „Dodaj” po prawej stronie. Powinno wyskoczyć okno dialogowe w którym dodamy warunki ograniczające.

05

Pierwsze ograniczenie dotyczy maksymalnej ilości promocji. Według właściciela pizzerii najlepszym rozwiązaniem jest 5 promocji. Dlatego też należy wpisać M17<=O17. Po kliknięciu w „dodaj” okno dialogowe doda dany warunek do analizy i wyskoczy ponownie puste. Teraz należy dodać warunek ograniczający ilość dostaw. W tym przypadku jest to maksymalnie 90 dziennie, także należy wpisać N20<=O20. Po kliknięciu w „dodaj” należy dodać warunek mówiący, iż komórki C17:L17 powinny wynosić 0 lub 1. 0 oznacza iż dana promocja nie została wybrana na ten miesiąc, 1 oznacza iż została wybrana. Aby określić ten warunek ograniczający należy wpisać C17:L17 i wybrać „bin”. Po kliknięciu w OK wyskoczy okno dialogowe solver z umieszczonymi już warunkami ograniczającymi.

06

Teraz zaznaczyć trzeba jeszcze „ustaw wartości nieujemne dla zmiennych bez ograniczeń” i można kliknąć w pole „rozwiąż”. Po dokonaniu obliczeń, których szybkość  zależy od procesora komputera wyskoczy okno „wyniki dodatku solver” w którym nie należy nic zaznaczać a tylko kliknąć OK.

Wyniki planowania sprzedaży

Wyniki analizy przedstawiają się następująco:

07

Jak widać maksymalny zysk, przy tych założeniach, to 19.201 zł (komórka O23). Promocje przynoszące maksymalny zysk to te dla których w komórkach C17:L17 wyskoczyły jedynki. Promocji jest pięć co zgadza się z oczekiwaniem właściciela pizzerii. Dzienna ilość dostaw wynosi 89 (komórka N20) i jest mniejsza od zakładanej o jedną. Excel wskazał promocje, które przynoszą maksymalny zysk. Jeśli właściciel pizzerii będzie chciał zmienić promocje to zysk będzie mniejszy od tego, który został obliczony dla tych promocji.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *