Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Complete the Master Budget on Excel for Hera Manufacturing Use the IF Functions . Provide a brief analysis of the financial forecasted situation of the

Complete the Master Budget on Excel for Hera Manufacturing

Use the IF Functions

. Provide a brief analysis of the financial forecasted situation of the company.

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed
A B C D E F G H J K M HERA Manufacturing Company Month Sales Jar 13.114 Fel 10.793 Background: 7.5ME (Starting in March.) Selling expenses: Travel, meals and lodging of the 20 sales persons (Historical data indicate about 1KE/month.sales person) Advertising expenses: None in May to Aug. 100KE in Jan, Mar, Apr & Sept; 300KE in Feb and Oct; 500KE in Nov and Dec Promotions: Sell-in promotions of 50KE in Jun and Jul, of 60KE in Aug and Sell-out promotions of 500KE in Mar and Dec. Administrative salaries: 15 persons with average salary and benefits of 60KE/yr General management: Salaries &n benefits of the CEO (200KE), CMO (180KE), CFO (180KE) and a secretary (80KE) Shipping expenses: 150 per 100 pairs of skis Office rent 240KE/yr Office utilities: Historical data indicate average monthly expenses of 1KE Telecom expenses: Fixed monthly expenses: 1KE; Variable expenses : 0.003 /Sales amount IT expenses: 65E per person employed (exluding CEO; CMO; CFO and secretary) Insurance: 80KE paid quarterly at the begining of quarterA B C D E G H K M N O P Q Hera Manufacturing - Direct Material Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan 5 Units [pairs of skis] 6 ASP [E Amount [E] 9 10 Desired ending inventory 11 Needed quantity 12 Begining Inventory 13 Quantity to be produced 14 15 Direct Material Budget [(] 16 Fiber glass 306/ski pair 17 Polymers 56/ski pair 18 Steel 7E/ski pair 19 Plastic 16/ski pair 20 Glue 1E/ski pair Total Accounts payable 910.000 Cash outflow (2) 910.000 Inventory Desired ending inventory = 20% of following month units forecast to be sold Direct Material Budget: Fiber glass 306 per pair of skis Polymers 56 per pair of skis Stee 76 per pair of skis Plastic 16 per pair of skis Glue 16 per pair of skis Cash disbursement: Suppliers are paid the amount of material used in month M in M+1 Closing inventory on Dec. 31, Year 4 was 5'027 pairs of skis Accounts payable on Dec. 31, Year 4 were 910'000EA B C D E F G H J K L M N O P Hera Manufacturing - Direct Labor Budget Year 5 Year 6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Units [pairs of skis] ASP Amount [E] - - - 10 Desired ending inventory 11 Needed quantity 12 Begining Inventory 13 Quantity to be produced 14 15 Direct Labour expenses 16 Direct salaries & benefits 17 Direct labor overtime cost 18 Direct supervision s&b 19 Supervision overtime costs: 20 Engineers' salaries & benefits T 7500000;0.1*SUM(C10:E10),0) - Direct Labor Budget, Direct Labor overtime costs: =IF(C13>29400, (C13-29400)*15/3,0) Supervision Overtime costs: =IF(C13>29400; (C13-29400) *21/3,0) - Manufacturing Overhead Budget, Utilities: =+IF(C13>29400;50000+10000,50000)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

College Accounting Chapters 1-24

Authors: Tracie L Nobles, Cathy Scott

11th Edition

1111528306, 978-1111528300

More Books

Students also viewed these Accounting questions

Question

Speak clearly and distinctly with moderate energy

Answered: 1 week ago

Question

Get married, do not wait for me

Answered: 1 week ago