Please Fill in the excel template below 

Budgex, a French company, wants to prepare its budget for the three coming months (M1, M2 and M3).

Budgex sells a finished good (FG) which is made of a single raw materials (RM) transformed in Budgex's workshop.

Forecasted sales for the four coming months are respectively 10,000; 12,000; 9,000; and 8,000 units of FG. The selling price should remain stable at 70 € per unit over the period.

To make the sales, Budgex supports selling expenses. In addition to 30,000 € in fixed salaries, sales representatives are paid a 2% commission on sales (exclusive of VAT). Selling costs also include external fixed expenses and depreciation expenses that amount respectively to 12,000 and 5,000 €.

 

Each unit of FG needs 5 units of RM. Purchasing cost of RM is 3 € per unit. Manufacturing costs include 200,000 € in labor costs, 120,000 € in external expenses, and 20,000 € in depreciation expenses. All these costs are fixed.

 

Budgex plans to invest in some administrative equipment in month 2 (M2). The total cost will amount to 120,000 € and be paid cash. This fixed asset will be depreciated over 5 years.

 

The production schedule is organized such that the number of units of FG in stock at the end of a month equals 50% of the volume of sales in the following month. As to RM, Budgex wants to maintain a safety stock of 20,000 units as of M1. The beginning inventories include 5,000 units of FG and 22,000 units of RM. Inventories are valued using the WAUC method.

 

Monthly administrative expenses are fixed and include 80,000 € in salaries, 30,000 € in external expenses, and 3,000 € in depreciation.

 

All expenses are paid cash, except RM purchases which are paid the following month. As for sales, 50% are paid cash and 50% are collected the following month.

 

In month M3, Budgex will have to pay an interest expense of 4,000 € in addition to the repayment of 10,000 € of its current loan principal.

 

Other payables in the balance sheet correspond to the VAT that has to be paid in M1. VAT rate is 20%. All sale and cost prices are given exclusive of VAT.

 

Budgex does not pay any dividend.

 

One will assume no income tax.

 

The balance sheet at the end of M0 is given below.

 

Balance sheet at the end of M0 (€)

 

 

Fixed assets	700 000
- accumulated depreciation	(260 000)
Net fixed assets	440 000
Finished goods inventory	255 000
Raw materials inventory	57 000
Receivables	390 000
Cash	32 260
Total Assets	1 174 260
Equity	400 000
Retained earnings	302 760
Loan	200 000
Payables (RM suppliers)	192 000
Other payables	79 500
Total Liabilities and Equity	1 174 260

Budgeted income statement Sales Change in FG inventory RM purchases Change in RM inventory External expenses Salaries Depreciation expenses Interest expenses Income before tax Budgeted income statement Sales COGS S.A.&G. expenses Interest expenses Income before tax M1 M1 M2 M2 M3 M3 Quarter Quarter