Exam 500895 -ppmc - Senior Capstone - Excel Spreadsheet

Transcript

Calculaton of Firs Year Break Even Poins Noe M1 M2 Jan M3 Feb M4 Mar Ar Stores 1 1,200 1,200 1,200 1,200 Mulply by 200 carts Total Carts Mulply by Revenue per cart !oal "evenues 3 3 1 1 240,000 240,000 67! 1#$2%#%%% 240,000 480,000 67! 3#24%#%%% 240,000 720,000 6 7 ! 4#&$%#%%% 240,000 960,000 6 7 ! $#4&%#%%% "#or$aon %2 year S&'( 2 746,400 746,400 746,400 746,400 )r*nn+ Replace#ent %even *str*buon( 3 4 26,400 7!,960 !2,800 7!,960 79,200 7!,960 132,000 7!,960 Cart Rental %10- Revenue( M.t+ Sales / Co## ! 6 162,000 861,000 324,000 861,000 486,000 861,000 648,000 1,!90,000 rocery Store peraons !oal 'C 7 840,000 2#*11#* %$840,000 2#+%%#1 %$840,000 3#%&&#, %$960,000 4#1,2#3 %$%1,091,760( %4!!( 339,840 071 1,771,440 246 2,327,640 242 'ariable Coss ('C) Contr*buon Mar+*n %CM( CM per n*t&Cart Fi-e. Coss (FC) "ccounn+ / "u*t 8 6,000 6,000 6,000 6,000 "vers*n+ %even *str*buon( "uto 'ease 9 10 39,37! 2,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 an. Car+es 5nterta*n#ent / )ro#oon nsurance 11 12 13 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 'e+al 14 10,000 10,000 10,000 10,000 Mana+e#ent ees 1! 24,167 24,167 24,167 24,167 ce / Sunry )ubl*c Relaons 16 17 !,000 33,333 !,000 33,333 !,000 33,333 3,000 33,333 Rent Salar*es / enets Staonary / )r*nn+ 18 19 20 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 Telepone / a: Travel / "cco##oaon !oal FC 21 22 3,!00 10,000 1&$#*%& 3,!00 10,000 1&$#*%& 3,!00 10,000 1&$#*%& 3,!00 20,000 1+4#*%& 2#&+$& 3#%&$#&$& 3#2*,#2$& 4#34*#%$& (1#2*$&) 1,3#132 1#,&4#*32 2#132#+32 !oal E-enses ('C / FC) Ne 0eratn ncoe Break Even Poin in ers of cars Break Even Poin in ers of sores   M, M$ Ma M* Jun M& Jul M+ Au 5e M1% M11 M12 1s 0c Nov 6ec Year 1,200 1,200 1,200 1,200 1,200 1,200 1,200 1,200 14,400 240,000 1,200,000 6 7 !
%% #1%%#% #%% %% 240,000 1,440,000 67! +#*2 +#*2% %#%%% #%%% 240,000 1,680,000 6 7 ! 11#3 11#34 4%#%% %#%%% % 240,000 1,920,000 6 7 ! 12#+ 12#+$% $%#% #%%% %% 240,000 2,160,000 67! 14#, 14#,&% &%#% #%%% %% 240,000 2,400,000 67! 1$#2 1$#2%% %%#% #%%% %% 240,000 2,640,000 67! 1*#& 1*#&2% 2%#% #%%% %% 240,000 2,880,000 6 7 ! 1+#4 1+#44 4%#%% %#%%% % 2,880,000 2,880,000 6 7 ! 12$# 12$#3 3$%#% $%#%%% %% 746,400 746,400 746,400 746,400 746,400 746,400 746,400 746,400 8,9!6,800 1!8,400 7!,960 184,800 7!,960 211,200 7!,960 237,600 7!,960 264,000 7!,960 290,400 7!,960 316,800 7!,960 343,200 7!,960 2,296,800 911,!20 810,000 1,!90,000 972,000 1,!90,000 1,134,000 2,319,000 1,296,000 2,319,000 1,4!8,000 2,319,000 1,620,000 3,048,000 1,782,000 3,048,000 1,944,000 3,048,000 12,636,000 23,4!4,000 960,000 4#34%#* %$960,000 4#,2+#1 %$1,080,000 ,#,$$#, %$1,080,000 ,#*,4#+ %$1,080,000 ,#+43#3 %$1,200,000 $#+&%#* %$1,200,000 *#1$+#1 %$1,200,000 *#3,*#, %$12,240,000 $%#4+,#12% 3,7!9,240 313 !,190,840 360 360 !,773,440 344 7,20!,040 37! 8,636,640 400 9,219,240 384 10,6!0,840 403 12,082,440 420 6!,864,880 2287 6,000 6,000 6,000 6,000 6,000 8,000 8,000 8,000 78,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 39,37! 2,000 472,!00 24,000 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 167 10,000 8,333 2,000 120,000 100,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000 24,167 24,167 24,167 24,167 24,167 24,167 24,167 24,167 290,000 3,000 33,333 3,000 33,333 4,000 33,333 4,000 33,333 4,000 33,333 !,000 33,333 !,000 33,333 !,000 33,333 !1,000 400,000 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 10,000 24,000 833 120,000 288,000 10,000 3,!00 20,000 1+4#*%& 3,!00 20,000 1+4#*%& 3,!00 30,000 2%,#*%& 3,!00 30,000 2%,#*%& 3,!00 30,000 2%,#*%& 3,!00 4!,000 223#*%& 3,!00 4!,000 223#*%& 3,!00 4!,000 223#*%& 42,000 31!,000 2#432#,%% 4#,3,#4$& 4#*23#&$& ,#**2#2$& ,#+$%#$$& $#14+#%$& *#2%4#4$& *#3+2#&$& *#,&1#2$& $2#+2*#$2% 3#,$4#,32 4#++$#132 $#+++#332 +%#+32 1%#42*#132 11#&,&#*32 $3#432#3&% ,#,$*#*32 &#++,#,32 1%$#3$3 ,32 Calculaton of Firs Year Break Even Poins 1s 2n. 8r 8r 3r. 47 1s 8r 8r Year 3,600 7,200 10,800 14,400 14,400 720,000 720,000 9,720,000 +#*2 +#*2%# %#%% %%% % 1,440,000 1,440,000 24,300,000 24#3 24#3%% %%#% #%%% %% 2,160,000 2,160,000 38,880,000 3&#& 3&#&&% &%#% #%%% %% 2,880,000 2,880,000 !3,460,000 ,3#4 ,3#4$ $%#%% %#%%% % 2,880,000 2,880,000 126,360,000 12$# 12$#3$ 3$%# %#% %%% 2,239,200 2,239,200 2,239,200 2,239,200 8,9!6,800 1!8,400 227,880 47!,200 227,880 712,800 227,880 9!0,400 227,880 2,296,800 911,!20 972,000 2,!83,000 2,430,000 4,770,000 3,888,000 6,9!7,000 !,346,000 9,144,000 12,636,000 23,4!4,000 2,!20,000 &#*%%#4&% 2,880,000 13#%22#2&% 3,240,000 1*#2$4#&&% 3,600,000 21#,%*#4&% 12,240,000 $%#4+,#12% 1,019,!20 142 11,277,720 783 21,61!,120 1001 31,9!2,!20 1109 6!,864,880 2287 18,000 18,000 18,000 24,000 78,000 118,12! 6,000 118,12! 6,000 118,12! 6,000 118,12! 6,000 472,!00 24,000 !00 30,000 2!,000 !00 30,000 2!,000 !00 30,000 2!,000 !00 30,000 2!,000 2,000 120,000 100,000 30,000 30,000 30,000 30,000 120,000 72,!00 72,!00 72,!00 72,!00 290,000 1!,000 100,000 9,000 100,000 12,000 100,000 1!,000 100,000 !1,000 400,000 30,000 72,000 2,!00 30,000 72,000 2,!00 30,000 72,000 2,!00 30,000 72,000 2,!00 120,000 288,000 10,000 10,!00 30,000 ,$%#12, 10,!00 60,000 ,&4#12, 10,!00 90,000 $1*#12, 10,!00 13!,000 $*1#12, 42,000 31!,000 2#432#,%% +#2$%#$%, 13#$%$#4%, 1*#&&2#%%, 22#1*&#$%, $2#+2*#$2% 4,+#3+, 1%#$+3#,+, 2%#++*#++, 31#2&1#3+, $3#432#3&% 1%$#3$3 ,32 5ock 9 of Fir !icker 5ores Nae S;< 2,200 Sa=e>ay S? )r*vate "@RA< 2,012 Supervalu 1,300 "l* 1,214 "olB@ela*$e )r*vate R 1,148 )ubl*: 782 ro+er )r*vate ;M 690 *B'o 466 ;ole oos Mar.et )r*vate )r*vate ")TG )r*vate 46! 340 300 23! )r*vate MT" S)TA 210 Save Mar 200 n+les 1!0 Aas *nc RA@< )r*vate 148 RounyEs 100 ;*nco oos )r*vate 90 ;e+#anEs Total !oal 9 of 5ores 12,0!0 241%% Traer DoeEs F5 reat "tlanc / )ac*c Tea Co FyB?ee