Ask Me Help Desk - Developing sales budgets

I have been provided 6 months worth of forecast data in order to develop a number of budgets. Data includes monthly sales revenue, finance payments, purchases, admin expenses, selling expenses, tax expenses and shopfront upgrading. All figures are in dollar values. No 'unit' figures have been provided. My questions as follows:

1. To make a sales budget, my research suggests that this simply includes the sales revenue figures that have been given and nothing else. These figures have already been provided as part of the question. Is this part really that simple or am I mssing something?

2. Is the development of a sales budget, ending finished goods inventory budget, selling and admin expenses budget, and a cash budget mandatory for the development of a budgeted income income statement?

3. Additional notes provides the gross profit margin of 20% for the business (furniture store) but I can't see where this figure is used in the development of any of the budgets... where is this included?

I am grateful for any help.

Parrot

Quote:

Originally Posted by ParrotBird48516

I have been provided 6 mths worth of forecast data in order to develop a number of budgets. Data includes monthly sales revenue, finance payments, purchases, admin expenses, selling expenses, tax expenses and shopfront upgrading. All figures are in dollar values. No 'unit' figures have been provided. My questions as follows:

1. To make a sales budget, my research suggests that this simply includes the sales revenue figures that have been given and nothing else. These figures have already been provided as part of the question. Is this part really that simple or am I mssing something?

2. Is the development of a sales budget, ending finished goods inventory budget, selling and admin expenses budget, and a cash budget mandatory for the development of a budgeted income income statement?

3. Additional notes provides the gross profit margin of 20% for the business (furniture store) but I can't see where this figure is used in the development of any of the budgets...where is this included?

I am grateful for any help.

Parrot

1 in the absence of any other data, yes

2. The budgetary process should tie everything together, nothing exists in isolation

3 gross margin is going to feed into your income statement and should give you the ability to calculate purchases or at least one part of the inventory movement equation

lastly don't overthink the problem

Thanks Paraclete. Now... inventory budgets if that is okay. Data provided is the same as above in terms of dollar values and various accounts provided. My inventory budget will look as follows:

1. Opening balance inventory (provided)
2. Plus purchased inventories (provided)
3. Less cost of inventories sold (or cost of goods sold - COGS - to be determined)
4. Closing Balance

I understand that my closing balance for one month then becomes the opening balance for the next month.

My question relates to the calculation of COGS sold given the info I have been provided (I know my starting inventory but that doesn't effect this calculation). So, for example, in June I know:

-Sales revenue = $400 000
-Purchases = $150 000
-Profit margin given is 25% (I am assuming that this is the same for each of the six months provided)

Useful Formula and current calculation:

Gross Profit margin = gross profit / total revenue
.25 = gross profit / 400,000
.25 x 400,000 = gross profit
100,000 = gross profit

Gross profit = net sales - COGS
My research suggests that net sales = sales revenue - discounts, but can be used interchangeably as accounts don't always have discounts, returns or allowances, thus I am using the provided sales revenue in the place of net sales in this formula - am hoping this is right as I can't see that any of the other accounts listed previously fit into this formula or definitions. Thus:

100,000 = 400,000 - COGS
-300,000 = -COGS
300,000 = COGS

I would then plug this amount into the inventory budget table as previously discussed. Do these formula and calcs seem logical and correct? Looking at the accounts provided, I don't think any of the ones I haven't used are useful for inventory budgets. They will come into play for the cash budget and income statement budget.

Thank you for your help as always.

Parrot

Quote:

Originally Posted by ParrotBird48516

Thanks Paraclete. Now... inventory budgets if that is okay. Data provided is the same as above in terms of dollar values and various accounts provided. My inventory budget will look as follows:

1. Opening balance inventory (provided)
2. Plus purchased inventories (provided)
3. Less cost of inventories sold (or cost of goods sold - COGS - to be determined)
4. Closing Balance

I understand that my closing balance for one month then becomes the opening balance for the next month.

My question relates to the calculation of COGS sold given the info I have been provided (I know my starting inventory but that doesn't effect this calculation). So, for example, in June I know:

-Sales revenue = $400 000
-Purchases = $150 000
-Profit margin given is 25% (I am assuming that this is the same for each of the six months provided)

When you first posted this question you said the gross margin was 20%

So given the data you have provided

1. Opening balance inventory (provided)
2. Plus purchased inventories (150,000)
3. Less cost of inventories sold (or cost of goods sold - COGS - 320,000)
4. Closing Balance

Quote:

Useful Formula and current calculation:

Gross Profit margin = gross profit / total revenue
.25 = gross profit / 400,000
.25 x 400,000 = gross profit
100,000 = gross profit

Gross profit = net sales - COGS
My research suggests that net sales = sales revenue - discounts, but can be used interchangeably as accounts don't always have discounts, returns or allowances, thus I am using the provided sales revenue in the place of net sales in this formula - am hoping this is right as I can't see that any of the other accounts listed previously fit into this formula or definitions. Thus:

100,000 = 400,000 - COGS
-300,000 = -COGS
300,000 = COGS

I would then plug this amount into the inventory budget table as previously discussed. Do these formula and calcs seem logical and correct? Looking at the accounts provided, I don't think any of the ones I haven't used are useful for inventory budgets. They will come into play for the cash budget and income statement budget.

Thank you for your help as always.

Parrot

You have to use Gross Sales to calculate the inventory movement. You are correct in your assumption regarding net sales but in the absence of numbers unless your variation in margin is indicative of discounts keep it simple. Remember that a gross margin percentage means that cost of goods sold is the other side of the equation so a 20% margin means an 80% COGS

My mistake with the profit margin: it is 20%.

Noting this, going over my calcualtions again, I found the same answer as you indicate for COGS: $320,000.

At first, I was a bit lost with the 20% margin = 80% COGS however mathematically, to check the answer, the calcualted COGS $320,000 is 80% of the revenue made. Enlightening!

Also, noting your point about gross sales and research indicating gross sales are before any discounts, I will still be using the sales revenue figures provided as the gross figure, given that no discounts have been provided. Does this make sense?

Thanks again for your guidance. Yes I know... keep it simple...

Parrot

Paraclete,

Part of the information that I have been given is that this furniture store has decided that it thinks it is advantageous that it keeps a minimum inventories level of $25,000 over the discussed six month period.

Do you think that this changes my calculations in the preparation of my inventory budget? My initial thought is that I don't think so as I have been provided a number of different account figures (listed previously) that I have used to calculate gross profit, COGS and then closing and opening inventory balances as discussed above. The question simply required an inventories budget and I am assuming I use the info provided (revenue, purchases etc).

It would be odd to have the same closing balance ($25,000) across the six month period ('cos I have been given other figures/accounts), however, I am not sure what to do with this piece of information.

The last part of the question requires an analysis so I was thinking that I could compare the actual inventory budget that I prepared based on the info given as we previously discussed, with the desired inventory level of $25,000 and discuss it from there (issues if the calcualted inventory levels were higher or lower than the desired $25,000 and how to fix it).

If I know what the desired end inventory is, I can work out what the purchases could/should be to attain that level of $25,000 (if COGS remains constant).

What do you think this $25,000 piece of info is for? Yes, I am using too much brain power I know, but it has been given and I am not sure how to use it.

Many thanks for your help.

Parrot