Solve the following excel problem and show me formulas:
If we are in a facility that uses a large amount of deep "drive in" bulk racking or just bulk pallet storage, the percent capacity number target should be even lower than the 85%. For this project. you have an "Plan Assumptions and Data" tab which provides you with the dollar value of product planned to be produced each month for the fiscal year. This tab also shows the number of "production weeks" for each month (note: Nov, Dec, \& Jan have holidays) and the Monthly Production Plan in terms of dollars. The plan takes the Monthly Forecasted Sales and converts it to a Weekly Sales forecast. Off Site space in the event that you will need it. There is also a table showing conversion formulas that you might need to construct your forecasting sheet. Your Task Your run chart should compare the forecasted On Hand inventory requirements with your existing available space, at the 85\% capacity level. The resulting table should indicate if/when you will run out of existing space and allow you the ability to forecast how much additional space you will need and how long you will need it. Assumptions and Conversion Formulas \begin{tabular}{|l|lr|} \hline Starting Inventory Level (\$) & $ & \multicolumn{1}{|c|}{8,630,000} \\ \hline Conversion formula (\$ to Sq. Ft) & =$/291.379 \\ \hline Conversion formula (Sq. t. to $)) & =( Sq. Ft. 29)/1.379 \\ \hline Inv. Value of a TL of product & $ & 25,000 \\ \hline Cost per truckload to move to Offsite & $ & 500 \\ \hline \end{tabular} Vote: Formula for Weekly inventory needs to subtract what was moved to offsite for revious week. VOTE: Worksheet subtracts the inventory "shipped" o offsite storage from the "Weekly Inventory" salance. Estimated Freight Expenses to move excess inventory to Offsite Storage. Estimated Truckloads per Month If we are in a facility that uses a large amount of deep "drive in" bulk racking or just bulk pallet storage, the percent capacity number target should be even lower than the 85%. For this project. you have an "Plan Assumptions and Data" tab which provides you with the dollar value of product planned to be produced each month for the fiscal year. This tab also shows the number of "production weeks" for each month (note: Nov, Dec, \& Jan have holidays) and the Monthly Production Plan in terms of dollars. The plan takes the Monthly Forecasted Sales and converts it to a Weekly Sales forecast. Off Site space in the event that you will need it. There is also a table showing conversion formulas that you might need to construct your forecasting sheet. Your Task Your run chart should compare the forecasted On Hand inventory requirements with your existing available space, at the 85\% capacity level. The resulting table should indicate if/when you will run out of existing space and allow you the ability to forecast how much additional space you will need and how long you will need it. Assumptions and Conversion Formulas \begin{tabular}{|l|lr|} \hline Starting Inventory Level (\$) & $ & \multicolumn{1}{|c|}{8,630,000} \\ \hline Conversion formula (\$ to Sq. Ft) & =$/291.379 \\ \hline Conversion formula (Sq. t. to $)) & =( Sq. Ft. 29)/1.379 \\ \hline Inv. Value of a TL of product & $ & 25,000 \\ \hline Cost per truckload to move to Offsite & $ & 500 \\ \hline \end{tabular} Vote: Formula for Weekly inventory needs to subtract what was moved to offsite for revious week. VOTE: Worksheet subtracts the inventory "shipped" o offsite storage from the "Weekly Inventory" salance. Estimated Freight Expenses to move excess inventory to Offsite Storage. Estimated Truckloads per Month
