excel formulas please!

Step $3$: Production Cost Forecast

Now that you have forecasted the production needs for the year, Highstorm Manufacturing needs you to forecast projected production costs for the year on a monthly basis. These costs should be based on the number of units being manufactured each month rather than the number of units being sold.

Production costs include Raw Materials, Labor, and Manufacturing Overhead. Your production volume forecast can be used to help determine the overall estimated cost of Raw Materials purchases. The purchasing department has recently locked in a contract with your suppliers for all of next year. So you know that each pound of Raw Materials purchased will be $\backslash (\backslash$$ $4\mathrm{.}50\backslash ).$

Variable labor costs per unit manufactured are $\backslash$$$30/$unit for Product A and $\backslash$$$45/$unit for Product B$.$ Variable Manufacturing Overhead costs are estimated to be $\backslash (\backslash$$ $25/\backslash )$ unit for both products. Additionally, fixed labor costs related to production activities are expected to be $\backslash (\backslash$$ $1,350,000\backslash )$ for the year, and fixed overhead costs are projected at $\backslash (\backslash$$ $1,500,000\backslash )$ for the year. These fixed costs are spread $\backslash (50/50\backslash )$ across Product A and B$.$

Use this information combined with your forecasted production and purchasing volume to populate Table $7$ below and compute a forecast of production costs for Highstorms Manufacturing.

Table $7-$ Worth up to $30$ points B

Septemb

C

D

E

F

G

H

$1$

J

K

L

M

N

$0$

P

Q

R

S

T

U

V

W

X

Y

Z

$96$

$99$

$101$

$103$

$105$

$\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$hline & & & & $\backslash \backslash$

$\backslash$hline December & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

Table $6-$ Product A $-$ Worth up to $15$ points

$\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$hline $\backslash$begin$\{$tabular$\}\{|$l$|$l$|\}$

$\backslash$hline $\backslash$multicolumn$\{1\}\{|$c$|\}\{\backslash$begin$\{$tabular$\}\{$c$\}$

Forecasted Units to Produce $\backslash \backslash$

$($Product A$)$

$\backslash$end$\{$tabular$\}\}$ & Projected Sales

$\backslash$end$\{$tabular$\}$ & $\backslash$begin$\{$tabular$\}\{$c$\}$

Projected Ending $\backslash \backslash$

Inventory

$\backslash$end$\{$tabular$\}$ & $\backslash$begin$\{$tabular$\}\{$c$\}$

Projected Beginning $\backslash \backslash$

Inventory

$\backslash$end$\{$tabular$\}$ & Units to Produce $\backslash \backslash$

$\backslash$hline January & & & & $\backslash \backslash$

$\backslash$hline Febraury & & & & $\backslash \backslash$

$\backslash$hline March & & & & $\backslash \backslash$

$\backslash$hline April & & & & $\backslash \backslash$

$\backslash$hline May & & & & $\backslash \backslash$

$\backslash$hline June & & & & $\backslash \backslash$

$\backslash$hline July & & & & $\backslash \backslash$

$\backslash$hline August & & & & $\backslash \backslash$

$\backslash$hline September & & & & $\backslash \backslash$

$\backslash$hline October & & & & $\backslash \backslash$

$\backslash$hline November & & & & $\backslash \backslash$

$\backslash$hline December & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

Table $6-$Product B $-$ Worth up to $15$

$\backslash$begin$\{$tabular$\}\{|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$hline $\backslash$multicolumn$\{1\}\{|$c$|\}\{\backslash$begin$\{$tabular$\}\{$l$\}$

Forecasted Units to Produce $\backslash \backslash$

$($ProductA$)$

$\backslash$end$\{$tabular$\}\}$ & $\backslash$begin$\{$tabular$\}\{$l$\}$

Projected Sales

$\backslash$end$\{$tabular$\}$ & $\backslash$begin$\{$tabular$\}\{$c$\}$

Projected Ending $\backslash \backslash$

Inventory

$\backslash$end$\{$tabular$\}$ & $\backslash$begin$\{$tabular$\}\{$c$\}$

Projected Beginning $\backslash \backslash$

Inventory

$\backslash$end$\{$tabular$\}$ & $\backslash$multicolumn$\{1\}\{$c$|\}\{$ Units to Produce $\}\backslash \backslash$

$\backslash$hline January & & & & $\backslash \backslash$

$\backslash$hline February & & & & $\backslash \backslash$

$\backslash$hline March & & & & $\backslash \backslash$

$\backslash$hline April & & & & $\backslash \backslash$

$\backslash$hline May & & & & $\backslash \backslash$

$\backslash$hline June & & & & $\backslash \backslash$

$\backslash$hline July & & & & $\backslash \backslash$

$\backslash$hline August & & & & $\backslash \backslash$

$\backslash$hline September & & & & $\backslash \backslash$

$\backslash$hline October & & & & $\backslash \backslash$

$\backslash$hline November & & & & $\backslash \backslash$

$\backslash$hline December & & & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

Step $3$: Production Cost Forecast

Now that you have forecasted the production needs for the year, Highstorm Manufacturing needs you to forecast projected production costs for the year on a monthly basis. These costs should be based on the number of units being manufactured each month rather than the number of units being sold.

Production costs include Raw Materials, Labor, and Manufacturing Overhead. Your production volume forecast can be used to help determine the overall estimated cost of Raw Materials purchases. The purchasing department has recently locked in a contract with your suppliers for all of

Project Overview

Project Inputs

F Accessibility: Investigate

Step $2$: Forecast Production Volume

Using the forecasted sales you've calculated for next year, Highstorm Manufacturing needs you to forecast their production unit volume on a monthly basis for both Product A and Product B next year. In Table $4$ below, you have been provided the projected year end Materials and Finished Goods inventory at the end of this year for both Product A and Product B$.$

Product A and B both use the same raw materials. Product A uses $2$ lbs $.$ of Raw Materials and Product B uses $3$ lbs $.$ of raw materials per unit.

The company would also like to adopt a policy beginning next year where Materials inventory at the end of each month is equal to $\backslash (100\backslash \%\backslash )$ of the anticipated needs for the following two months while Finished Goods inventory at the end of each month should be kept at $\backslash (50\backslash \%\backslash )$ of the anticipated unit sales for the following two months.

Since the company currently has no estimated for January or February sales two years from now, ending Materials inventory for those two months should be targeted as $\backslash (100\backslash \%\backslash )$ of next year's projected November $\backslash (\backslash$& $\backslash )$ December needs, while Finished Goods Inventory should be targeted as $\backslash (50\backslash \%\backslash )$ of projected November and December's anticipated sales.

Using this information, use Tables $5,6,\backslash$& $7$ below to forecast Highstorm Manufacturing's monthly materials purchases and production volume for Product $\backslash ($ A $\backslash )$ and Product B$.$ Assume all units produced are started and completed in the same month $($No WIP inventory$).$

Table $4$

Table $5-$Worth up to $15$ points