Budgeting and control project assignment

Hey, I’d like someone to help me for my budgeting and control project assignment due friday 3rd of April

Growth

150%

150%

150%

150%

48,6%

%

50 000

0

-51 857

-22%

0

-51 857

820 985

820 985

0

0

0

1 074 021

0

0

0

0

0

0

0

0

				15 0 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper %
2017	2018	2020	2021	2022	2023	2024	2025
sales	232 500	697 500	2 092 500
COGS	104 625	310 989	943 987
Travelling expenses for projects.	14 987	40 790	130 976
Margin 1	112 888	345 721	1 017 537
	%		48,6%	49,6%
Indirect labor		50 000	80 000	144 000
Depreciation														0	82 099	89 074
Utilities	3 500	4 234	12 702
Total product cost	173 112	518 112	1 320 739
Margin 2	59 388	179 389	771 762
25,5%	25, 7%	36,9%
Sales & marketing	100 767	176 534
Consulting	20 925	62 775
Administration	61 245	87 990	126 789
Operating income			-51 857	-30 294	405 664
OI %		-22%	-4%	19%
EBITDA	51 805	494 737
7%	24%
Interest expense	27 209	35 820
EBT	-57 502	369 843
Taxes	-12 964	-14 376	92 461
EAT	-38 893	-43 127	277 383
CAPEX			820 985	69 750	209 250
Assets		1 074 021	1 697 536
Fixed assets	808 637	928 813
Accounts receivable	171 986	515 959
Inventories	76 682	232 764
Cash	16 716	20 000
Liablilities	902 274	1 697 535
Accounts payable	25 561	77 588
Loans	602 274	758 155	1 032 853
Equity	300 000	290 306	587 094
Control line	-81 289

BCO322 MASTER BUDGET Task brief & rubrics

Task: Spreadsheet

You need to develop your company’s master budget for 5 years (2020-2024), taking into account all relevant aspects learned during this semester about management accounting:

·

Cost-center, Type & Bearer and Allocation Policies · MOH & SGA

· Material flow · Inventory Turnover

· Direct & Indirect Costs

· Job Order Costing vs Process Costing

· Variable vs Fixed Costs & Cost Volume Profit Analysis

· Cash Flow Management, Cash Budgeting

· Long term profitability, sustainability · Business model viability

To begin with your project should display at least 2 years of past activities (Financial Statements containing at least a detailed P&L and a Balance Sheet).

You just closed books March, 31st of 2020.

In light of recent results, the Board is asking form you the forecast for the next five business years.

So, for the master budget exercise you will have 2 scenarios:

1. Sales growth of 50% more than the 2020 over 2019. In example your sales increased 2019 8% over 2018, then your sales target is at least 8% + 50% (over 8%) = 12% growth from 2019.

Or

2. Operating Profit Margin of 3 percentage points improvement over last year. In example your profit margin was 8%. the first year of budget needs to be 10% over sales.

Task: Prepare an excel sheet with the forecasts of each and every one of the lines of the income statement.

The assumptions are:

If we choose scenario 1

· Sales grow 50% over last year and this growth continues for the 5 years.

· Contribution margin will be reduced in 1% compared to the last year of past activity and maintained stable for the rest of the years.

·

Travelling expenses for projects will increase in the same proportion as sales.

·

Indirect Labor will increase in the same proportion as sales.

· Depreciation based on 10 years.

· Utilities will increase in the same proportion as sales.

· All other costs will increase at a 10% year over year.

· Accounts receivables in days remain constant, as last year of activity.

·

Accounts payable in days remain constant, as last year of activity.

· Inventories in days remain constant, as last year of activity.

·

Interest expense is 4%.

· Investments will be 5% of net sales every year.

· Make a forecast for all balance sheet accounts.

If we choose scenario 2

· Net sales are reducing 3% compared to the last year of activity and the reduction continues for the 5 years.

· Contribution margin will be increased in 3% compared to the last year of past activity and maintained stable for the rest of the years.

· Travelling expenses for projects will increase in the same proportion as sales.

· Indirect Labor will increase in the same proportion as sales.

· Depreciation based on 10 years.
· Utilities will increase in the same proportion as sales.

· All other costs will decrease at a 1% year over year.

· Accounts receivables in days remain constant, as last year of activity.

· Accounts payable in days remain constant, as last year of activity.

· Inventories in days remain constant, as last year of activity.

· Interest expense is 4%.

· Investments will be 1% of net sales every year.

· Make a forecast for all balance sheet accounts.

Your choices are: (You can make up a company or choose a real one)

· Automotive Parts Supplier B2B

· Surfaces Coating (weather proof / interior) B2B

· Consumer Electronics B2C

· High End Furniture B2C

· Textile, Clothing and Apparel B2C

Each group will have a different company.

Once the files are submitted your group will have to present in class the master budget in 15 minutes (on Week 11). You should support your presentation with a slide deck with data extracted from your spreadsheet and general information about the company chosen.

For the Master Budget Presentation in Week 11:

The grading will be based on:

1. Business Judgement (according to your experience level: Bachelor 3rd year)

2. use of tools discussed during the course. Material Flow, CVP, Income Statement, Balance Sheet, Cash Budget, etc…

3. Completeness, Effort and Team Interaction

4. Correctness of the Spreadsheet Formulas and Quality of the Budget Presentation/Discussion in class.

LAUNCH: WEEK 8 / DELIVERY: WEEK 10 (April 3rd) – Submission on Moodle by 23:59hrs GMT+1 (Barcelona’s time).

This task is worth 40% of your overall grade for this subject.

It assesses the following learning outcomes:

· Develop an understanding and working knowledge of real-world budget issues including variances in input, labor and other prices, and costs of finance.

· Analyze the way in which costs can be managed.

Rubrics

Exceptional

90-100

Good

80-89

Fair

70-79

Fail

<70 Critical analysis (25%) Student effectively assesses the impact of project on the company. Student engages with theory/data in a critical manner. Student fairly assesses the impact of project on the company. Students attempt to engage with theory/data in a critical manner. Student fairly assesses the impact of project on the company, although some key aspects might be missing. Student may be unsuccessful in attempts to engage critically with theory/data. Student fail to assesses the impact of project on the company, although some key aspects might be missing. Student makes no attempt to engage with theory/data in a critical manner. Critical evaluation (25%) Student effectively engages in critical evaluation of all aspects presented in the brief. Student makes a good attempt at engaging in critical evaluation of most aspects presented in the brief. Student makes a fair attempt at engaging in critical evaluation of some aspects presented in the brief (argument might be weak). Student makes an insufficient attempt to critically evaluate aspects presented in the brief. Critical discussion & formulation of solutions (25%) Student effectively leads discussion towards strong theory/data-driven solutions. Student makes a good attempt at leading discussion towards theory/data-driven solutions. Student makes a fair attempt at leading discussion towards theory/data-driven solutions. Student fails to lead discussion towards relevant solutions. Communication (25%) Student includes all relevant sections, meeting professional standards of presentation. Correct referencing format. Student includes all relevant sections, but falls short of professional standards of presentation. Largely correct referencing format. Student includes most relevant sections, but falls short of professional standards of presentation. Some incorrect referencing. Student fails to submit several relevant sections and/or falls significantly short of professional presentation standards. Largely incorrect referencing format.