IndivProjectP1_jaylaredding.xlsm

IndivProjectP1_jaylaredding.xlsm

5.ID

Do Not Alter or Delete this Worksheet or you submission cannot be graded!DirVer
ActionNamePanther IDDate/TimeS01
Start S01Ahadu Solomon20202020220-Sep-2021 05:31Microsoft Office User
Starter SheetNone0Jan-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 InformationSpring 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 periodFixed Costs
Revenue: Cup of Coffee$2.99Varible Costs
Revenue: Donut$2.50Coffee
Donut ingredients per donunt)per donut$0.60Donut
paper products: napkins, plates etc
Insurancemonth$300.00
Maintenance & Repairs to equipmentmonth$0.00
Marketing & Promotion: Advertisingmonth$100.00
Coffeeper cup$0.35
Coffee cupsper cup$0.15
Payroll: Wages (Owner/ Manager)month$2,400.00
Payroll: Wages (per Employees)month$1,200.00
Donut and Coffee equipment rentmonth$500.00
Professional Fees: Accountingmonth$50.00
Professional Fees: Legalmonth$25.00
Powdered and Liquid Beverages$0.00
Rentmonth$1,000.00
Previous research expense for Donuts advancements$1,500.00
Supplies: Officemonth$25.00
Utilitiesmonth$200.00
Additional Data
Operations
Monthly Production
Lost Sales
Day old revenue
High demand, % above Average
Low Demand, % below average
Franchise Operations
Monthly Fixed Expense increase
Monthly Production Increase
Monthly demand increase
States of Natures
High demand
Average demand
Low demand
Total

SI and regression Pt 1

MonthDemandYearly averageSeasonal IndexAverage SIDeseasonalizedTime periodRegression Output
1/1/1922720.81320.77492931.871CLICK CELL J2 as output cell for regressionSUMMARY OUTPUT
2/1/1924160.86470.79363044.432
3/1/1928931.03540.86243354.763Regression Statistics
4/1/1927981.00140.97232877.794Multiple R0.806790669
5/1/1924010.85930.98242444.085R Square0.6509111836
6/1/1934941.25051.12113116.576Adjusted R Square0.6406438654
7/1/1925810.92380.82173141.017Standard Error276.8537358173
8/1/1922410.80210.85652616.318Observations36
9/1/1922790.81570.87392607.849
10/1/1926330.94241.06432473.9810ANOVA
11/1/1934821.24621.30792662.2211dfSSMSFSignificance F
12/1/1940382794.001.44521.56902573.6112Regression14859208.016008384859208.0160083863.39641718370.0000000028
1/1/2026030.80930.77493359.0113Residual342606031.6952239976647.9910359998
2/1/2024550.76330.79363093.5714Total357465239.71123238
3/1/2025330.78760.86242937.3015
4/1/2031170.96910.97233205.8916CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
5/1/2035671.10910.98243631.0017Intercept2608.831387803894.241359720427.6824463866.63101391134469E-252417.30990196142800.35287364622417.30990196142800.3528736462
6/1/2034471.07171.12113074.6518Time period35.36610515854.44175783027.96218670870.000000002826.339367196444.392843120626.339367196444.3928431206
7/1/2024490.76140.82172980.3719
8/1/2028250.87840.85653298.1220
9/1/2028570.88830.87393269.2421
10/1/2034271.06551.06433220.0322
11/1/2041741.29781.30793191.3023
12/1/2051413216.251.59841.56903276.6024
1/1/2126600.70230.77493432.5625
2/1/2128510.75270.79363592.5726
3/1/2128940.76410.86243355.9227
4/1/2135840.94620.97233686.2028
5/1/2137070.97870.98243773.5129
6/1/2139431.04101.12113517.0730
7/1/2129540.77990.82173594.9531
8/1/2133680.88920.85653932.0532
9/1/2134760.91770.87393977.5533
10/1/2144881.18491.06434216.9534
11/1/2152261.37981.30793995.6235Use the average seasonal Index in the column for 2019
12/1/2163003787.581.66331.56904015.2836Deseasonalized forecastSeasonalized forecast
1/1/22373917.383035.70
2/1/22383952.743136.82
3/1/22393988.113439.18
4/1/22404023.483911.92
5/1/22414058.843987.30
6/1/22424094.214590.04
7/1/22434129.573393.31
8/1/22444164.943567.48
9/1/22454200.313670.67
10/1/22464235.674507.92
11/1/22474271.045586.23
12/1/22484306.406756.77
Highest yearly averageHighest Average SITotal 3 year deseasonalized demandPut yearly average in cell I504131.94
3787.581.5690117471.76When you move your forecast to the profit models, you must use an equation, not just copy the values
Total 3 year demandAverage 3 year deseasonalized demand
1175743263.10
Average 3 year demand
3265.94
Ahadu Solomon
Spring 2021

Current operations Pt2 & Pt3

CURRENT OPERATIONSReminder: Format Cells to show 2 decimal placesMonthly ProductionLost SalesDay old revenuetest
Part 2Summer 2020
AVERAGE DEMAND
MonthJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Yearly 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 3You should be able to copy from average to high and low and then just make some modifications
HIGH DEMAND
MonthJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Yearly 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
MonthJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Yearly 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

FRANCHISEReminder: Format Cells to show 2 decimal placesMonthly ProductionLost SalesDay old revenue
Part 2
AVERAGE DEMAND
MonthJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Yearly 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 3You should be able to copy from average to high and low and then just make some modifications
HIGH DEMAND
MonthJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Yearly 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
MonthJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Yearly 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 tablesPAYOFF TABLEReminder: Format Cells to show 2 decimal places
State of NatureSummary of ResultsMethodsFill in the space below for each method solved under each Decision Alternative
Decision AlternativesLowAverageHighDecision AlternativesMaximinMaxiMaxLaplaceMinMax regretEVUIIEOL
CURRENT OPERATIONSCURRENT OPERATIONS
FRANCHISEFRANCHISE
Decision Alternatives
DM UNDER IGNORANCE
Kiana should choose to:
MaximinFRANCHISE
State of Nature
Decision AlternativesLowAverageHighBecause:
CURRENT OPERATIONSFranchise 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 AlternativesLowAverageHigh
CURRENT OPERATIONS
FRANCHISE
Laplace
State of Nature
Decision AlternativesLowAverageHigh
CURRENT OPERATIONS
FRANCHISE
Minimax Regret
Regret tableState of Nature
Decision AlternativesLowAverageHigh
CURRENT OPERATIONS
FRANCHISE
DM UNDER RISK
EVUII
State of Nature
Decision AlternativesLowAverageHigh
Probability
CURRENT OPERATIONS
FRANCHISE
EVUPI
State of Nature
Decision AlternativesLowAverageHigh
Probability
Payoff
EVPI
EOL
Regret tableState of Nature
Decision AlternativesLowAverageHigh
Probability
CURRENT OPERATIONS
FRANCHISE

Related posts

June 22, 2022

HW

Read more