Month calculation from cost for a business plan - please help

[Juergen L]

hello all

i have to prepare a cost calculation but i stock
following variable are there:

- normal employees / cost per month
- special employees / cost per month and commission
- Material per 13000 peaces is 1000 dollar
- electricity and gas 2000 doller per 13000 peaces
- rent 3000 dollar / month

how can i prepare a calculation based on the 13000 peaces. the products will
sold always more and more and more. i need to see how many peaces i have to
sell to get the expenses in and when starts the business.

i really stock in that case how i can material cost calculating
how many times i have to sell 13000 peaces to get the cost down

the first week the sales person should/must sell following table
after 3 days - 1000 peaces / per day
after 6 days - 2000 peases / per day
after 15 days - 3000 peases / per day
end of the month i want to sell 5000 peaces per day
(i am the boss and that is my privilege to sett limits for the sales peoples
;-) )

i need for that use a grafik

additional infos: this is a product which the people buy always. As example:
Shop A buy every day 200 peaces, Shop B every day 500 Peaces and so on

thx to all
Juergen

 

[JLatham]

Before you can determine the break even point (point at which income meets
expenses), you are going to have to determine the selling price of each piece.

The cost of the piece must account for all expenses incurred to acquire it.
Some, but not all of these costs will be:
employee salaries
cost of employee benefits (insurance, 401K, training, paid off-time, etc)
cost of utilities (gas, electricity, phone, water, sewer, internet service)
cost of rent, insurance, delivery vehicles (and their upkeep, licensing,
insurance, etc)
cost of storage of the pieces if that's not included as part of the rent.

Personally, my recommendation at this point is to find a reasonably priced
CPA and take your business plan to him, be prepared to provide information
about those costs I've mentioned and probably some I haven't. Pay him/her to
assist you in coming up with the numbers you need - ask him to also provide
the formulas to calculate those values: with those formulas you can set up an
Excel workbook to repeat the process and update it with time.

 

[KC Rippstein hotmail com>]

I'm not sure how much help this will be, but if what you're asking for this
the algebra formula that will help you determine your "break-even" point (the
point at which expenses are met and you begin making a profit), then this is
your formula:
x/13 + 2x/13 + payroll + commission + 3000 rent = 0
or solving for x
x = 13 * (payroll + commission + 3000 rent)

So if your payroll was, say, $10,000 and your commissions were $2,000, then
you'd need to sell 13 * 15,000 = 195,000 pieces per month to break even
(divide 195,000 by 13,000 and you'll see you need a total of fifteen bulk
orders per month).

Multiply 195,000 by 12 and divide by 365.2425 days/yr to get a daily average
of 6,406 pieces per day.

Hopefully that helps you develop your spreadsheet design. Good luck!!

 

[Billy Liddel]

Juergen
Lets assume that you just have one shop then you can develop this. I
estimated Material and Labour costs. With Target Sales in B2 this goes down
to B12

Costs are:
Target_Sales 13000
Variable_costs
Gas&Elect 2000
Rent/month 3000
Materials 1000
6000
Employees
Normal 7000
Special 5000
12000
Month_1_Cost 18000

Cost per Item Labels in column F Costs in Column G
Materials 0.461538462
Labour 0.923076923
Total_Item_Cost 1.384615385
Sales Price 4.15

Material Cost in G3: =C7/B4
Labour Cost In G4: =C11/c4
Tot Item Cost in G5 =SUM(G3:G5)

Sales Price = G5*3

Your Target Sales And associated costs would be:

Days 1-3 Days 4 - 6 Days 7 - 15 "Days
16 - 21" "Days
22 - 28"
Items Sold 1000 2000 3000 4000 5000 15000
Material Cost 461.54 923.08 1384.62 1846.15 2307.69 6923.08
Labour Cost 923.08 1846.15 2769.23 3692.31 4615.38 13846.15
Total Costs 1384.62 2769.23 4153.85 5538.46 6923.07 20769.23

Sales 4150 8300 12450 16600 20750 62250
Operating Profit 2765.38 5530.77 8296.15 11061.54 13826.93 41480.77

The formulas for day 1-3 are

=ROUND($G$3*B16,2)
=ROUND($G$4*B16,2)
=SUM(B17:B18)

=$G$6*B16
=B21-B19

The final columns is just a sum of the rows. This should get you started.

Regards
Peter Atherton