benderindividualproject.xlsm

    0

    5.ID

    Do Not Alter or Delete this Worksheet or you submission cannot be graded! DirVer
    Action Name Panther ID Date/Time S01
    Start S01 Ahadu Solomon 202020202 20-Sep-2021 05:31 Microsoft Office User
    Starter Sheet None 0 Jan-01 00:00
    Welcome to Microsoft Excel version 16.53 build 912 running on Macintosh (Intel) Version 11.2.3 (Build 20D91)!

    Financial information

    Donut Information Spring 2021
    Based on the below data, create the profit model for Donuts to Go. Ahadu Solomon
    Assume that each customer will buy one donut and one cup of coffee
    Enter totals
    Time period Fixed Costs
    Revenue: Cup of Coffee $2.99 Varible Costs
    Revenue: Donut $2.50 Coffee
    Donut ingredients per donunt) per donut $0.60 Donut
    paper products: napkins, plates etc
    Insurance month $300.00
    Maintenance & Repairs to equipment month $0.00
    Marketing & Promotion: Advertising month $100.00
    Coffee per cup $0.35
    Coffee cups per cup $0.15
    Payroll: Wages (Owner/ Manager) month $2,400.00
    Payroll: Wages (per Employees) month $1,200.00
    Donut and Coffee equipment rent month $500.00
    Professional Fees: Accounting month $50.00
    Professional Fees: Legal month $25.00
    Powdered and Liquid Beverages $0.00
    Rent month $1,000.00
    Previous research expense for Donuts advancements $1,500.00
    Supplies: Office month $25.00
    Utilities month $200.00
    Additional Data
    Operations
    Monthly Production 4000
    Lost Sales 3
    Day old revenue 1.25
    High demand, % above Average 22%
    Low Demand, % below average 26%
    Franchise Operations
    Monthly Fixed Expense increase 4,350.00
    Monthly Production Increase 32%
    Monthly demand increase 22%
    States of Natures
    High demand 20.00%
    Average demand 55.00%
    Low demand 25.00%
    Total

    SI and regression Pt 1

    Month Demand Yearly average Seasonal Index Average SI Deseasonalized Time period Regression Output
    1/1/19 2422 0.8226 0.7817 3098.3966 1 CLICK CELL J2 as output cell for regression SUMMARY OUTPUT
    2/1/19 2564 0.8708 0.8020 3196.9936 2
    3/1/19 3080 1.0460 0.8793 3502.7679 3 Regression Statistics
    4/1/19 3004 1.0202 0.9846 3050.9105 4 Multiple R 0.7763705501
    5/1/19 2595 0.8813 0.9926 2614.4145 5 R Square 0.6027512311
    6/1/19 3654 1.2410 1.1140 3280.0933 6 Adjusted R Square 0.5910674438
    7/1/19 2725 0.9255 0.8306 3280.6930 7 Standard Error 258.1234968348
    8/1/19 2383 0.8093 0.8672 2747.8748 8 Observations 36
    9/1/19 2432 0.8260 0.8803 2762.6766 9
    10/1/19 2744 0.9319 1.0535 2604.6423 10 ANOVA
    11/1/19 3600 1.2227 1.2843 2803.0318 11 df SS MS F Significance F
    12/1/19 4130 2944.4167 1.4027 1.5298 2699.6276 12 Regression 1 3437237.51395852 3437237.51395852 51.5886856384 0.0000000263
    1/1/20 2634 0.7794 0.7817 3369.6022 13 Residual 34 2265343.14701949 66627.7396182204
    2/1/20 2508 0.7421 0.8020 3127.1684 14 Total 35 5702580.66097802
    3/1/20 2653 0.7850 0.8793 3017.1569 15
    4/1/20 3270 0.9676 0.9846 3321.0644 16 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
    5/1/20 3770 1.1155 0.9926 3798.2053 17 Intercept 2816.1576303201 87.8655628239 32.0507550377 5.4439539849223E-27 2637.593322713 2994.7219379271 2637.593322713 2994.7219379271
    6/1/20 3588 1.0617 1.1140 3220.8469 18 Time period 29.744677185 4.1412555255 7.1825264106 0.0000000263 21.3286333815 38.1607209884 21.3286333815 38.1607209884
    7/1/20 2658 0.7865 0.8306 3200.0301 19
    8/1/20 3065 0.9069 0.8672 3534.2998 20
    9/1/20 3095 0.9158 0.8803 3515.8240 21
    10/1/20 3634 1.0753 1.0535 3449.4425 22
    11/1/20 4350 1.2871 1.2843 3386.9967 23
    12/1/20 5330 3379.5833 1.5771 1.5298 3484.0230 24
    1/1/21 2810 0.7431 0.7817 3594.7541 25
    2/1/21 2999 0.7931 0.8020 3739.3852 26
    3/1/21 3051 0.8069 0.8793 3469.7873 27
    4/1/21 3653 0.9661 0.9846 3710.0453 28
    5/1/21 3709 0.9809 0.9926 3736.7489 29
    6/1/21 3930 1.0393 1.1140 3527.8507 30
    7/1/21 2949 0.7799 0.8306 3550.3720 31
    8/1/21 3348 0.8854 0.8672 3860.6315 32
    9/1/21 3400 0.8992 0.8803 3862.2946 33
    10/1/21 4361 1.1533 1.0535 4139.5209 34
    11/1/21 5079 1.3432 1.2843 3954.6106 35 Use the average seasonal Index in the column for 2019
    12/1/21 6087 3781.3333 1.6097 1.5298 3978.8458 36 Deseasonalized forecast Seasonalized forecast
    1/1/22 37 3916.71 3061.67
    2/1/22 38 3946.46 3165.07
    3/1/22 39 3976.20 3496.29
    4/1/22 40 4005.94 3944.35
    5/1/22 41 4035.69 4005.72
    6/1/22 42 4065.43 4528.86
    7/1/22 43 4095.18 3401.53
    8/1/22 44 4124.92 3577.20
    9/1/22 45 4154.67 3657.38
    10/1/22 46 4184.41 4408.29
    11/1/22 47 4214.16 5412.34
    12/1/22 48 4243.90 6492.49
    Highest yearly average Highest Average SI Total 3 year deseasonalized demand Put yearly average in cell I50
    3781.3333 1.5298 121191.6297 When you move your forecast to the profit models, you must use an equation, not just copy the values
    Total 3 year demand Average 3 year deseasonalized demand
    121264 3366.4342
    Average 3 year demand
    3368.4444
    average si for 2023 12.0000
    Ahadu Solomon
    Spring 2021

    Current operations Pt2 & Pt3

    CURRENT OPERATIONS Reminder: Format Cells to show 2 decimal places Monthly Production Lost Sales Day old revenue test
    Part 2 4000 3 1.25 Summer 2020
    AVERAGE DEMAND
    Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
    Demand 3061.67 3165.07 3496.29 3944.35 4005.72 4528.86 3401.53 3577.20 3657.38 4408.29 5412.34 6492.49 49151.20
    satisfied demand 3061.67 3165.07 3496.29 3944.35 4000.00 4000.00 3401.53 3577.20 3657.38 4000.00 4000.00 4000.00 44303.49
    Extra donuts(over) 938.33 834.93 503.71 55.65 0.00 0.00 598.47 422.80 342.62 0.00 0.00 0.00 3696.51
    Unsatisfied customers (short) 0.00 0.00 0.00 0.00 5.72 528.86 0.00 0.00 0.00 408.29 1412.34 2492.49
    Revenue
    coffee
    donut
    Revenue from day old sales
    Total Revenue
    Expenses
    Fixed Expenses
    Total Fixed Expense
    Variable Expenses
    Coffee Variable expense
    Donut Variable expense
    Total Variable Expenses
    Expenses: due to lost sales
    Total Expenses
    Profit Donuts and Coffee
    Part 3 You should be able to copy from average to high and low and then just make some modifications
    HIGH DEMAND
    Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
    Demand
    satisfied demand
    Extra donuts(over)
    Unsatisfied customers (short)
    Revenue
    coffee
    donut
    Revenue from day old sales
    Total Revenue
    Expenses
    Fixed Expenses
    Total Fixed Expense
    Variable Expenses
    Coffee Variable expense
    Donut Variable expense
    Total Variable Expenses
    Expenses: due to lost sales
    Total Expenses
    Profit Donuts and Coffee
    Part 3
    LOW DEMAND
    Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
    Demand
    satisfied demand
    Extra donuts(over)
    Unsatisfied customers (short)
    Revenue
    coffee
    donut
    Revenue from day old sales
    Total Revenue
    Expenses
    Fixed Expenses
    Total Fixed Expense
    Variable Expenses
    Coffee Variable expense
    Donut Variable expense
    Total Variable Expenses
    Expenses: due to lost sales
    Total Expenses
    Profit Donuts and Coffee
    Ahadu Solomon
    Spring 2021

    Franchise operations Pt2 & Pt3

    FRANCHISE Reminder: Format Cells to show 2 decimal places Monthly Production Lost Sales Day old revenue
    Part 2
    AVERAGE DEMAND
    Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
    Demand
    satisfied demand
    Extra donuts(over)
    Unsatisfied customers (short)
    Revenue
    coffee
    donut
    Revenue from day old sales
    Total Revenue
    Expenses
    Fixed Expenses
    Total Fixed Expense
    Variable Expenses
    Coffee Variable expense
    Donut Variable expense
    Total Variable Expenses
    Expenses: due to lost sales
    Total Expenses
    Profit Donuts and Coffee
    Part 3 You should be able to copy from average to high and low and then just make some modifications
    HIGH DEMAND
    Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
    Demand
    satisfied demand
    Extra donuts(over)
    Unsatisfied customers (short)
    Revenue
    coffee
    donut
    Revenue from day old sales
    Total Revenue
    Expenses
    Fixed Expenses
    Total Fixed Expense
    Variable Expenses
    Coffee Variable expense
    Donut Variable expense
    Total Variable Expenses
    Expenses: due to lost sales
    Total Expenses
    Profit Donuts and Coffee
    Part 3
    LOW DEMAND
    Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
    Demand
    satisfied demand
    Extra donuts(over)
    Unsatisfied customers (short)
    Revenue
    coffee
    donut
    Revenue from day old sales
    Total Revenue
    Expenses
    Fixed Expenses
    Total Fixed Expense
    Variable Expenses
    Coffee Variable expense
    Donut Variable expense
    Total Variable Expenses
    Expenses: due to lost sales
    Total Expenses
    Profit Donuts and Coffee

    Expected Values Pt3

    Remember that we use profits to fill in payoff tables like the ones we forecasted in the previous two sheets. It would help if you filled in the table in C5:E6 and referenced those values to the other tables below. Use the probabilities found in Finanicl Information B42:44 for the regret tables PAYOFF TABLE Reminder: Format Cells to show 2 decimal places
    State of Nature Summary of Results Methods Fill in the space below for each method solved under each Decision Alternative
    Decision Alternatives Low Average High Decision Alternatives Maximin MaxiMax Laplace MinMax regret EVUII EOL
    CURRENT OPERATIONS CURRENT OPERATIONS
    FRANCHISE FRANCHISE
    Decision Alternatives
    DM UNDER IGNORANCE
    Kiana should choose to:
    Maximin FRANCHISE
    State of Nature
    Decision Alternatives Low Average High Because:
    CURRENT OPERATIONS Franchise Operations is proving to be the best decision alternative with most methods.For example, if we evaluate the EOL of the two, Franchise Operations has the lowest EOL. This is the best decision as we always want the lowest "loss" or EOL!
    FRANCHISE
    Maximax
    State of Nature
    Decision Alternatives Low Average High
    CURRENT OPERATIONS
    FRANCHISE
    Laplace
    State of Nature
    Decision Alternatives Low Average High
    CURRENT OPERATIONS
    FRANCHISE
    Minimax Regret
    Regret table State of Nature
    Decision Alternatives Low Average High
    CURRENT OPERATIONS
    FRANCHISE
    DM UNDER RISK
    EVUII
    State of Nature
    Decision Alternatives Low Average High
    Probability
    CURRENT OPERATIONS
    FRANCHISE
    EVUPI
    State of Nature
    Decision Alternatives Low Average High
    Probability
    Payoff
    EVPI
    EOL
    Regret table State of Nature
    Decision Alternatives Low Average High
    Probability
    CURRENT OPERATIONS
    FRANCHISE

                                                                                                                                      Order Now